Date Time Buckets

Question

Date Time Buckets

ryanlewis76

Old Hand

Points: 311
More actions
November 27, 2008 at 8:29 am

#202145

Hi, fairly new to the arena and hoping someone can help out.
I am trying to pull data from an employee table according to time buckets. Each employee belongs to a certain 2 week bucket, and for that time bucket they need to be marked as an 'A' person or a 'B' person.
For example:
EmployeeName - ShiftEndPeriod - Marker (not yet figured out)
Joe Bloggs - 23/11/2008 - 'A'
Fred Flinstone - 23/11/2008 - 'A'
John Doe - 16/11/2008 - 'B'
Jane Doe - 23/11/2008 - 'A'
Barny Rubble - 16/11/2008 - 'A'
So from an employee table that contains the shiftendperiod date I need to match to a new table that gives me the 'A'/'B' marker. This new table must be rolling forward and cover a period of 6 weeks at all times with end periods being on Sundays.
I hope that makes sense and hope someone can help.
Thanks

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Tim Mitchell SSCoach Points: 15695 More actions · Answer 1

Ryan,

If you are always running this on Sunday and the dates provided are always Sundays as well, you could just do a MOD (%) on the number of days between the current system date and the date identified for that employee:

UPDATE [MyTable]

SET [Marker] = CASE WHEN (DATEDIFF(day, GETDATE(), ShiftEndPeriod)) % 2 = 0 THEN 'A' ELSE 'B' END

This will return a zero for the current day resulting in A, then 7 for the next Sunday resulting in B, etc.

hth,

Tim

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

ryanlewis76 Old Hand Points: 311 More actions · Answer 2

Ta very much, works a treat, but can I just check (in my ignorance) what does the %2 actually do. I know this works, but just trying to figure out why.

Many Thanks for that.

Ryan

Tim Mitchell SSCoach Points: 15695 More actions · Answer 3

The % is a MOD operator in SQL Server. If you're not familiar with that mathematical function, it will return the remainder portion of a division operation. So, 6 % 2 = 0, 10 % 3 = 1, and so forth. The %2 will return 0 for even numbers and 1 for odd numbers.

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

ryanlewis76 Old Hand Points: 311 More actions · Answer 4

Many thanks for that, as soon as you posted I remembered using MOD in Excel. Changed the MOD %2 to %14 as using 2 week buckets and all is working exactly as it should, thanks for the help.

Many Thanks

Ryan