SQL grouping results by first date of the week

Question

Currently writing an sql query on sql server 2008 and have a query which counts the amount of sales in a week, currently my code is grouping by week number however I am wanting to return the first date in the week.

currently it's returning

week number count
1             5
2             3
3             8

what I'm trying to get it to do is

Week Number Date Count
1     1/01/2015    5
2     7/01/2015    3
3     14/01/2015   8

Currently the code I have is

SELECT DATEPART(WK,CAST(a.sale_date AS DATE)), count(a.sale)
from sales a
where a.sale_date >= Dateadd(DAY, Datediff(Day, 0, DATEADD(WEEK, -52, current_timestamp)), 0)
group by DATEPART(WK,CAST(a.sale_date AS DATE))

Any time I try and add the sale_date column it prints out similar to the following

Week Date count
1  1/01/2015 2
1  2/01/2015 1
1  5/01/2015 2
2  7/01/2015 1
2  7/01/2015 2

etc.

score 2 · Accepted Answer · edited May 23 '17 at 11:51

2

A similar question was asked before here. In summary, you need to get the current day of week from the date and then subtract a number of days in order to get the first day of the week, like so:

SELECT DATEPART(WK,CAST(a.sale_date AS DATE)), 
max(DATEADD(dd, -(DATEPART(dw, a.sale_date) - 1), a.sale_date)) as WeekStart, sum(1) as Count 
from testdata a
where a.sale_date >= Dateadd(DAY, Datediff(Day, 0, DATEADD(WEEK, -52, current_timestamp)), 0)
group by DATEPART(WK,CAST(a.sale_date AS DATE));

Edit: Notice how you used count instead of sum, this was your main issue as count counts all distinct values, whereas sum just gives you a sum of rows in that grouping.

SQLFiddle here

edited May 23 '17 at 11:51

Community

1
1

answered May 26 '15 at 21:19

Duffmaster33

1,160
9
16

Hi when I implement the code above I receive the same error message as earlier – user1393064 May 27 '15 at 00:00
I see. And what is that error message? I don't see anything about an error message in your question – Duffmaster33 May 27 '15 at 14:28
Error message was a poor word choice, apologies. It is printing out multiple dates for week 1/week2/week3 similar to the last example in the o.p – user1393064 May 27 '15 at 21:11
Yes, sorry, there's an error in your original SQL I missed. You don't want to use count, but rather sum to get a total of the values. I have updated my answer, please mark it right if I helped you – Duffmaster33 May 27 '15 at 21:22

score 0 · Answer 2 · answered May 26 '15 at 22:05

Looks like you are heading in the right direction, I think you would just need to go a little further after you add the sale_date column to your query and do something like this:

SELECT t.WEEK_NUMBER
    ,MIN(t.Sale_date) OVER (
        PARTITION BY t.WEEK_NUMBER ORDER BY t.WEEK_NUMBER
        ) as Week_StartDate
    ,SUM(t.Amount_of_Sales_Count) Amount_of_Sales_Total_Count
FROM (
    SELECT DATEPART(week, CAST(a.sale_date AS DATE)) Week_Number
        ,sale_date
        ,count(a.sale) Amount_of_Sales_Count
    FROM sales a
    WHERE a.sale_date >= 
              Dateadd(DAY, Datediff(Day, 0, DATEADD(WEEK, - 52, current_timestamp)), 0)
        GROUP BY DATEPART(WK, CAST(a.sale_date AS DATE))
            ,sale_date
        ) t
    GROUP BY t.WEEK_NUMBER

SQL grouping results by first date of the week

2 Answers2