SQL Script Question

[VisualGuy]

Say you had a table with the following fields

Logo Acct Num Tran Date Amount
123 1111111111 11/1/2009 1.50
123 1111111111 11/2/2010 1.75
234 2222222222 11/2/2010 1.50
234 2222222222 11/4/2010 1.75
345 3333333333 11/4/2010 1.99

I'm only interested in first purchases, that occured in November 2010. And if you've already made a purchase before, I don't want you on my list at all. So this would make for the following output.

Logo Acct Num Tran Date Amount
234 2222222222 11/2/2010 1.50
345 3333333333 11/4/2010 1.99

Is there a way this could be done in one call?

 

[dhulbert]

Select *
from yourtable yt
inner join
(select acctnum, max(trandate) as tdate
from yourtable
group by acctnum) s
on
yt.acctnum = s.acctnum
and
yt.trandate = s.tdate

Only issue will come with customers who may have purchased twice on the same day.




I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[SimonSellick]

And to exclude those who have purchased earlier:

...
and logo not in (
  select logo 
  from yourtable 
  where trandate < '01-Nov-2010'
)

 

[dhulbert]

Didn't read the bit about the first purchase in November properly

Select *
from yourtable yt
inner join
(select acctnum, MIN(trandate) as tdate
from yourtable
WHERE TRANDATE >= '01-Nov-2010'
group by acctnum) s
on
yt.acctnum = s.acctnum
and
yt.trandate = s.tdate

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[NiceArms]

sql 2005 onwards

declare @tablea table (logo int, acct_num int, Tran_date datetime, Amount money)

insert @tablea (logo, acct_num, Tran_date, Amount) values(123,111111111,'11/01/2009',1.50)
insert @tablea (logo, acct_num, Tran_date, Amount) values(123,111111111,'11/02/2010',1.75)
insert @tablea (logo, acct_num, Tran_date, Amount) values(234,222222222,'11/02/2010',1.50)
insert @tablea (logo, acct_num, Tran_date, Amount) values(234,222222222,'11/04/2010',1.75)
insert @tablea (logo, acct_num, Tran_date, Amount) values(345,333333333,'11/04/2010',1.99)

--This is the bit you will want.
SELECT logo, acct_num, Tran_date, Amount
FROM(
	SELECT logo, acct_num, Tran_date, Amount
	, ROW_NUMBER() OVER (PARTITION BY logo ORDER BY logo, Tran_date ASC) AS 'RN'
	FROM @TABLEA
	WHERE tran_date >= '11/01/2010'
	AND tran_date <= '11/30/2010')A
WHERE A.RN = 1

I feel sorry for people who don't drink, when they wake up in the morning that's as good as the're going to feel all day!