Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1541

Re: how many new IDs participated

Show all headers | View raw

On Tuesday, August 13, 2013 9:43:44 PM UTC-4, migurus wrote:
> I have a series of Customer ID / Date records showing participation of customers on the daily basis. I need to find out how many new customers participated each day.
[snip]
> Any ideas? I was trying to self-join but can not quite wrap my brain around it.
> 
> Thanks in advance.

WITH X1 AS (
SELECT ID, MIN(DT) AS FirstDT
FROM @T AS T 
GROUP BY ID
),
X2 AS (
SELECT DISTINCT DT FROM @T AS T
)
SELECT X2.DT, COUNT(X1.ID)
FROM X2 
LEFT JOIN X1 ON X2.DT = X1.FirstDT
GROUP BY X2.DT

DT         
---------- -----------
2013-08-07 2
2013-08-08 0
2013-08-09 1
2013-08-12 0
2013-08-13 1
Warning: Null value is eliminated by an aggregate or other SET operation.

(5 row(s) affected)

The value for 2013-08-09 is accurate; look closely at your data, 332551 is represented twice on that day.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

how many new IDs participated migurus <migurus@yahoo.com> - 2013-08-13 18:43 -0700
  Re: how many new IDs participated Ross Presser <rpresser@gmail.com> - 2013-08-14 02:18 -0700
    Re: how many new IDs participated migurus <migurus@yahoo.com> - 2013-08-14 13:27 -0700

csiph-web