June 14, 2013 at 4:49 am
Hey guys,
I am querying a database to find out the dates of customers 1st, 2nd, 3rd, 4th purchases.
I am so far querying the first transaction (using MIN) and the most recent transaction (using MAX).
My question is: how can I query the database further to get the 2nd, 3rd and 4th purchases etc?
My query is this:
SELECT mem.MemberID, FirstName + ' ' + LastName AS 'Customer', MIN(TransactionDate) AS 'First Transaction', MAX(TransactionDate) AS 'Most Recent Transaction', COUNT(*) AS 'Total Number Of Purchases'
FROM Transaction t
INNER JOIN Members m ON t.MemberId = m.MemberId
GROUP BY mem.MemberId, FirstName + ' ' + LastName
HAVING NOT FirstName + ' ' + LastName = '' --where members name has not been provided
AND COUNT(*) > 1
AND MIN(TransactionDate) > '2011-01-01'
ORDER BY COUNT(*) DESC
All advice will be greatly appreciated.
Thanks,
Dan
June 14, 2013 at 4:53 am
You will have to use ROW_NUMBER() to do this easily
If you could provide DDL of the tables involved, some sample data and the expected result, we can come up with tested solutions.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 14, 2013 at 5:03 am
Thank you for replying.
My expected output is something like the following:
MemberID Customer First Transaction 2nd Transaction 3rd Transaction 4th Transaction Total Number of Purchases
1| Cust1 | 2013-03-06 20:23:03.257 | 2013-04-01 00:04:00 | 2013-04-07 01:04:02 | 2013-06-03 01:15:35 | 309
2| Cust2 | 2013-03-13 19:35:32.077 | 2013-03-21 09:04:09 | 2013-04-06 12:45:09 | 2013-06-03 22:46:47 | 377
3| Cust3 | 2013-02-12 23:06:42.823 | 2013-05-02 09:45:12 | 2013-05-12 15:34:04 | 2013-06-03 21:17:31 | 227
I hope this helps.
Dan
June 14, 2013 at 5:24 am
Hope this helps..
DECLARE @tbl_transaction TABLE
(
MemberIDINT,
CustomerVARCHAR(100),
TransactionDate DATETIME
)
INSERT@tbl_transaction
SELECT1, 'Cust1', '2013-01-01 00:00:00.000' UNION ALL
SELECT1, 'Cust1', '2013-03-01 00:00:00.000' UNION ALL
SELECT1, 'Cust1', '2013-02-01 00:00:00.000' UNION ALL
SELECT1, 'Cust1', '2013-05-01 00:00:00.000' UNION ALL
SELECT1, 'Cust1', '2013-06-01 00:00:00.000' UNION ALL
SELECT2, 'Cust2', '2013-01-01 00:00:00.000' UNION ALL
SELECT2, 'Cust2', '2013-02-01 00:00:00.000' UNION ALL
SELECT2, 'Cust2', '2013-04-01 00:00:00.000' UNION ALL
SELECT2, 'Cust2', '2013-05-01 00:00:00.000' UNION ALL
SELECT2, 'Cust2', '2013-06-01 00:00:00.000' UNION ALL
SELECT2, 'Cust2', '2013-07-01 00:00:00.000'
SELECTT.MemberID, T.Customer,
MAX(CASE WHEN T.RN = 1 THEN T.TransactionDate ELSE NULL END) AS [Trn1],
MAX(CASE WHEN T.RN = 2 THEN T.TransactionDate ELSE NULL END) AS [Trn2],
MAX(CASE WHEN T.RN = 3 THEN T.TransactionDate ELSE NULL END) AS [Trn3],
MAX(CASE WHEN T.RN = 4 THEN T.TransactionDate ELSE NULL END) AS [Trn4],
COUNT(*) AS Purchases
FROM(
SELECTROW_NUMBER() OVER ( PARTITION BY MemberID, Customer ORDER BY TransactionDate ) AS RN, *
FROM@tbl_transaction
) AS T
GROUP BY T.MemberID, T.Customer
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 14, 2013 at 5:27 am
here's my best guess, based on your original query, using row_number() like kingston suggested:
SELECT
ROW_NUMBER() OVER (PARTITION BY mem.MemberID ORDER BY t.TransactionDate) AS RW,
mem.MemberID,
mem.FirstName + ' ' + mem.LastName AS 'Customer',
t.TransactionDate
FROM [TRANSACTION] t
INNER JOIN Members mem
ON t.MemberId = mem.MemberId
ORDER BY mem.MemberID,t.TransactionDate
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply