Hi Eirikur & SSC,
I appreciate the response from you guys. I apologize for not being clear before. We have jobs that only get shipped on Thursday of every week. The below T-SQL statement works fine. Let me be more clear, for example if we missed the ship date 07/24/2014 (last Thursday) for a job the 'TestNewShipDate' adds 7 days to the ship date i.e. 07/31/2014 which works according to my requirement. The only issue is the 'TestNewShipDate' updates/adds 7 days (07/31/2014) to the ship date if we missed the shipdate (07/24/2014) only on Sundays which is (07/27/2014). I want the 'TestNewShipDate' to add/update 7 days, on Friday (07/25/2014) instead of updating the date on Sunday for the above case. Need your help guys.
Thanks
select j.JobNumber, j.JobStatus,
Cast(x.ExpectedDate as Date) 'OldShipDate'
, 'TestNewShipDate' =
Case
when j.JobStatus <>'S'
and Cast(x.ExpectedDate as Date)
< DATEADD(wk, DATEDIFF(wk,3,cast (GETDATE() as date)), 3)
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(x.ExpectedDate as Date)
END
, 'Shipped' = Case When j.LastShippedDate = '' Then Null Else Cast(j.LastShippedDate As Date) End
from JobExtra j (NOLOCK)
Join ArchiveJobHeader m (nolock) on m.JobNumber = j.JobNumber
Left Join JobMaster x (nolock) on x.JobNumber = j.JobNumber
where m.customercode='3308'
JobNumber JobStatus OldShipDate TestNewShipDate Shipped
J012345 S (Shipped) 2014-07-10 2014-07-17 2014-07-17
J012346 (Blank) (Not Shipped) 2014-07-10 2014-07-31 NULL