updating a datetime feild using Case

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

updating a datetime feild using Case

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-01-20 : 09:01:18

Hi All-Below is the case statement I have written to update a datetime(Deliverydt) feild based on three other feilds ot of which two are datetime feilds(approvedt,availabledt) and the third one(turndays) is a integer value

Update Customer_Delivery
Set Deliverydt = 
Case
When availabledt is null then deliverydt = approvedt
When availabledt > approvedt then deliverydt = dataavailable + [Turndays] business days
When approvedt > availabledt then deliverydt = approvedt + [Turndays] businessdays
Else uwapprovedt
end 
Where reqno in ()

In the above query i would need help to add turndays feild to the date.the turndays value is an integer value that needs to be added as business days to the date taking into consideration that weekends are holidays and all weekdays are business days

Thanks
Jim

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-20 : 09:03:47

When availabledt > approvedt then deliverydt = dataavailable + [Turndays] ~~business days~~

When availabledt > approvedt then deliverydt = DATEADD(dd,[Turndays],dataavailable)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-01-20 : 09:12:01

how do i convert the turndays feild to businessdays.
so for example if i have to add 10[turndays] to todaysdate(deliverydt)
HopeI'm making sense

quote:

Originally posted by X002548

When availabledt > approvedt then deliverydt = dataavailable + [Turndays] ~~business days~~

When availabledt > approvedt then deliverydt = DATEADD(dd,[Turndays],dataavailable)

Brett

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-01-22 : 22:14:35

still waiting for help on my post

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-01-24 : 08:47:32

found help for this post online in case someone might need this in future

Update Customer_Delivery
Set deliverydt =
Case
When availabledtis null then approvedt
When availabledt > approvedt then DATEADD(day,

Turndays%5 +

CASE DATEPART(weekday,availabledt) + Turndays%5

WHEN 6 THEN 2

WHEN 7 THEN 1

ELSE 0 END,

DATEADD(week,Turndays/5,availabledt))
WHEN approvedt > availabledt then DATEADD(day,

Turndays%5 +

CASE DATEPART(weekday,approvedt) + Turndays%5

WHEN 6 THEN 2

WHEN 7 THEN 1

ELSE 0 END,

DATEADD(week,Turndays/5,approvedt)
Else approvedt
end
Where reqno in ()

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources