Another enjoyable problem in SQL which reared its head. We had a transactions table with transactions linked to customers. Normally in SQL you operate on all, there's no concept of an ordered list which you iterate through. In this particular case we had to retrieve the previous transactions for customers where the last transaction was a cancellation on the same day.


SELECT cancelList.customerID, Max(cancelList.customerID) as [customerID]
INTO ##prevTransaction
FROM ##transactionsView cancelList
JOIN Transaction
ON cancelList.customerID = Transaction.customerID AND cancelList.transactionDate = Transaction.transactionDate
WHERE cancelList.transactionType = 'Cancel'
GROUP BY cancelList.customerID
HAVING COUNT(Transaction.customerID) > 1

DELETE FROM ##transactionsView
WHERE ##transactionsView.customerID IN (SELECT customerID FROM ##prevTransaction)

INSERT INTO ##transactionsView
SELECT TLast.[customerID], transactionID, Trans.transactionType, Trans.transactionDate
FROM Transaction Trans
JOIN (SELECT ##prevTransaction.customerID, Max(transactionID) as [customerID]
FROM (SELECT Transaction.customerID, Transaction.customerID FROM Transaction WHERE customerID NOT IN
(SELECT customerID FROM ##prevTransaction)
) innerList
JOIN ##prevTransaction on innerList.customerID = ##prevTransaction.customerID
GROUP BY ##prevTransaction.customerID) TLast
ON Trans.customerID = TLast.customerID
WHERE TLast.[customerID] NOT IN (SELECT customerID FROM ##transactionsView)


You can see from the above the three stages and the two temporary tables.

In the first step we already have a temporary table ##transactionsView which contains the transactions we're going to present in our report. From that list we get all the transactions which are cancelled and have another transaction on the same day. We add that list to temp table ##prevTransaction.

In the second step we clear ##transactionsView of the transactions we took in the previous step. This makes room for the previous transactions in the final report.

The third step is the meat of our operation. Using ##prevTransaction in the where clause we search for the highest transactionID for a customer which is NOT in ##prevTransaction. The only thing to be cautious on is the innermost SELECT WHERE NOT IN will return every row which is not in ##prevTransaction so use with caution. The outermost join by the way adds a bit of security to ensure we don't select lines which are already in ##transactionsView. This makes the previous step's importance clear. It makes room for the previous transactions.

The output of this query will accurately display previous transactions while ensuring only one transaction is displayed per customerID.

No comments

Add Comment

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
BBCode format allowed