amolacp (7/28/2014)

---

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