CODEWITHSUNDEEP
sql-servert-sql
Petrik
Petrik

Reputation: 827

T-SQL count of unique records by each month

I am trying to get the count by which our customer base grew each month.

Eg. "Josh","Tim" and "Dustin" has used the service in January, so for January the number of new unique customers would be 3.

In February the "Josh","Tim" and "Eve" would use the service. As "Josh" and "Tim" has used the service before, the number of new unique customers would be 1.

And so on....

I wanted to use EXCEPT statement, but obviously this is not getting the right results.

SELECT COUNT(DISTINCT Name) as NewUniqueCustomers, convert(varchar(7), RegDate, 126)
FROM T
GROUP BY convert(varchar(7), RegDate, 126)
EXCEPT 

--This should excludie all customers which was included previously
SELECT COUNT(DISTINCT Name)as NewUniqueCustomers, convert(varchar(7), RegDate, 126)
FROM T 
WHERE convert(varchar(7), DATEADD(month,-1,RegDate) , 126)

GROUP BY convert(varchar(7), RegDate, 126)

http://sqlfiddle.com/#!3/73621

Upvotes: 0

Views: 370

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

Using your sqlfiddle this should do it.

with SortedData as
(
    select *
        , ROW_NUMBER() over(partition by Name order by RegDate) as RowNum
    from t
)

select dateadd(month, datediff(month, 0, RegDate), 0) 
    , COUNT(Name)
from SortedData sd
where sd.RowNum = 1
group by dateadd(month, datediff(month, 0, RegDate), 0)

--EDIT--

Given your comment about needing to pull from two tables with a union why not something like this?

with SortedData as
(
    select *
        , ROW_NUMBER() over(partition by Name order by RegDate) as RowNum
    from 
    (
        SELECT * FROM ProductionTable 
        UNION ALL 
        SELECT * FROM Archive.dbo.T
    )
    x
)

Upvotes: 4

Related Questions