Assign a "shift" number based on weekday and time.

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

SQL Server 2005 Forums

Transact-SQL (2005)

Assign a "shift" number based on weekday and time.

Author

Topic

mddowlin
Starting Member

2 Posts

Posted - 2011-07-13 : 16:56:52

I would like to assign a shift number to a receipt time of x based on the following:
Shift Condition
1 Mon-Fri 7am to 3:30pm
2 (All weekdays) 3:31pm to 11:30pm
3 (All weekdays) 11:31pm to 6:59am
4 Sat-Sun 7am to 3:30

If only I could convince the client to change their shifts to whole hours, I could use:

CASE WHEN DATEPART(dw,ReceiptDateTime) BETWEEN 2 AND 6 AND DATEPART(hh,ReceiptDateTime) BETWEEN 7 AND 15 THEN 1
WHEN DATEPART(dw,ReceiptDateTime) IN (1,7) AND DATEPART(hh,ReceiptDateTime) BETWEEN 7 AND 15 THEN 4
WHEN DATEPART(hh,ReceiptDateTime) BETWEEN 16 AND 23 THEN 2
ELSE 3 END AS ReceiptShift

So I'm looking for the best way (cheapest) to assign a shift that includes half-hours.

Any help is greatly appreciated. First time, long time :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-13 : 17:40:00

You could do something like this, where I am converting time to minutes to do the calculations.


SELECT 
   CASE 
      WHEN DATEDIFF(dd,0,GETDATE())%7 BETWEEN 0 AND 4 THEN
      CASE 
         WHEN DATEDIFF(minute, 0, GETDATE())%1440 BETWEEN 420 AND 930 THEN 1
         WHEN DATEDIFF(minute, 0, GETDATE())%1440 BETWEEN 931 AND 1410 THEN 2
         ELSE 3
      END
      
      WHEN DATEDIFF(dd,0,GETDATE())%7 IN (5,6) 
         AND DATEDIFF(minute, 0, GETDATE())%1440 BETWEEN 420 AND 930 THEN 4
   END

mddowlin
Starting Member

2 Posts

Posted - 2011-07-13 : 18:09:51

Thank you that works great! That's much better than writing a scalar function with nasty concatenation. If it's not too much trouble, do you know where I could read about using the '%' in this way? I've never seen that before.

Thans again!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-13 : 18:39:05

do you know where I could read about using the '%' in this way?

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

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources