SQL Help Please 1

[nmair]

Hello there,

I have the following small table (CardTransactions), given below which is sorted by card and then by date:

CardNum ActivityDate
---------- -------------
1 2011/12/17
1 2011/12/18
1 2011/12/20
1 2011/12/22
2 2011/12/13
2 2011/12/16
2 2011/12/19
2 2011/12/22
3 2011/12/25
3 2011/12/30
3 2011/12/31
3 2011/12/31

What I'm wondering is if there is a SQL query that will do a DATEDIFF(for days) for only the 2 smallest dates on each card. The result of the query should show like this:

CardNum DateDiff
------- --------
1 1
2 3
3 5

Is it possible, please help.

Thanks

Neill

 

[PHV]

What about this ?

SELECT A.CardNum, Min(B.ActivityDate)-A.FirstDate AS DateDiff
FROM (SELECT CardNum, Min(ActivityDate) AS FirstDate FROM CardTransactions GROUP BY CardNum) A
INNER JOIN CardTransactions B ON A.CardNum = B.CardNum
WHERE B.ActivityDate > A.FirstDate
GROUP BY A.CardNum, A.FirstDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nmair]

That is fantastic! Works great!

As I'm new to SQL I have just one question: How can the GROUPBY clase be grouped by 2 columns(A.CardNum, A.FirstDate)? I thought limitations to the GROUPBY clause was that you must group by the same amount of columns (NOT column functions) that are specified in the SELECT statement.

Thank you very much sir/ma'am.

Regards,

Neill