Say I want to display the first, last transactions of our users, and display these amounts. This can be done by
SELECT user, min(Date) firstDate, max(Date) lastDate
INTO #MinAndMax
FROM transactionTable
GROUP BY user
Then I do another inner select statement, to get the amount based on the value obtained above when doing the min(Date), so like the following;
SELECT (SELECT amount
FROM transactionTable
WHERE date = firstDate) AS firstAmount
FROM #MinAndMax
However is there a more efficient manner to do this, like pivoting?
If might be faster to use analytic functions to rank your transactions by user, and select the first for each:
WITH CTE AS
( SELECT [User],
FirstDate = Date,
FirstAmount = Amount,
RowNum = ROW_NUMBER() OVER(PARTITION BY [User] ORDER BY Date),
LastDate = MAX(Date) OVER(PARTITION BY [User])
FROM TransactionTable
)
SELECT [User], FirstDate, FirstAmount, LastDate
FROM CTE
WHERE RowNum = 1;
If, as in your query you only need the amount, your query could simply be. If you have a user table, then it might be faster still to use APPLY along with TOP:
SELECT u.[User], FirstAmount = t.Amount
FROM Users u
CROSS APPLY
( SELECT TOP 1 Amount
FROM TransactionTable t
WHERE t.[User] = u.[User]
ORDER BY t.Date
) t;
EDIT
To get the first and last amounts you would need a second ROW_NUMBER function:
WITH CTE AS
( SELECT [User],
Date,
Amount,
RowNumAsc = ROW_NUMBER() OVER(PARTITION BY [User] ORDER BY Date),
RowNumDesc = ROW_NUMBER() OVER(PARTITION BY [User] ORDER BY Date DESC)
FROM TransactionTable
)
SELECT [User],
FirstDate = MIN(CASE WHEN RowNumAsc = 1 THEN Date END),
FirstAmount = MIN(CASE WHEN RowNumAsc = 1 THEN Amount END),
LastDate = MIN(CASE WHEN RowNumDesc = 1 THEN Date END),
LastAmount = MIN(CASE WHEN RowNumDesc = 1 THEN Amount END)
FROM CTE
WHERE 1 IN (RowNumAsc, RowNumDesc)
GROUP BY [User];
Or
SELECT u.[User],
FirstDate = f.Date,
FirstAmount = f.Amount,
LastDate = l.Date,
LastAmount = l.Amount
FROM Users u
CROSS APPLY
( SELECT TOP 1 Amount, Date
FROM TransactionTable t
WHERE t.[User] = u.[User]
ORDER BY t.Date
) AS f
CROSS APPLY
( SELECT TOP 1 Amount, Date
FROM TransactionTable t
WHERE t.[User] = u.[User]
ORDER BY t.Date DESC
) AS l;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments