need first occurance of each date from a table to a new table

[sparks]

customerID PurchaseDate PurchaseID
000000011 2008-09-30 00:00:00 50
000000011 2008-10-30 00:00:00 175
000000011 2008-03-20 00:00:00 42
000000022 2008-03-20 00:00:00 209
000000022 2008-03-20 00:00:00 223
000000022 2008-03-20 00:00:00 312
000000022 2008-03-20 00:00:00 391
000000022 2008-03-20 00:00:00 430
000000022 2008-03-20 00:00:00 553
000000044 2008-09-30 00:00:00 50
000000044 2008-10-30 00:00:00 50
000000044 2008-11-30 00:00:00 50
000000044 2008-11-30 00:00:00 50
000000044 2008-11-30 00:00:00 50
000000044 2008-11-30 00:00:00 50




I need to make a new table from this data.
Its the only place where they have stored the customerID and date but
I only need the first occurance of each date.

so the output would be something like this.

customerID PurchaseDate
000000011 2008-09-30 00:00:00
000000011 2008-10-30 00:00:00
000000011 2008-03-20 00:00:00
000000022 2008-03-20 00:00:00
000000044 2008-09-30 00:00:00
000000044 2008-10-30 00:00:00
000000044 2008-11-30 00:00:00

I did find a routine online but I am unsure what to do with it.

WITH CTE
AS (SELECT customerID,
PurchaseDate,
PurchaseID,
ROW_NUMBER() OVER (PARTITION BY PurchaseDate ORDER BY customerID asc ) AS Row
FROM dbo.tblCustomerPurchases)
SELECT customerID ,
PurchaseDate,
PurchaseID
FROM CTE
WHERE Row = 1 ;

yes this seems to output the correct data but I am unsure how to write this to a new table.

[sparks]

Ok I think I have it.
I had to do a double inner join with another table.
Wow its monday and I got something right.
I am scared now LOL

Post by sparks
customerID PurchaseDate PurchaseID
000000011 2008-09-30 00:00:00 50
000000011 2008-10-30 00:00:00 175
000000011 2008-03-20 00:00:00 42
000000022 2008-03-20 00:00:00 209
000000022 2008-03-20 00:00:00 223
000000022 2008-03-20 00:00:00 312
000000022 2008-03-20 00:00:00 391
000000022 2008-03-20 00:00:00 430
000000022 2008-03-20 00:00:00 553
000000044 2008-09-30 00:00:00 50
000000044 2008-10-30 00:00:00 50
000000044 2008-11-30 00:00:00 50
000000044 2008-11-30 00:00:00 50
000000044 2008-11-30 00:00:00 50
000000044 2008-11-30 00:00:00 50
I need to make a new table from this data.
Its the only place where they have stored the customerID and date but
I only need the first occurance of each date.
so the output would be something like this.
customerID PurchaseDate
000000011 2008-09-30 00:00:00
000000011 2008-10-30 00:00:00
000000011 2008-03-20 00:00:00
000000022 2008-03-20 00:00:00
000000044 2008-09-30 00:00:00
000000044 2008-10-30 00:00:00
000000044 2008-11-30 00:00:00
I did find a routine online but I am unsure what to do with it.
WITH CTE
AS (SELECT customerID,
PurchaseDate,
PurchaseID,
ROW_NUMBER() OVER (PARTITION BY PurchaseDate ORDER BY customerID asc ) AS Row
FROM dbo.tblCustomerPurchases)
SELECT customerID ,
PurchaseDate,
PurchaseID
FROM CTE
WHERE Row = 1 ;
yes this seems to output the correct data but I am unsure how to write this to a new table.

[Gene Wirchenko]

Post by sparks
Ok I think I have it.
I had to do a double inner join with another table.
Wow its monday and I got something right.
I am scared now LOL

Your solution is WAY too complex for what you posted.

[snip]

Sincerely,

Gene Wirchenko

[Gene Wirchenko]

On Mon, 06 Aug 2012 07:00:34 -0500, sparks <***@home.com> wrote:

[snipped data]

Post by sparks
I need to make a new table from this data.
Its the only place where they have stored the customerID and date but
I only need the first occurance of each date.

"first" is not meaningful in a set. You have to specify an
order.

Post by sparks
so the output would be something like this.
customerID PurchaseDate
000000011 2008-09-30 00:00:00
000000011 2008-10-30 00:00:00
000000011 2008-03-20 00:00:00
000000022 2008-03-20 00:00:00
000000044 2008-09-30 00:00:00
000000044 2008-10-30 00:00:00
000000044 2008-11-30 00:00:00

However, this just looks to be each unique date. If you required
other columns, then it would get more complex.

Post by sparks
I did find a routine online but I am unsure what to do with it.

[snip]

Well, you can snip it like I did. <G> It does look overly
complex.

Post by sparks
yes this seems to output the correct data but I am unsure how to write this to a new table.

Here is my sample code. The select you appear to want is at the
very end.

***** Start of Example Code *****
use tempdb
go

drop table #Posting

create table #Posting
(
customerID int not null,
PurchaseDate datetime2 not null,
PurchaseId int not null
)

insert into #Posting
(customerID,PurchaseDate,PurchaseID)
values
(000000011,'2008-09-30 00:00:00',50),
(000000011,'2008-10-30 00:00:00',175),
(000000011,'2008-03-20 00:00:00',42),
(000000022,'2008-03-20 00:00:00',209),
(000000022,'2008-03-20 00:00:00',223),
(000000022,'2008-03-20 00:00:00',312),
(000000022,'2008-03-20 00:00:00',391),
(000000022,'2008-03-20 00:00:00',430),
(000000022,'2008-03-20 00:00:00',553),
(000000044,'2008-09-30 00:00:00',50),
(000000044,'2008-10-30 00:00:00',50),
(000000044,'2008-11-30 00:00:00',50),
(000000044,'2008-11-30 00:00:00',50),
(000000044,'2008-11-30 00:00:00',50),
(000000044,'2008-11-30 00:00:00',50)

select customerID,PurchaseDate from #Posting
group by customerID,PurchaseDate
order by customerID,PurchaseDate
***** End of Example Code *****

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by sparks
I did find a routine online but I am unsure what to do with it.
WITH CTE
AS (SELECT customerID,
PurchaseDate,
PurchaseID,
ROW_NUMBER() OVER (PARTITION BY PurchaseDate ORDER

BY customerID asc ) AS Row

Post by sparks
FROM dbo.tblCustomerPurchases)
SELECT customerID ,
PurchaseDate,
PurchaseID
FROM CTE
WHERE Row = 1 ;
yes this seems to output the correct data but I am unsure how to write
this to a new table.

You stick the INSERT into the middle of it:

WITH CTE AS
(SELECT customerID, PurchaseDate, PurchaseID,
ROW_NUMBER() OVER (PARTITION BY PurchaseDate
ORDER BY customerID asc ) AS Row
FROM dbo.tblCustomerPurchases)
INSERT newtable (customerID, PurchaseDate, PurchaseID)
SELECT customerID ,
PurchaseDate,
PurchaseID
FROM CTE
WHERE Row = 1 ;

I suspect, though, that CustomerID should be in the PARTITION BY
clause, and then in the ORDER BY clause you would put whatever that
defines "first".

[Gene Wirchenko]

On Mon, 06 Aug 2012 20:51:09 +0200, Erland Sommarskog
<***@sommarskog.se> wrote:

[snip]

What am I missing that a CTE is necessary?

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
What am I missing that a CTE is necessary?

To be able to access the value from row_number. Since it is defined in
the SELECT clause, and the SELECT clause is (logically) computed after the
WHERE clause, you cannot refer to the row number in the WHERE clause.

[Gene Wirchenko]

On Mon, 06 Aug 2012 23:26:37 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by Gene Wirchenko
What am I missing that a CTE is necessary?

To be able to access the value from row_number. Since it is defined in
the SELECT clause, and the SELECT clause is (logically) computed after the
WHERE clause, you cannot refer to the row number in the WHERE clause.

Huh? All he needs is customerID and PurchaseDate. How does my
simple select not do that?

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

Post by Gene Wirchenko
On Mon, 06 Aug 2012 23:26:37 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by Gene Wirchenko
What am I missing that a CTE is necessary?

To be able to access the value from row_number. Since it is defined in
the SELECT clause, and the SELECT clause is (logically) computed after the
WHERE clause, you cannot refer to the row number in the WHERE clause.

Huh? All he needs is customerID and PurchaseDate. How does my
simple select not do that?

It's unclear what sparks wanted, but from my experience he wants more. And
if he had only wanted those two, he might not have felt compelled to ask.
:-)

[Gene Wirchenko]

On Tue, 7 Aug 2012 08:11:04 +0000 (UTC), Erland Sommarskog

Post by Erland Sommarskog

Post by Gene Wirchenko
On Mon, 06 Aug 2012 23:26:37 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by Gene Wirchenko
What am I missing that a CTE is necessary?

To be able to access the value from row_number. Since it is defined in
the SELECT clause, and the SELECT clause is (logically) computed after the
WHERE clause, you cannot refer to the row number in the WHERE clause.

Huh? All he needs is customerID and PurchaseDate. How does my
simple select not do that?

It's unclear what sparks wanted, but from my experience he wants more. And
if he had only wanted those two, he might not have felt compelled to ask.
:-)

It *is* what he said he wanted. As to compelled, he might be a
newbie, and newbies ask some rather obvious questions at times. I
have done that myself (and possibly here recently, too). I hope he
posts again with some more details.

Sincerely,

Gene Wirchenko

[sparks]

yes you are correct I snipped off the last column in the example and I am sorry about that mistake

customerID PurchaseDate
000000011 2008-09-30 00:00:00
000000011 2008-10-30 00:00:00
000000011 2008-03-20 00:00:00
000000022 2008-03-20 00:00:00
000000044 2008-09-30 00:00:00
000000044 2008-10-30 00:00:00
000000044 2008-11-30 00:00:00


This is the records they were wanting I missed cutting and pasting the last column.
For each customer they wanted the unique purchase dates like the example.
so customer 00000011 had 3 dates but customer 000000022 has 6 records but only the 1 date.

The CTE example was the first one I found on the internet and when I tried it it gave me this output.
that is the reason why I posted it.

this line
INSERT newtable (customerID, PurchaseDate, PurchaseID)
was what I was missing I thought it had to go after WHERE Row = 1 ; or it would get all records

you are correct that I am a noob I have been doing access stuff for years and I was going to link this to an access
database where it would have been easy but I decided to try and figure out how to do it in sql.
the sql things I am doing now are pretty blah, just taking data in one database and mapping it to another.
This database has 85 tables 100+ columns each with 80,000 records. Basically a lot of data checking and converting.

customerID of 00000000
order date of 1/1/0952

its been an experience

Post by Gene Wirchenko
On Tue, 7 Aug 2012 08:11:04 +0000 (UTC), Erland Sommarskog

Post by Erland Sommarskog

Post by Gene Wirchenko
On Mon, 06 Aug 2012 23:26:37 +0200, Erland Sommarskog

Post by Erland Sommarskog

Post by Gene Wirchenko
What am I missing that a CTE is necessary?

To be able to access the value from row_number. Since it is defined in
the SELECT clause, and the SELECT clause is (logically) computed after the
WHERE clause, you cannot refer to the row number in the WHERE clause.

Huh? All he needs is customerID and PurchaseDate. How does my
simple select not do that?

It's unclear what sparks wanted, but from my experience he wants more. And
if he had only wanted those two, he might not have felt compelled to ask.
:-)

It *is* what he said he wanted. As to compelled, he might be a
newbie, and newbies ask some rather obvious questions at times. I
have done that myself (and possibly here recently, too). I hope he
posts again with some more details.
Sincerely,
Gene Wirchenko

[sparks]

I can only say thank you all big time for putting up with my stupid noob questions :)

thanks again for all your help