Hi All,

I need to execute the below mentioned query

SyntaxEditor Code Snippet

SELECT 
(
SET @DeadlineHours=
(SELECT (CASE WHEN {Deadline}.[Unit]='Days' THEN {Deadline}.[Deadline]*24 ELSE {Deadline}.[Deadline] END) FROM {Deadline} WHERE {Deadline}.[Status]={OPA_Record}.[StatusId]) 
SET @AssignedDateTime=(SELECT TOP 1 {ProcessAudit}.[ChangedDateTime] FROM {ProcessAudit} WHERE {ProcessAudit}.[OPARecordId]={OPA_Record}.[Id] ORDER BY {ProcessAudit}.[ChangedDateTime] DESC);
WHILE @DeadlineHours>0
BEGIN
SET @AssignedDateTime=(SELECT DATEADD(HOUR,CASE WHEN DATEDIFF(HOUR,0,@AssignedDateTime)%24 > 3*24 THEN 7*24-DATEDIFF(HOUR,0,@AssignedDateTime)%24 ELSE 1 END,@AssignedDateTime))

IF DATEPART(DW,@AssignedDateTime)=1 
SET @AssignedDateTime=DATEADD(DD,1,@AssignedDateTime);
ELSE IF DATEPART(DW,@AssignedDateTime)=7
SET @AssignedDateTime=DATEADD(DD,2,@AssignedDateTime);


SET @DeadlineHours=@DeadlineHours-1;
END
SELECT @AssignedDateTime;
)
FROM {OPA_Record} 
INNER JOIN {Status} ON {OPA_Record}.[StatusId]={Status}.[Id]

The bold section was supposed to be in a function but since I don't have rights to create a function in DB so I was looking to incorporate the logic in the select statement. But it seems to give error .I would be helpful if some can advice on how to do it.


Thanks 

Hi Pranav,

Could you share some more details about the error? What happens when you test it?

Hi Afonso,

Please find the screen shot below,