CODEWITHSUNDEEP
sqlsql-servert-sql
joe
joe

Reputation: 1473

How to query records 60 days prior to today

I have a scenario where I need to identify the unique sales person on 12/1/20. Of those identified on this date, how many sales person had sold products in the prior 60 days period.

Repeat the same process for 12/2/20, of those sales persons, how many sales person had sold products in the prior 60 days period before 12/2/20. so on and so on until 3/31/21. I was able to do in one date or specific dates. Is there a while loop or something in similar in SQL sever that can tackle this task?

Example:

person      sold on     Prior 60 days Period (10/2/20 - 11/30/20)
Nancy       12/1/2020   Nancy
Henry       12/1/2020
Rany        12/1/2020   Rany

person      sold on     Prior 60 days Period (10/3/20 - 12/01/20)
Tom         12/2/2020
Mar         12/2/2020   Mar
Young       12/2/2020   Young

Sample one day query. It is very tedious.

SELECT distinct
CONVERT(VARCHAR(10),date,101) AS date,
Person_id
into #test
FROM person
WHERE date >='12/1/20'
AND date <'12/2/20'

Select count (distinct a.Person_id) as ct
from #test a inner join person b
on Person_id= Person_id
and b.date>='10/2/20'
and b.date < '12/1/20'

Upvotes: 0

Views: 1645

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

One method is to use a list of dates and, say, correlated subqueries. An easy way to construct the dates (if you don't have a dates table) is a recursive CTE:

with dates as (
      select convert(date, '2020-12-01') as dte
      union all
      select dateadd(day, 1, dte)
      from dates
     )
select d.dte,
       (select count(distinct s.person)
        from sales s
        where s.sold_on <= d.dte and
              s.sold_on > dateadd(day, -60, d.dte)
      ) as cnt_sales_persons
from dates d
group by d.dte
option (maxrecursion 0);

Upvotes: 1

Related Questions