CODEWITHSUNDEEP
sql-servert-sql
bumble_bee_tuna
bumble_bee_tuna

Reputation: 3563

Display Query Over Weeks

I'm sorry if this is a duplicate but I've been looking for an hour and have yet to figure out a solution.

If I have the following query:

SELECT 
 [Count]=COUNT([d.ccode])
,[Code]=([d.ccode])
FROM lntmuid.document d
WHERE (DateDiff(WEEK, [d.date], GETDATE()) = 237)
AND staff = 'MF'
AND ccode = 'SUM'
GROUP BY d.ccode

This gives me the number of records for the week. Is it possible for me to generate a record set like:

╔═════════╦═══════╗
║ Week Of ║ Count ║
╠═════════╬═══════╣
║ 1/1/10  ║   100 ║
║ 1/7/10  ║    50 ║
║ 1/14/10 ║    80 ║
║ 1/21/10 ║    70 ║
║ 1/28/10 ║   120 ║
║ etc …   ║       ║
╚═════════╩═══════╝

Thanks for the help

Upvotes: 1

Views: 31

Answers (1)

GarethD
GarethD

Reputation: 69749

You can get the first day of the week using:

SELECT DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '19000101');

The logic is simply get the number of weeks between a given date (GETDATE() in the above) and a fixed date, then add that number of weeks back to the fixed date, and because DATEDIFF only counts week boundaries it will give you the first day of the week for your given date.

You then just need to substitute your date field into the above, and add the expression to the group by clause:

SELECT WeekOf = DATEADD(WEEK, DATEDIFF(WEEK, '19000101', [d.date]), '19000101')
        ,[Count]=COUNT([d.ccode])
FROM lntmuid.document d
WHERE staff = 'MF'
AND ccode = 'SUM'
GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, '19000101', [d.date]), '19000101');

You will also see this method used as:

SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0);

This is exactly the same, but uses the implicit conversion of 0 to 1900-01-01 rather than explicitly typing it to save a bit of time. I tend to use this method in my code, but feel like using a date makes what is going on a bit more clear for answers on Stackoverflow.

Upvotes: 1

Related Questions