Post reply

How to Group by the Past Seven Days

  • 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.

  • August 13, 2013 at 9:06 am

    #1640984

    You could use something like this:

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

  • August 13, 2013 at 9:13 am

    #1640989

    Stefan beat me to it - same answer here.

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

  • August 13, 2013 at 9:25 am

    #1640999

    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

  • August 13, 2013 at 9:32 am

    #1641006

    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.

  • August 13, 2013 at 9:47 am

    #1641022

    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]

  • August 13, 2013 at 9:53 am

    #1641028

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

  • August 13, 2013 at 10:11 am

    #1641034

    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.

  • August 13, 2013 at 11:23 am

    #1641051

    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?

  • August 13, 2013 at 12:02 pm

    #1641063

    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.

  • August 13, 2013 at 12:57 pm

    #1641086

    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]

  • August 13, 2013 at 1:47 pm

    #1641111

    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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • August 13, 2013 at 1:56 pm

    #1641115

    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)

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

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