TSQL Number of last rolling 10 weeks
I have a table as simple as two columns Name, Date is a table that has "leads" in it when the user asks from our site.
Do I need to create a dataset of lead count for the last 10 weeks of rolling?
The expected output should 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)
etc. it will have 10 lines
its like sliding a week ago and the sum of the last 10 weeks from there
any help?
source to share
This is exactly what the windowing clause is supposed to do. However, first you need to collect data:
select date, count(*) as cnt
from simple s
group by date;
Assuming you have data every day, you need 70 days. You get this by adding up the count over 70 days. It will 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;
This works for several days. To get this week by week you need to do something to convert days to weeks and then change the windowing clause. You don't define the 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;
source to share
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 the counter of the week window, i.e. for a week box containing the last 10 weeks starting from the current date of the week 0
, 1
for a week box starting from the previous week, etc.
weekDiff
is a calculated column containing the [Date] offset for each weekSlide
. Negative offset implies [Date], which is outside the window of the week.
source to share
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: While Brian's question (comment added to your original question) is a good one - if it's anything other than "rollback from today" then you need to add another line of logic to the definition @end_date
.
source to share