Hi All,

I need to execute the below mentioned query

SyntaxEditor Code Snippet

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
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;
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.


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,