Customers count on first of the month report

Question

Customers count on first of the month report

mario.it

Valued Member

Points: 62
More actions
August 9, 2021 at 7:07 pm

#3918240

Hi all!
I'm a t-sql beginner and have to generate a report as it follows:
First_of_Month | Customers Count
2020-01-01 100
2020-02-01 180
...........
2021-01-01 379
...........
2021-08-01 500
On each first of month it should count the total number of customers in that moment.
declare @first DATE = '2020-01-01';
declare @counter INT =0;
while @counter <= 20
begin
select dateadd(month, @counter,@first)
count(distinct customerid)
insert into dbo.report
from sales
where saledate <= @first
set @counter = @counter + 1
end;
How to do this with one SQL query? Something is not ok now and I cannot generate properly.
Thank you so much!
Mario

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100950 More actions · Answer 1

DECLARE @first_sales_month date = '20200101'
DECLARE @last_sales_month date  = '20201201'

SET @first_sales_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, @first_sales_month, 0)
SET @last_sales_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, @last_sales_month, 0)

SELECT
    DATEADD(MONTH, DATEDIFF(MONTH, 0, @last_sales_month), 0) AS sales_month,
    COUNT(DISTINCT customerid) AS distinct_customer_count
FROM dbo.sales
WHERE saledate >= @first_sales_month AND saledate < DATEADD(MONTH, 1 @last_sales_month)

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

ktflash Default port Points: 1400 More actions · Answer 2

Does it have to be a query using variables etc?

In theory you can get the first of each month and then do a group by with your distinct count of all rows older than.

select FIRST_OF_MONTH
      ,count(distinct customer_ID)
from dbo.sales a
/*Get First of Month, Timetable preferable instead of sales*/
      inner join (select FIRST_OF_MONTH=DATEADD(DAY,1,EOMONTH(Date_Column,-1))
    from dbo.sales
    group by DATEADD(DAY,1,EOMONTH(Date_Column,-1))) FOM on a.Date_Column <= FOM.FIRST_OF_MONTH
group by FIRST_OF_MONTH
order by 1

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

kaj SSCommitted Points: 1580 More actions · Answer 3

or maybe

declare @first DATE = '20200101';
insert into dbo.report (First_Of_Month, Customers_Count)
select top(20) 
    dateadd(d,1-day(saledate),saledate) as First_Of_Month, 
    count(distinct customerid) as Customers_Count
from Sales
where saledate >= @first
group by dateadd(d,1-day(saledate),saledate)
order by First_Of_Month desc