Between Times - SQL Server Forums

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

Between Times

Author

Topic

Derick
Starting Member

17 Posts

Posted - 2009-06-24 : 09:47:57

Hi All,
I'm trying to calc the amount of people that worked between two times

SUM(CASE WHEN tblAttend.txtShift BETWEEN '06:00:00 AM' AND '01:00:00 PM' AND tblAttend.chkDidTrain = 1 THEN 1 ELSE 0 END)
AS AttendMorning

but get a value of 0

if I put

SUM(CASE WHEN tblAttend.txtShift BETWEEN '06:00:00 AM' AND '11:59:00 AM' AND tblAttend.chkDidTrain = 1 THEN 1 ELSE 0 END)
AS AttendMorning

Then get a value of 1

There are records after 12:00

Thanks

Do something out of character each day this week. Change your hairstyle, wear a different colour, show your teeth when you smile, eat chocolate-anything! Just choose to be different because you can!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-24 : 09:57:41

Since txtShift is VARCHAR, you NEED the entries for the BETWEEN clause in ascending order.
If the two values for the BETWEEN clause are descending, no match is done.

SUM(CASE WHEN DATEDIFF(HOUR, 0, tblAttend.txtShift) BETWEEN 6 AND 11 AND tblAttend.chkDidTrain = 1 THEN 1 ELSE 0 END) ,
SUM(CASE WHEN DATEDIFF(HOUR, 0, tblAttend.txtShift) BETWEEN 12 AND 19 AND tblAttend.chkDidTrain = 1 THEN 1 ELSE 0 END)-- miltary time style
AS AttendMorning

E 12°55'05.63"
N 56°04'39.26"

Derick
Starting Member

17 Posts

Posted - 2009-06-24 : 10:20:50

Many thanks, it works great

Do something out of character each day this week. Change your hairstyle, wear a different colour, show your teeth when you smile, eat chocolate-anything! Just choose to be different because you can!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources