Here is the DDL. Suppose, if today's date is 2014-08-01 and we miss the shipdate for the job# J012362, the 'TestNewShipDate' still shows 2014-07-31 instead of 2014-08-01. I've also included the DDL at the end of this message. A quick note, since we only ship on Thursday of every weel, I want the TestNewShipDate to get updated to next Thursday. Also, I've attached the results screenshot to this message. I appreciate your help.

USE tempdb;

GO

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.JobNumber

,J.JobStatus

,J.ExpectedDate

,

'TestNewShipDate'=Case

when j.JobStatus <>'S'

and Cast(J.ExpectedDate as Date)

< cast (GETDATE()+4 as DATE)

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

,cast (GETDATE()+4 as DATE) as TodaysDate

,J.LastShippedDate

FROM @TEST_DATA J