Creating a Period Date Range Table, with SQL!
18 October 2006You need a way to lump dated records into periodic chunks, like say for a timesheet system. Say your company’s payroll periods are bi-weekly (that’s every other week). So you need a way to distinguish the dated payroll data into two week timesheets.
Sure you could spend three days figuring out a fancy algorithm, only to find out 6 months into your project that there is just this one teeny-tiny instance where they manually change the dates around, and your algorithm doesn’t work.
Well my friend what you need is a table that you can look up against. How are you going to create the data for that table? Very simple, especially with my example of a bi-weekly pay period schedule. We’ll use a SQL Query to perform the insert for us.
Here is what the table will look like for us when we are done:
| PeriodID | PeriodStart | PeriodEnd |
| 1 | 2006-09-25 00:00:00.000 | 2006-10-08 00:00:00.000 |
| 2 | 2006-10-09 00:00:00.000 | 2006-10-22 00:00:00.000 |
| 3 | 2006-10-23 00:00:00.000 | 2006-11-05 00:00:00.000 |
| 4 | 2006-11-06 00:00:00.000 | 2006-11-19 00:00:00.000 |
| 5 | 2006-11-20 00:00:00.000 | 2006-12-03 00:00:00.000 |
Here is the SQL Script that I used to generate the data for the period table (bi-weekly):
declare @i int, @ws datetime, @we datetime select @ws = '2006-09-25', @i = -2 while @i < 2000 begin select @i = @i + 2 select @we = dateadd(day, -1, dateadd(week, @i+2, @ws)) insert into PayPeriod (PeriodStart, PeriodEnd) values (dateadd(week, @i, @ws), @we) print cast(dateadd(week, @i, @ws) as varchar) + ' | ' + cast(@we as varchar) end
Methodology used here
- We use a sentinel start date that we know 9-25-2006
- Then in a loop:
- we increment a counter by 2
- the start date is sentinel date + counter (in weeks with the date add function)
- the finish date is sentinel date + counter + 2 weeks , then subtract 1 day to get the date just prior to the start of the next period
There you go, now you have a table with a unique id given to a date range of start date and end date. So now if you want to know what period a particular record is in, you can just join to that table in this way:
select employee, dateWorked, PayPeriod.id as payPeriod from Timesheet, PayPeriod where Timesheet.dateWorked between PayPeriod.PeriodStart and PayPeriod.PeriodEnd
Notice there is no JOIN statement used, that means we need to specify the join on criteria in the WHERE clause. We do it this way in order to utilize the BETWEEN function (inclusive of the start date and end date).
There is always a simple solution; some times it takes a little longer than other times to work it out. Coffee always seems to help me, Oh! and google.
For more SQL recipies and help you should check out the SQL Pocket Guide
No comments yet
Leave a Reply
You must be logged in to post a comment.