TSQL Count of last rolling 10 weeks

Question

I have a table as simple as two columns Name, Date -- It is a table that has "Leads" in it when user inquiries from our website.

I need to produce a data set of lead count of last 10 rolling weeks?

The expected output is suppose to be

Starting Week -- Count
==============
10     SUM(Last 10 weeks)
9    SUM(Starting from Week 9 for Last 10 weeks)
8    SUM(Starting from Week 8 for Last 10 weeks)

and so on.. it will have 10 rows

its like sliding a week back and sum the last 10 weeks from there

any help?

Answer 1

This is exactly what the windowing clause is supposed to do. However, you first need to aggregate the data:

select date, count(*) as cnt
from simple s
group by date;

Assuming that you have data on every day, then you want 70 days worth of counts. You get that by summing the counts for 70 days. That would be:

select date, count(*) as cnt,
       sum(count(*)) over
           (order by date rows between 69 preceding and current row)
from simple s
group by date;

That works for days. To get this by weeks, you need to do something to convert days to weeks and then change the windowing clause. You don't define a week, but this might be close to what you want:

select dateadd(day, 1 - datepart(weekday, date), date) as weekstart,
       count(*) as cnt,
       sum(count(*)) over
           (order by dateadd(day, 1 - datepart(weekday, date), date) rows between 9 preceding and current row)
from simple s
group by dateadd(day, 1 - datepart(weekday, date), date)
order by weekstart;

Answer 2

You can use the following query:

SELECT Name,
       weekSlide + 1 AS weekSlide,
       COUNT(CASE WHEN weekDiff BETWEEN 0 AND 9 THEN 1 END) AS [Count]
FROM (
  SELECT Name,
         DATEPART(week, [Date]) AS weekNo,
         (x.c - DATEPART(week, [Date])) + 
         (YEAR(GETDATE()) - DATEPART(year, [Date])) * 52 - 
         y.weekSlide AS weekDiff,      
         y.weekSlide
  FROM mytable
  CROSS JOIN (SELECT DATEPART(week, GETDATE())) x(c) -- current week number
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS y(weekSlide)) t
GROUP BY Name, weekSlide

weekSlide is a counter of the week window, i.e. it is 0 for the week window containing the last 10 weeks starting from current date's week, 1 for the week window starting from previous week, etc.

weekDiff is a computed column containing the offset of [Date] for each weekSlide. A negative offset implies a [Date] that lies past the week window.

Demo here

Answer 3

I would use:

DECLARE @week INT, @end_date DATE
DECLARE @results AS TABLE (starting_week INT, count INT)

SET @week = 0
WHILE @week < 10
BEGIN
    SELECT @end_date = DATEADD(dd, @week * (-7), GETDATE())
    INSERT INTO @results
    SELECT (10 - @week),
           COUNT(1) FROM Leads
               WHERE Date BETWEEN (DATEADD(dd, -70, @end_date)) AND @end_date
    @week = @week + 1
END

SELECT * FROM @results

Edit: although Brian's question (comment added to your initial question) is a good one - if it's anything other than "rolling back from today", then you'd need to add another line of logic to the definition of @end_date.

Answer 4

You can do a Cumulative Sum on the Count like this:

select DATEPART(week, [Date]) AS weekNo
  ,sum(count(*)) 
   over (order by DATEPART(week, [Date])
         rows unbounded preceding)
from tab
where ...
group by DATEPART(week, [Date])