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>
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

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.