Kalimantan Kalimantan - 1 month ago 5
SQL Question

SQL Server: Get Count of past 7 days for each day

I want to get the count of the past 7 days for every day.

So:

updateDate Type ResultOfSqlStatement
2016-05-31 Thing1 5
2016-05-31 Thing2
2016-05-31 Thing3
2016-05-30 Thing1
2016-05-29 Thing2
2016-05-28 Thing1
2016-05-28 Thing3
2016-05-27 Thing1
2016-05-26 Thing1


I would have many more record spanning further back. I would want to get the count of all the occurrences of a Type for the last 7 days for each day. So for

2016-5-31


I would want the result column to have the sum of the previous seven days leading up to 5-31. Then for 5-30 I would want to do the same. I've tried:

SELECT
CONVERT(DATE,(DATEADD(ww, DATEDIFF(ww,0,update_date), 0))) As groupedweek,
type,
Count(type) total
FROM table
Group BY DATEADD(ww, DATEDIFF(ww,0,update_date), 0), type
ORDER BY DATEADD(ww, DATEDIFF(ww,0,update_date), 0)


That just gives me each week total starting from the first day, grouped by week.I want it to give I would want the result column to have the sum of the previous seven days leading up to 5-31

Answer

If 2012+ You can use the Window functions with the preceding clause

Declare @YourTable table (updateDate date,Type varchar(25))
Insert Into @YourTable values
('2016-05-31','Thing1'),
('2016-05-31','Thing2'),
('2016-05-31','Thing3'),
('2016-05-30','Thing1'),
('2016-05-29','Thing2'),
('2016-05-28','Thing1'), 
('2016-05-28','Thing3'), 
('2016-05-27','Thing1'),
('2016-05-26','Thing1')

Select *,ThingCount=sum(1) over(Partition By Type order by updateDate rows between 7 preceding and current row) 
 From  @YourTable

Returns

enter image description here