amolacp (7/29/2014)
Apologies for reposting the above question. I was not able to see the post after refreshing the page that's why I ended up posting multiple times.
No worries, I think I got it in the end:-D
Here is the simplified version which sets all missed dates to the coming Thursday.
😎
USE tempdb;
GO
DECLARE @Date DATE;
SET @Date = GETDATE();
--SET @Date = '2014-07-25';
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,JobNumberVARCHAR(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', 'J066811','2014-07-29',NULL)
,('O', 'J066594','2014-07-28',NULL);
SELECT
J.DT_ID
,J.JobStatus
,J.JobNumber
,J.ExpectedDate
,J.LastShippedDate
,CASE
/* SHIPPED - SHOW LastShippedDate */
WHEN j.JobStatus ='S' THEN Cast(j.LastShippedDate as DATE)
/* MISSED SHIPMENT SET TO NEXT THURSDAY */
WHEN j.JobStatus <>'S' AND DATEDIFF(DAY,CAST(J.ExpectedDate AS DATE), @Date) > 0 THEN
CASE
/* SET SHIPMENTS TO NEXT THURSDAY */
WHEN DATEDIFF(dd,0,@Date)%7 = 0THEN DATEADD(DAY,3,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 1THEN DATEADD(DAY,2,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 2THEN DATEADD(DAY,1,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 3THEN DATEADD(DAY,7,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 4THEN DATEADD(DAY,3,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 6THEN DATEADD(DAY,4,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 5THEN DATEADD(DAY,5,@Date)
END
/* FUTURE SHIPMENTS */
ELSE CAST(J.ExpectedDate AS DATE)
END AS NEXT_SHIP_DATE
FROM @TEST_DATA J