Weekly unit with CTE does not behave as expected
I have a table USERS
with users that can be of two different types (A and B). I need to show an aggregate report for each type for each week. The request that I have been working well so far, except for a few weeks, is not grouping properly. In the example below, the week starting January 28 should have one row, not two.
Week Starts |Week| Type A | Type B
------------+----+--------+------
2013-02-04 | 14 | 2 | 26
2013-01-28 | 13 | 5 | 191
2013-01-28 | 13 | 0 | 24
2013-01-21 | 12 | 1 | 134
2013-01-21 | 12 | 0 | 20
2013-01-14 | 11 | 1 | 143
2013-01-14 | 11 | 0 | 2
2013-01-07 | 10 | 0 | 233
2013-01-07 | 10 | 0 | 23
2012-12-31 | 9 | 0 | 12
2012-12-31 | 9 | 4 | 164
2012-12-31 | 9 | 0 | 20
SQL
;with cte as
(
select DATEADD(m,-3,GETDATE()) firstday, DATEADD(m,-3,GETDATE()) + 6 - DATEDIFF(day, 0, DATEADD(m,-3,GETDATE())) %7 lastday, 1 week
union all
select lastday + 1, case when GETDATE() < lastday + 7 then GETDATE() else lastday + 7 end, week + 1
from cte
where lastday < GETDATE()
)
SELECT
cast(firstday as date) 'Week Starts',
cte.week as 'Week',
Sum(CASE WHEN USR_TYPE = 'A' THEN 1 ELSE 0 END) As 'Type A',
Sum(CASE WHEN USR_TYPE = 'B' THEN 1 ELSE 0 END) As 'Type B'
FROM cte left join USERS
ON cte.firstday <= USERS.CREATED
AND cte.lastday > USERS.CREATED
GROUP BY cte.week, cte.firstday, cte.lastday, DATEPART(YEAR,USERS.CREATED), DATEPART(wk,USERS.CREATED)
ORDER BY week desc
What am I doing wrong?
source to share
Without seeing any data from the table users
, I'm going to guess.
The list of dates you generate in the CTE includes time
.
You may need cast()
your values firstday
and lastday
like either date
, or create a list without time.
See Demo SQL Fiddle
Example from CTE and new dates:
| CASTFIRSTDAY | CASTLASTDAY | WEEK | FIRSTDAY | LASTDAY |
---------------------------------------------------------------------------------------------------------
| 2012-11-05 | 2012-11-11 | 1 | November, 05 2012 20:08:10+0000 | November, 11 2012 20:08:10+0000 |
| 2012-11-12 | 2012-11-18 | 2 | November, 12 2012 20:08:10+0000 | November, 18 2012 20:08:10+0000 |
| 2012-11-19 | 2012-11-25 | 3 | November, 19 2012 20:08:10+0000 | November, 25 2012 20:08:10+0000 |
| 2012-11-26 | 2012-12-02 | 4 | November, 26 2012 20:08:10+0000 | December, 02 2012 20:08:10+0000 |
| 2012-12-03 | 2012-12-09 | 5 | December, 03 2012 20:08:10+0000 | December, 09 2012 20:08:10+0000 |
| 2012-12-10 | 2012-12-16 | 6 | December, 10 2012 20:08:10+0000 | December, 16 2012 20:08:10+0000 |
You can edit the CTE to only return values date
:
;with cte as
(
select
cast(DATEADD(m,-3,GETDATE()) as date) firstday,
cast(DATEADD(m,-3,GETDATE()) + 6 - DATEDIFF(day, 0, DATEADD(m,-3,GETDATE())) %7 as DATE) lastday,
1 week
union all
select
cast(DATEADD(DAY, 1, lastday) as date),
case
when cast(GETDATE() as date) < cast(DATEADD(DAY, 7, lastday) as date)
then cast(GETDATE() as date)
else cast(DATEADD(DAY, 7, lastday) as date)
end,
week + 1
from cte
where cast(lastday as date) < cast(GETDATE() as date)
)
select *
from cte
source to share