Based on Eirikurs great solution I added a few minor "enhancements":
a) instead of using GETDATE() in a CASE statement, I've added a separat variable. This has two advantages: it makes the code a little more compact and it avoids using a non-deterministic value that will be re-evaluated each time at runtime. Start a batch with 10.000 rows on a Thursday at 23:59:59.994 and you'll see what I mean: some rows will have a shipment day of the following friday and some will be set to the following monday.
Using a variable the code will always use a consistant value.
b) I replaced the non-deterministic function DATENAME(WEEKDAY,GETDATE()) = '...' with a solution that is independend on the settings of SET LANGUAGE:
DATEDIFF(dd,0,@Date)%7 basically calculates the days since 1900-01-01 (which was a Monday) and takes the Modulo of it. This leads to values from 0 (Monday) to 6 (Sunday) regardless of the language setting being active for that code block.
You can verify the negative effect of DATENAME by adding SET LANGUAGE 'GERMAN' to Eirikurs code after the declaration of @TEST_DATA
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
,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), @Date) BETWEEN 1 AND 5 THEN
CASE
/* SET SATURDAY AND SUNDAY SHIPMENTS TO NEXT FRIDAY */
WHEN DATEDIFF(dd,0,CAST(J.ExpectedDate AS DATE))%7 = 6THEN DATEADD(DAY,5,CAST(J.ExpectedDate AS DATE))
WHEN DATEDIFF(dd,0,CAST(J.ExpectedDate AS DATE))%7 = 5THEN 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), @Date) > 5 THEN
CASE
/* SET SHIPMENTS TO NEXT FRIDAY EXEPT FRIDAY GOES TO MONDAY */
WHEN DATEDIFF(dd,0,@Date)%7 = 6THEN DATEADD(DAY,5,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 5THEN DATEADD(DAY,6,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 0THEN DATEADD(DAY,4,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 1THEN DATEADD(DAY,3,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 2THEN DATEADD(DAY,2,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 3THEN DATEADD(DAY,1,@Date)
WHEN DATEDIFF(dd,0,@Date)%7 = 4THEN DATEADD(DAY,3,@Date)
END
/* FUTURE SHIPMENTS */
ELSE CAST(J.ExpectedDate AS DATE)
END AS NEXT_SHIP_DATE
FROM @TEST_DATA J