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.
| Author |
Topic |
|
Muj9
Yak Posting Veteran
75 Posts |
 Posted - 2014-01-02 : 05:28:47
|
| Hi all I am having difficulty with this case statement, each condition writen below is either 'in hours' or 'out of hours'. so what i am after is, when the time is between 9am to 5pm then 'in hours' else 'Out of hours' but if time starts from 8am and finihes a 6pm then 'out of hours' should be 2hrs and 'in hours' should be 8hrs, hope this makes sense. select case when datepart(HOUR,StartTime)>=9 and datepart(HOUR,Endtime) <= 17 then 'Inhours' when datepart(HOUR,StartTime) between 9 and 17 and datepart(HOUR,Endtime)> 17 then (17 - datepart(HOUR,StartTime) --'Inhours' when datepart(HOUR,StartTime) between 9 and 17 and datepart(HOUR,Endtime)> 17 then datepart(HOUR,Endtime)- 17) --'outofhours' when datepart(HOUR,StartTime)< 9 and datepart(HOUR,Endtime) between 9 and 17 then 9 - datepart(HOUR,StartTime) --'outofhours' when datepart(HOUR,StartTime)< 9 and datepart(HOUR,Endtime) between 9 and 17 then datepart(HOUR,Endtime) - 9 --'Inhours' else 'outofhours' end as WorkingPattern from tablexCan some one help me please . Thank you |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2014-01-02 : 11:56:19
|
| When this executes if the 2nd when is true the third when (exactly the same) will not execute. I think you have a copy paste error in your example.djj |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
Case statement