There is no magic here, the case statement I posted (use Lutz's version) allows you to control this to the detail, not certain what would be the reason for not using that. Read through the code and come back with any questions you might have. Of course you must adjust it to your needs, but I cannot see any scenario where it doesn't work.

Here is a modification of Lutz's code towards your requirements

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);

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 = 5THEN DATEADD(DAY,5,CAST(J.ExpectedDate AS DATE))

WHEN DATEDIFF(dd,0,CAST(J.ExpectedDate AS DATE))%7 = 4THEN 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,4,@Date)

WHEN DATEDIFF(dd,0,@Date)%7 = 5THEN DATEADD(DAY,5,@Date)

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,0,@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

Results

DT_ID JobStatus ExpectedDate LastShippedDate NEXT_SHIP_DATE

----------- --------- ------------ --------------- --------------

1 S 2014-06-26 2014-07-03 2014-07-03

2 O 2014-07-03 NULL 2014-07-31

3 O 2014-07-03 NULL 2014-07-31

4 O 2014-07-10 NULL 2014-07-31

5 O 2014-07-10 NULL 2014-07-31

6 O 2014-07-10 NULL 2014-07-31

7 O 2014-07-10 NULL 2014-07-31

8 O 2014-07-17 NULL 2014-07-31

9 O 2014-07-17 NULL 2014-07-31

10 O 2014-07-17 NULL 2014-07-31

11 O 2014-07-17 NULL 2014-07-31

12 O 2014-07-17 NULL 2014-07-31

13 O 2014-07-24 NULL 2014-07-31

14 O 2014-07-24 NULL 2014-07-31

15 O 2014-07-24 NULL 2014-07-31

16 O 2014-07-24 NULL 2014-07-31

17 O 2014-07-24 NULL 2014-07-31

18 O 2014-07-31 NULL 2014-07-31