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

Question

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.

Answer 1

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