pdm2011 pdm2011 - 3 months ago 7
SQL Question

SQL return multiple non-null values with highest index

I have the following tables in a SQL Server 2008 R2 database:

Customers:

CustID CustName
====== ========
1 A
2 B
3 C
4 D


Transactions:

TransID CustID InvoiceTotal LoyaltyPointsEarned
======= ====== ============ ===================
1 1 300 25
2 2 NULL 10
3 3 100 10
4 2 200 25
5 1 NULL 100
6 3 120 NULL


Transactions are inserted in chronological order (higher ID = more recent order); a transaction allows either InvoiceTotal or LoyaltyPointsEarned to be NULL, but not both.

I want to get the most recent non-null invoice total AND (this is the tricky bit) most recent non-null loyalty points earned for all customers, with this information displayed on the same row for each customer:

CustID CustName LatestInvoiceTotal LatestLoyaltyPointsEarned
1 A 300 100
2 B 200 25
3 C 120 10


The following query gives the latest invoice total:

SELECT DISTINCT
CustID, CustName, LatestInvoiceTotal, LatestLoyaltyPointsEarned
FROM
Customers
INNER JOIN
(SELECT
CustID, InvoiceTotal AS LatestInvoiceTotal, TransID
FROM
Transactions
GROUP BY
CustID, InvoiceTotal, TransID) CustomerTransactions ON Customers.CustID = CustomerTransactions.CustID
INNER JOIN
(SELECT
CustID, MAX(TransID) AS MaxTransID
FROM
Transactions
WHERE
InvoiceTotal IS NOT NULL
GROUP BY
CustID) MaxTransactionIDs ON Customers.CustID = MaxTransactionIDs.CustID AND CustomerTransactions.TransID = MaxTransactionIDs.MaxTransID


How can this be extended to do the same for LoyaltyPointsEarned, without duplicating customer records in the results?

Answer

The easy solution is having two subqueries retrieving that information.

select CustID, CustName, 
       (select top 1 InvoiceTotal
        from Transactions
        where Transactions.CustID = Customers.CustID and InvoiceTotal is not null
        order by TransID desc) as LatestInvoiceTotal,
       (select top 1 LoyaltyPointsEarned
        from Transactions
        where Transactions.CustID = Customers.CustID and LoyaltyPointsEarnedis not null
        order by TransID desc) as LatestLoyaltyPointsEarned               
from Customers

But as subqueries could importantly degrade your performance, you just have to be sure to have a multiple index on Transactions over CustID, TransID descending, so those subqueries would be optimized.