It is better to break down the problem into smaller parts using nested case statements, more readable and easier to follow. Here is an example which should be close to what you are after.
😎
USE tempdb;
GO
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,JobStatus CHAR(1) NOT NULL
,ExpectedDate VARCHAR(10) NOT NULL
,LastShippedDate VARCHAR(10) NULL
);
INSERT INTO @TEST_DATA (JobStatus,ExpectedDate,LastShippedDate)
VALUES
('S','2014-06-21','2014-06-21')
,('F','2014-07-15',NULL)
,('F','2014-07-16',NULL)
,('F','2014-07-17',NULL)
,('F','2014-07-18',NULL)
,('F','2014-07-19',NULL)
,('F','2014-07-20',NULL)
,('F','2014-07-21',NULL)
,('F','2014-07-22',NULL)
,('F','2014-07-23',NULL)
,('F','2014-07-24',NULL)
,('F','2014-07-25',NULL)
,('F','2014-07-26',NULL)
,('F','2014-07-27',NULL)
,('F','2014-07-28',NULL)
,('F','2014-07-29',NULL)
,('F','2014-07-30',NULL)
,('F','2014-07-31',NULL);
SELECT
J.DT_ID
,J.JobStatus
,J.ExpectedDate
,J.LastShippedDate
,CASE
/* SHIPPED - SHOW LastShippedDate */
WHEN j.JobStatus ='S' THEN Cast(j.LastShippedDate as DATE)
/* MISSED SHIPMENT BY 1 TO 5 DAYS */
WHEN j.JobStatus <>'S' AND DATEDIFF(DAY,CAST(J.ExpectedDate AS DATE), CAST(GETDATE() AS DATE)) BETWEEN 1 AND 5 THEN
CASE
/* SET SATURDAY AND SUNDAY SHIPMENTS TO NEXT FRIDAY */
WHEN DATENAME(WEEKDAY,CAST(J.ExpectedDate AS DATE)) = 'Sunday' THEN DATEADD(DAY,5,CAST(J.ExpectedDate AS DATE))
WHEN DATENAME(WEEKDAY,CAST(J.ExpectedDate AS DATE)) = 'Saturday' THEN DATEADD(DAY,6,CAST(J.ExpectedDate AS DATE))
/* ADD 7 DAYS TO THE OTHER */
ELSE DATEADD(DAY,7,CAST(J.ExpectedDate AS DATE))
END
/* MISSED SHIPMENT BY MORE THAN 5 DAYS */
WHEN j.JobStatus <>'S' AND DATEDIFF(DAY,CAST(J.ExpectedDate AS DATE), CAST(GETDATE() AS DATE)) > 5 THEN
CASE
/* SET SHIPMENTS TO NEXT FRIDAY EXEPT FRIDAY GOES TO MONDAY */
WHEN DATENAME(WEEKDAY,GETDATE()) = 'Sunday' THEN DATEADD(DAY,5,CAST(GETDATE() AS DATE))
WHEN DATENAME(WEEKDAY,GETDATE()) = 'Saturday' THEN DATEADD(DAY,6,CAST(GETDATE() AS DATE))
WHEN DATENAME(WEEKDAY,GETDATE()) = 'Monday' THEN DATEADD(DAY,4,CAST(GETDATE() AS DATE))
WHEN DATENAME(WEEKDAY,GETDATE()) = 'Tuesday' THEN DATEADD(DAY,3,CAST(GETDATE() AS DATE))
WHEN DATENAME(WEEKDAY,GETDATE()) = 'Wednesday' THEN DATEADD(DAY,2,CAST(GETDATE() AS DATE))
WHEN DATENAME(WEEKDAY,GETDATE()) = 'Thursday' THEN DATEADD(DAY,1,CAST(GETDATE() AS DATE))
WHEN DATENAME(WEEKDAY,GETDATE()) = 'Friday' THEN DATEADD(DAY,3,CAST(GETDATE() AS DATE))
END
/* FUTURE SHIPMENTS */
ELSE CAST(J.ExpectedDate AS DATE)
END AS NEXT_SHIP_DATE
FROM @TEST_DATA J
Results
DT_ID JobStatus ExpectedDate LastShippedDate NEXT_SHIP_DATE
----------- --------- ------------ --------------- --------------
1 S 2014-06-21 2014-06-21 2014-06-21
2 F 2014-07-15 NULL 2014-08-01
3 F 2014-07-16 NULL 2014-08-01
4 F 2014-07-17 NULL 2014-08-01
5 F 2014-07-18 NULL 2014-08-01
6 F 2014-07-19 NULL 2014-08-01
7 F 2014-07-20 NULL 2014-08-01
8 F 2014-07-21 NULL 2014-08-01
9 F 2014-07-22 NULL 2014-07-29
10 F 2014-07-23 NULL 2014-07-30
11 F 2014-07-24 NULL 2014-07-31
12 F 2014-07-25 NULL 2014-08-01
13 F 2014-07-26 NULL 2014-08-01
14 F 2014-07-27 NULL 2014-07-27
15 F 2014-07-28 NULL 2014-07-28
16 F 2014-07-29 NULL 2014-07-29
17 F 2014-07-30 NULL 2014-07-30
18 F 2014-07-31 NULL 2014-07-31