It’s been a busy month since my last post. Moving from contracting to consulting requires a more work that I’d thought at first. In looking over the past few months I’ve built a few new queries that I haven’t built before. This first one comes from a report built for a client. The request came in to break down aggregates into weeks. Breaking down aggregates by year and month is simple. SQL has functions that can do that no problem, you simply call YEAR(dateColumn) or MONTH(dateColumn).
Without thinking too much about it, I suggested using DATEPART, but I didn’t know the exact parameter I’d use to be able to group my data by weeks. Turns out I wanted to use DATEPART(WK, DateColumn).
SELECT
DATEPART(wk, dateColumn) as WeekNumber
, COUNT(*) as AggregateCount
FROM sourceTable
GROUP BY
DATEPART(wk, dateColumn)
But this brought up the question, what’s the week? Sunday to Saturday? Monday to Sunday? By default the first day of the week is Sunday. How can you see that? Well, let’s check what day of the week this past Sunday (4/29/2012) was.
SELECT DATEPART(dw, '4/29/2012') AS 'This Past Sunday';
That’s the way I choose my weeks, and the SQL Developers evidently agree with me, but our business users did not. So I needed to change the first day of the week to Monday. Fortunately, the SQL developers made it dead simple to change the first day of the week from Sunday to Monday. They gave us SET DATEFIRST.
All you have to do is change the day of the week to the number of the day of the week you want to be the first day of the week.
| Value | First day of the week is |
|---|---|
| 1 | Monday |
| 2 | Tuesday |
| 3 | Wednesday |
| 4 | Thursday |
| 5 | Friday |
| 6 | Saturday |
| 7 (default, U.S. English) | Sunday |
*from msdn.
Simply call SET DATEFIRST before your query, and you can change the first day of the week for all queries using that connection, until it’s closed. You can also SELECT @@DATEFIRST to see what the current first day of the week is, in case you forget.
SET DATEFIRST 1;
SELECT @@DATEFIRST AS 'First Day'
,DATEPART(dw, '4/29/2012') AS 'This Past Sunday'
,DATEPART(dw, '4/30/2012') AS 'This Past Monday';
Results in:
First Day This Past Sunday This Past Monday --------- ---------------- ---------------- 1 7 1 (1 row(s) affected)
Now that you have a week number you can pretty up the results by converting your week numbers into week endings, week beginnings, or show the full range (first – last day of the week). The sky’s the limit when it comes to presenting the data. Once we presented this option to the programmers, they took off with the idea, and presented several new reports now capable of breaking down their counts by week.
It’s good to be able to present a solution to a client and have them embrace it so completely! It’s what makes the job fun. If you’re looking for SQL Consulting help, feel free to contact me, we’re here to help!