1

I need to write a proc where if my actual date has exceeded 3 days i need to update ACTUAL_FINISH date. Saturday sunday are not to be considered in those three days.

select *
from MILESTONE_SCHEDULE
WHERE MILESTONE_TYPE_ID = 398
  and ACTUAL_FINISH IS NULL
  AND ACTUAL_START IS NOT NULL;
simba
  • 277
  • 4
  • 19

1 Answers1

0

Assuming that you have a unique id in your MILESTONE_SCHEDULE table, to achieve what you are looking for you can try like following.

UPDATE T
SET ACTUAL_FINISH = GETDATE()--or whatever you want
FROM MILESTONE_SCHEDULE T
INNER JOIN
(    
   SELECT ID,
   (DATEDIFF(dd, ACTUAL_START, GETDATE()) )
  -(DATEDIFF(wk, ACTUAL_START, GETDATE()) * 2)
  -(CASE WHEN DATENAME(dw, ACTUAL_START) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, GETDATE()) = 'Saturday' THEN 1 ELSE 0 END) AS DayDif
  FROM MILESTONE_SCHEDULE T
  ) T1
  ON T1.ID= T.ID
WHERE T1.DayDif > 3
AND T.MILESTONE_TYPE_ID = 398
AND T.ACTUAL_FINISH IS NULL
AND T.ACTUAL_START IS NOT NULL;

Example with Dummy data

Following query will help to update the value.

DECLARE @MILESTONE_SCHEDULE TABLE(ID INT ,ACTUAL_START DATETIME ,ACTUAL_FINISH DATETIME)

INSERT INTO @MILESTONE_SCHEDULE
VALUES(1,GETDATE()-10,NULL),(2,GETDATE(),NULL)     

UPDATE T
SET ACTUAL_FINISH = GETDATE()
FROM @MILESTONE_SCHEDULE T
INNER JOIN
(    
   SELECT ID,
   (DATEDIFF(dd, ACTUAL_START, GETDATE()) )
  -(DATEDIFF(wk, ACTUAL_START, GETDATE()) * 2)
  -(CASE WHEN DATENAME(dw, ACTUAL_START) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, GETDATE()) = 'Saturday' THEN 1 ELSE 0 END) AS DayDif
  FROM @MILESTONE_SCHEDULE T
  ) T1
  ON T1.ID= T.ID
WHERE T1.DayDif > 3

SELECT * FROM @MILESTONE_SCHEDULE

Output:

ID  ACTUAL_START                ACTUAL_FINISH
1   2018-03-02 11:26:25.707     2018-03-12 11:26:25.707
2   2018-03-12 11:26:25.707     NULL
PSK
  • 17,547
  • 5
  • 32
  • 43
  • query is plain sql, you can find equivalent of DATEDIFF on oracle and easily convert it. – PSK Mar 12 '18 at 09:47
  • UPDATE T SET ACTUAL_FINISH = SYSTIMESTAMP--or whatever you want FROM MILESTONE_SCHEDULE T INNER JOIN ( SELECT ID, (dd- ACTUAL_START, SYSTIMESTAMP) ) -(wk- ACTUAL_START, SYSTIMESTAMP) * 2) -(CASE WHEN TO_CHAR(ACTUAL_START, 'dw') = 'Sunday' THEN 1 ELSE 0 END) -(CASE WHEN TO_CHAR(SYSTIMESTAMP, 'dw') = 'Saturday' THEN 1 ELSE 0 END) AS DayDif FROM MILESTONE_SCHEDULE T ) T1 ON T1.ID= T.ID WHERE T1.DayDif > 3 AND T.MILESTONE_TYPE_ID = 398 AND T.ACTUAL_FINISH IS NULL AND T.ACTUAL_START IS NOT NULL; – simba Mar 12 '18 at 11:16
  • You tagged both SQL Server and Oracle, so you might not get response easily. Suggest you to raise another question with more detail for oracle tag only. You might get help. – PSK Mar 12 '18 at 12:15