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

Re: how many new IDs participated

X-Received	by 10.224.137.68 with SMTP id v4mr5325075qat.1.1376471901527; Wed, 14 Aug 2013 02:18:21 -0700 (PDT)
X-Received	by 10.49.70.138 with SMTP id m10mr42733qeu.9.1376471901513; Wed, 14 Aug 2013 02:18:21 -0700 (PDT)
Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!f7no2319079qan.0!news-out.google.com!he10ni1415qab.0!nntp.google.com!fx3no2433368qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Wed, 14 Aug 2013 02:18:21 -0700 (PDT)
In-Reply-To	<492c4449-7052-4337-a9b1-ddee6371ba20@googlegroups.com>
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=50.166.4.111; posting-account=SOVadwoAAAB3h7W1MLW9kMYtEc2JW2L8
NNTP-Posting-Host	50.166.4.111
References	<492c4449-7052-4337-a9b1-ddee6371ba20@googlegroups.com>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<b189b500-6a6e-49bf-abac-3be46e0164fe@googlegroups.com> (permalink)
Subject	Re: how many new IDs participated
From	Ross Presser <rpresser@gmail.com>
Injection-Date	Wed, 14 Aug 2013 09:18:21 +0000
Content-Type	text/plain; charset=ISO-8859-1
Xref	csiph.com comp.databases.ms-sqlserver:1541

Show key headers only | 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