Weekly count splits to multiple rows

Question

I want to fetch count of items for a week from a table and I used the below query:

SELECT 
    count(*) As Total 
from MyTable 
WHERE convert(date,DateCreated)>='08/17/2015'
AND convert(date,DateCreated)<='08/23/2015'

I get the result as (which is correct):

    Total
    1149

Now I tried to rewrite the query to make it return the result on a weekly basis over a period of time and I noticed the count is not showing correctly. As a sample, I used the below query for same time period:

SELECT 
    count(*) AS Total,
    DATEPART(wk, DateCreated) AS WeekNumber,
    CAST(dateadd(ww, datediff(ww, 0, DateCreated), 0) AS date) as WeekStartDate,
    CAST(dateadd(ww, datediff(ww, 0, DateCreated), 6) AS date) as WeekEndDate
FROM MyTable 
WHERE convert(date,DateCreated) >= '08/17/2015'
and convert(date,DateCreated) <= '08/23/2015'
GROUP BY DATEPART(wk, DateCreated), CAST(dateadd(ww, datediff(ww, 0, DateCreated), 0) AS date), CAST(dateadd(ww, datediff(ww, 0, DateCreated), 6) AS date) 
ORDER BY DATEPART(wk, DateCreated)

The SUM of rows is returning the correct count, but it is expected to come as a single row only since my date selection (from '08/17/2015' to '08/23/2015') represents a week only, but the result as below shows as 2 weeks

    Total   WeekNumber  WeekStartDate   WeekEndDate
    1078    34           2015-08-17     2015-08-23
    71      35           2015-08-24     2015-08-30

Any known reason for this behaviour. Is it because of week calculation logic I am using? Or some other known issues.

`WHERE and convert(...)` ... your query has a typo. Is this what you actually ran? — Tim Biegeleisen, Aug 24 '17 at 13:21
You are grouping by the date funtion that makes your week info so it will split the result accordingly. — JohnHC, Aug 24 '17 at 13:21
@JohnHC yes i wants to group result based on week . But why here its breaking result as 2 weeks [see my inouts represents a single week only] — Sebastian, Aug 24 '17 at 13:23

Tab Alleman · Answer 1 · 2017-08-24T13:43:16.337

3

You are Grouping By DATEPART(wk...

This uses the DATEFIRST setting of your server. It is not the same as the way you are calculating WeekStartDate and WeekEndDate, so it will come up with a different WeekNumber than you are expecting, unless your DATEFIRST value is set to coincide.

In 2015, Aug 17 was a Monday, and Aug 23 was a Sunday. Do you have your DATEFIRST Set to Monday? The default is Sunday.

See the "Week and DatePart Arguments" section of the MS Documentation.

EDIT: This answer provides some detailed explanation and tips for getting the first day of a week based on your current DATEFIRST value.

edited Aug 24 '17 at 13:43

answered Aug 24 '17 at 13:24

Tab Alleman

31,483
7
36
52

SELECT @@DATEFIRST; returns 7 on my server . So how can i mention the Datefirst as Monday while writing the above query – Sebastian Aug 24 '17 at 13:27
I just added link in the 3rd paragraph showing how to SET DATEFIRST. – Tab Alleman Aug 24 '17 at 13:28
I executed SET DATEFIRST 7; [ i assumes it will set to Monday ] and executed above query , but still i am getting it in 2 rows – Sebastian Aug 24 '17 at 13:32
@JibinMathew Datefirst 7 = Sunday. Datefirst 1 = Monday. – Tab Alleman Aug 24 '17 at 13:32
oops sorry i mean SET to 1 Monday as you said , but still the same i am getting it as 2 rows – Sebastian Aug 24 '17 at 13:34
1

Oops I was wrong. Your query DOES use DATEDIFF(), and that function ignores DATEFIRST and uses Sunday to calculate weeks. You need to find another way to calculate WeekStartDate and WeekEndDate without using DateDiff() – Tab Alleman Aug 24 '17 at 13:37

SqlZim · Accepted Answer · 2017-08-24T16:08:46.193

Since Tab did not end up provided a working solution, here is one:

set datefirst 1;

select 
    count(*) as Total
  , datepart(wk, DateCreated) as WeekNumber
  , convert(date,dateadd(week,datediff(day,0,DateCreated )/7,0)) as WeekStartDate
  , convert(date,dateadd(week,datediff(day,0,DateCreated )/7,6)) as WeekEndDate
from MyTable 
where convert(date,DateCreated) >= '20150817' --'08/17/2015'
  and convert(date,DateCreated) <= '20150823' --'08/23/2015'
group by 
    datepart(wk, DateCreated)
  , convert(date,dateadd(week,datediff(day,0,DateCreated )/7,0)) 
  , convert(date,dateadd(week,datediff(day,0,DateCreated )/7,6)) 
order by datepart(wk, DateCreated)

Simple calendar example:

declare @FromDate date = '20150817';
declare @ThruDate date = '20150831';
;with cal as (
  select top (1+datediff(day, @FromDate, @ThruDate))
      DateValue = convert(date,dateadd(day,row_number() over (order by (select 1))-1,@FromDate))
  from [master]..spt_values v
  order by DateValue
)
select 
    [Date]     = convert(char(10),DateValue,120)
  , weekday    = datename(WeekDay,DateValue)
  , WeekMonday = convert(date,dateadd(week,datediff(day,0,datevalue )/7,0))
  , WeekSunday = convert(date,dateadd(week,datediff(day,0,datevalue )/7,6))
from cal

rextester demo: http://rextester.com/TYJ50600

returns:

+------------+-----------+------------+------------+
|    Date    |  weekday  | WeekMonday | WeekSunday |
+------------+-----------+------------+------------+
| 2015-08-17 | Monday    | 2015-08-17 | 2015-08-23 |
| 2015-08-18 | Tuesday   | 2015-08-17 | 2015-08-23 |
| 2015-08-19 | Wednesday | 2015-08-17 | 2015-08-23 |
| 2015-08-20 | Thursday  | 2015-08-17 | 2015-08-23 |
| 2015-08-21 | Friday    | 2015-08-17 | 2015-08-23 |
| 2015-08-22 | Saturday  | 2015-08-17 | 2015-08-23 |
| 2015-08-23 | Sunday    | 2015-08-17 | 2015-08-23 |
| 2015-08-24 | Monday    | 2015-08-24 | 2015-08-30 |
| 2015-08-25 | Tuesday   | 2015-08-24 | 2015-08-30 |
| 2015-08-26 | Wednesday | 2015-08-24 | 2015-08-30 |
| 2015-08-27 | Thursday  | 2015-08-24 | 2015-08-30 |
| 2015-08-28 | Friday    | 2015-08-24 | 2015-08-30 |
| 2015-08-29 | Saturday  | 2015-08-24 | 2015-08-30 |
| 2015-08-30 | Sunday    | 2015-08-24 | 2015-08-30 |
| 2015-08-31 | Monday    | 2015-08-31 | 2015-09-06 |
+------------+-----------+------------+------------+

This works well. Does the date formats in where condition like '20150817' AND '08/17/2015' makes any difference or impact in result set — Sebastian, Aug 25 '17 at 03:54
@JibinMathew It depends on language/date settings, I just swapped it for the safe format. The only truly safe formats for date/time literals in SQL Server, at least for `datetime` and `smalldatetime`, are: `YYYYMMDD` and `YYYY-MM-DDThh:mm:ss[.nnn]` - [Bad habits to kick : mis-handling date / range queries - Aaron Bertrand](https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries) — SqlZim, Aug 25 '17 at 12:08

score 0 · Answer 3 · edited Aug 24 '17 at 14:26

0

You should use iso_week as a datepart, look at this:

 select 
     datepart(wk,'20150817'),  
     datepart(wk,'20150823'),
     datepart(iso_week,'20150817'),  
     datepart(iso_week,'20150823')

edited Aug 24 '17 at 14:26

Eli

2,538
1
25
36

answered Aug 24 '17 at 13:52

sepupic

8,409
1
9
20

Weekly count splits to multiple rows

3 Answers3