How to Group by the Past Seven Days

Question

Post reply

How to Group by the Past Seven Days

garyh2k3

SSC-Addicted

Points: 456
More actions
August 13, 2013 at 8:38 am

#276877

I am familiar with how to group my query results by year, month, week etc. But what is the best way to group my results by the past 7 days for a given timeframe? So my results would be grouped by seven days back from today (8/6/2013) then seven days back from that (7/30/2013) etc. How do I accomplish this? Please advise.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Stefan_G SSCertifiable Points: 6609 More actions · Answer 1

You could use something like this:

GROUP BY datediff(day, StartTime, getdate())/7

batgirl SSCarpal Tunnel Points: 4979 More actions · Answer 2

Stefan beat me to it - same answer here.

group by ((DATEDIFF(day, getdate(), "datefield"))/7)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Have a play with this date arithmetic code:

SELECT DATEDIFF(DD,'19010101',GETDATE())

SELECT DATEDIFF(DD,0,GETDATE())

SELECT DATENAME(DW,CAST('19010101' AS DATETIME))

SELECT

MyDate,

DATENAME(dw,MyDate),

DaysSince19000101 = DATEDIFF(DD,0,MyDate),

DateNoTime = DATEADD(DD,DATEDIFF(DD,0,MyDate),0),

mon_sun = DATEADD(DD,0+DATEDIFF(DD,0,MyDate)/7*7,0),

tue_mon = DATEADD(DD,1+DATEDIFF(DD,1,MyDate)/7*7,0),

wed_tue = DATEADD(DD,2+DATEDIFF(DD,2,MyDate)/7*7,0),

thu_wed = DATEADD(DD,3+DATEDIFF(DD,3,MyDate)/7*7,0),

fri_thu = DATEADD(DD,4+DATEDIFF(DD,4,MyDate)/7*7,0),

sat_fri = DATEADD(DD,5+DATEDIFF(DD,5,MyDate)/7*7,0),

sun_sat = DATEADD(DD,6+DATEDIFF(DD,6,MyDate)/7*7,0)

FROM (

SELECT MyDate = GETDATE()-0 UNION ALL

SELECT GETDATE()-1 UNION ALL

SELECT GETDATE()-2 UNION ALL

SELECT GETDATE()-3 UNION ALL

SELECT GETDATE()-4 UNION ALL

SELECT GETDATE()-5 UNION ALL

SELECT GETDATE()-6 UNION ALL

SELECT GETDATE()-7 UNION ALL

SELECT GETDATE()-8 UNION ALL

SELECT GETDATE()-9 UNION ALL

SELECT GETDATE()-10 UNION ALL

SELECT GETDATE()-11 UNION ALL

SELECT GETDATE()-12) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

garyh2k3 SSC-Addicted Points: 456 More actions · Answer 4

This looks like the correct logic but it is in integer format. Is there a way for me to get the date of this calculation? For example display 8/6/2013, 7/30/2013 etc.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

Which post, Gary?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

garyh2k3 SSC-Addicted Points: 456 More actions · Answer 6

The group by ((DATEDIFF(day, getdate(), "datefield"))/7) solution.

batgirl SSCarpal Tunnel Points: 4979 More actions · Answer 7

garyh2k3 (8/13/2013)
This looks like the correct logic but it is in integer format. Is there a way for me to get the date of this calculation? For example display 8/6/2013, 7/30/2013 etc.

I'm not sure what you mean by "integer format" or "date of this calculation".

This is simply used to drive the groups - you can select whatever columns you want to display in the results accordingly. If you want to provide the scripts to create a table and insert some sample data as well as a representation of your expected output, I'll be happy to work on it further.

garyh2k3 SSC-Addicted Points: 456 More actions · Answer 8

My spreadsheet has sample data of grouped by month, grouped by Day. I would like the same date displayed for the group by the past 7 days. When I use the DATEDIFF example it returned numbers 0 - 12. I need the result to show the actual date for that day for that group. Similar to what is on the spreadsheet for month and day?

batgirl SSCarpal Tunnel Points: 4979 More actions · Answer 9

garyh2k3 (8/13/2013)
My spreadsheet has sample data of grouped by month, grouped by Day. I would like the same date displayed for the group by the past 7 days. When I use the DATEDIFF example it returned numbers 0 - 12. I need the result to show the actual date for that day for that group. Similar to what is on the spreadsheet for month and day?

I understand that you would like a date returned rather than an integer - but I don't know what your table looks like in order to answer that for you.

I need a create statement for the table and insert statement to populate it with sample data.

Then I can try to duplicate what your spreadsheet looks like only using the 7 day groups.

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 10

garyh2k3 (8/13/2013)
My spreadsheet has sample data of grouped by month, grouped by Day. I would like the same date displayed for the group by the past 7 days. When I use the DATEDIFF example it returned numbers 0 - 12. I need the result to show the actual date for that day for that group. Similar to what is on the spreadsheet for month and day?

Gary, I showed you how to fish and caught the first six for you. You want me to cook 'em too?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ScottPletcher SSC Guru Points: 101118 More actions · Answer 11

garyh2k3 (8/13/2013)
My spreadsheet has sample data of grouped by month, grouped by Day. I would like the same date displayed for the group by the past 7 days. When I use the DATEDIFF example it returned numbers 0 - 12. I need the result to show the actual date for that day for that group. Similar to what is on the spreadsheet for month and day?

I think the calc below should at least be close; adjust as needed:

DATEADD(DAY, DATEDIFF(DAY, GETDATE(), <date_in_table>) / 7 * 7,

DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS week_starting_date

For example:

SELECT

test_date,

DATEADD(DAY, DATEDIFF(DAY, GETDATE(), test_date) / 7 * 7,

DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS week_date

FROM (

SELECT GETDATE()- 0 AS test_date UNION ALL

SELECT GETDATE()- 1 UNION ALL

SELECT GETDATE()- 2 UNION ALL

SELECT GETDATE()- 3 UNION ALL

SELECT GETDATE()- 4 UNION ALL

SELECT GETDATE()- 5 UNION ALL

SELECT GETDATE()- 6 UNION ALL

SELECT GETDATE()- 7 UNION ALL

SELECT GETDATE()- 8 UNION ALL

SELECT GETDATE()- 9 UNION ALL

SELECT GETDATE()-10 UNION ALL

SELECT GETDATE()-11 UNION ALL

SELECT GETDATE()-12 UNION ALL

SELECT GETDATE()-13 UNION ALL

SELECT GETDATE()-14

) AS test_dates

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

batgirl SSCarpal Tunnel Points: 4979 More actions · Answer 12

select

max or min(testdate), --max or min depends on which date you want assigned to that 7 day range

sum(value1), --or whatever aggregate function is needed on a column

sum(value2), --etc.

sum(value3),

from table

group by ((DATEDIFF(day, getdate(), testdate))/7)