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

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

Re: how many new IDs participated

Show all headers | View raw

On Wednesday, August 14, 2013 2:18:21 AM UTC-7, Ross Presser wrote:
> 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.

Thank you!
Works perfectly well.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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