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

Re: how many new IDs participated

X-Received	by 10.224.64.202 with SMTP id f10mr12073291qai.2.1376512030207; Wed, 14 Aug 2013 13:27:10 -0700 (PDT)
X-Received	by 10.50.83.6 with SMTP id m6mr218795igy.1.1376512030134; Wed, 14 Aug 2013 13:27:10 -0700 (PDT)
Path	csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!newsfeed.hal-mli.net!feeder1.hal-mli.net!npeer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!fx3no2493504qab.0!news-out.google.com!he10ni1979qab.0!nntp.google.com!fx3no2493497qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Wed, 14 Aug 2013 13:27:09 -0700 (PDT)
In-Reply-To	<b189b500-6a6e-49bf-abac-3be46e0164fe@googlegroups.com>
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=216.14.58.122; posting-account=PG2dbQkAAADVILsQ0GhgAM6hZK18SIjs
NNTP-Posting-Host	216.14.58.122
References	<492c4449-7052-4337-a9b1-ddee6371ba20@googlegroups.com> <b189b500-6a6e-49bf-abac-3be46e0164fe@googlegroups.com>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<8b5a1edb-43f2-4975-a211-b7bdf7aa5fb7@googlegroups.com> (permalink)
Subject	Re: how many new IDs participated
From	migurus <migurus@yahoo.com>
Injection-Date	Wed, 14 Aug 2013 20:27:10 +0000
Content-Type	text/plain; charset=ISO-8859-1
X-Received-Bytes	2344
Xref	csiph.com comp.databases.ms-sqlserver:1542

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