Monday, July 28, 2014

Update Date based on the condition


DECLARE @TEST_DATA TABLE (DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, JobNumber VARCHAR(10) NOT NULL, JobStatus CHAR(1) NOT NULL, ExpectedDate VARCHAR(10) NOT NULL, LastShippedDate VARCHAR(10) NULL);

INSERT INTO @TEST_DATA (JobStatus, JobNumber,ExpectedDate,LastShippedDate)
VALUES
('S', 'J012345','2014-06-26','2014-07-03') ,('O', 'J012346','2014-07-03',NULL ) ,('O', 'J012347','2014-07-03',NULL ) ,('O', 'J012348','2014-07-10',NULL ) ,('O', 'J012349','2014-07-10',NULL )
,('O', 'J012350','2014-07-10',NULL ) ,('O', 'J012351','2014-07-10',NULL ) ,('O', 'J012352','2014-07-17',NULL ) ,('O', 'J012353','2014-07-17',NULL ) ,('O', 'J012354','2014-07-17',NULL )
,('O', 'J012355','2014-07-17',NULL ) ,('O', 'J012356','2014-07-17',NULL ) ,('O', 'J012357','2014-07-24',NULL ) ,('O', 'J012358','2014-07-24',NULL ) ,('O', 'J012359','2014-07-24',NULL )
,('O', 'J012360','2014-07-24',NULL ) ,('O', 'J012361','2014-07-24',NULL ) ,('O', 'J012362','2014-07-31',NULL ) ,('O', 'J012362','2014-07-28',NULL );

SELECT J.DT_ID, J.JobStatus, J.ExpectedDate, J.LastShippedDate,
CASE
WHEN j.JobStatus <>'S' AND Cast(J.ExpectedDate as Date) <= CONVERT(DATE,GETDATE()) THEN Dateadd(day,4,cast (DATEADD(week, DATEDIFF(week,0,GETDATE()),-1) AS DATE))
WHEN j.JobStatus ='S' then Cast(j.LastShippedDate AS DATE)
ELSE Cast(J.ExpectedDate AS Date)
END AS TestNewShipDate
FROM @TEST_DATA J





Slight change to the case criteria so it compared to todays date, rather than the start of next week.

No comments:

Post a Comment