I have a table that stores customer information including their registration date with the service. I would like to know the total number of subscribed customers (from the beginning of the service up until that day) at the end of the day for the past 10 days.

I imagine the solution would be something similar to a for loop, so the best I managed so far is the following:

DECLARE @Counter INT 
SET @Counter=1
WHILE ( @Counter <= 10)
BEGIN
   select try_convert(date, getdate()-@Counter), count(id)
   from Table_name
   where try_convert(date, registrationdate) <=  try_convert(date, getdate()-@Counter)
    SET @Counter  = @Counter    1
END

The problem with the above script is that it produces 10 different tables and I haven't been able to union them together. I'm fairly new to SQL so there might be a simpler solution that I'm missing!

The output I have in mind looks like this:

CodePudding user response：

It has nothing to do with a loop. You would simply create an SQL select that selects them. ie:

select * 
from myTable 
where datediff(day, registrationDate, getdate()) <= 10;

EDIT: Question is edited to show the desired output now, then it would need a different way like:

select registrationDate, count(*) as TotalNumberRegistered 
    from myTable 
    where datediff(day, registrationDate, getdate()) <= 10
    group by registrationDate;

EDIT: If as P.Salmon said you were after a "running total" then you still can get it with a slight modification to code:

WITH dailyTotals(registrationDate, TotalNumberRegistered) AS 
     (
        SELECT   registrationDate, COUNT(*)
        FROM     myTable
        WHERE    DATEDIFF(DAY, registrationDate, GETDATE())<=10
        GROUP BY registrationDate
     )
SELECT registrationDate
   , SUM(TotalNumberRegistered) OVER (ORDER BY registrationDate) AS TotalNumberRegistered
FROM dailyTotals
Order by registrationDate desc; -- you want it to be ordered last date first? 

Here is DBFiddle demo

CodePudding user response：

You can use temp table, GROUP BY and INNER JOIN:

select try_convert(date, registrationdate) as registrationdate, count(1) as cnt
into #tmp
from Table_name
group by try_convert(date, registrationdate)

select top 10 t1.registrationdate, SUM(1) as total
from #tmp t1
inner join #tmp t2 on t1.registrationdate >= t2.registrationdate
group by t1.registrationdate
order by t1.registrationdate desc