SQL return transaction table with the date of previous payment

I have a query in Access that returns transactions for all开发者_如何学编程 customers the fields are

TransID, TenantID, TransactionType, Amount, TransactionDate

Where TransactionType =1 for Charges and 2 is for payments

I would like to return all the records of this query with an extra field containing the previous payment's (TransactionType =2 ) TransactionDate for this clinet (TenantID) In case there is no previous payment I would like to just return the record with null as previous transaction date.

Can you help me please?

Using a sub-query, pass in the outer query's transaction date:

SELECT TransID, TenantID, TransactionType, Amount, TransactionDate,
       (SELECT MAX(I.TransactionDate)
        FROM unnamed_table I
        WHERE I.TransactionDate < O.TransactionDate
          AND I.TransactionType = 2
          AND I.TenantID = O.TenantID) PrevTransDate
FROM unnamed_table O

select t1.TransID, t1.TenantID, t1.TransactionType, t1.Amount, t1.TransactionDate,  max(t2.TransactionDate)
from table as t1 left outer join table as t2 on (t1.TenantID=t2.TenantID)
Where t1.TransactionType =1 and t2.TransactionType =2
and t1.TransactionDate > t2.TransactionDate

you'll need a sub query or you'll need to do some magic with MAX and string functions to do better than this in one query or you'll need an id that identifies the previous transaction uniquely.

select t.*, MAX(prevt.transid) as prev_trans_id
from transactions t 
left join transactions prevt
on t.tenantid=prevt.tenantid 
and t.transid > prevt.transid
group by t.transid

Another method:

SELECT
    T1.TransID,
    T1.TenantID,
    T1.TransactionType,
    T1.Amount,
    T1.TransactionDate,
    T2.TransactionDate AS PreviousPaymentDate
FROM
    Transactions T1
LEFT OUTER JOIN Transactions T2 ON
    T2.TenantID = T1.TenantID AND
    T2.TransactionType = 2 AND
    T2.TransactionDate < T1.TransactionDate
LEFT OUTER JOIN Transactions T3 ON
    T3.TenantID = T1.TenantID AND
    T3.TransactionType = 2 AND
    T3.TransactionDate < T1.TransactionDate AND
    T3.TransactionDate > T2.TransactionDate
WHERE
    T3.TransID IS NULL AND
    <your normal selection criteria>

You're basically getting the last transaction (T2) that's of type 2 for the same tenant where no other transaction for that tenant and of the same type 2 appears after that one (that's what the T3.TransID IS NULL does - the only way that it will be NULL is if no match was found). You can also do this a little more clearly with a NOT EXISTS, but performance is often better with the double LEFT OUTER JOIN. Here's a NOT EXISTS version:

SELECT
    T1.TransID,
    T1.TenantID,
    T1.TransactionType,
    T1.Amount,
    T1.TransactionDate,
    T2.TransactionDate AS PreviousPaymentDate
FROM
    Transactions T1
LEFT OUTER JOIN Transactions T2 ON
    T2.TenantID = T1.TenantID AND
    T2.TransactionType = 2 AND
    T2.TransactionDate < T1.TransactionDate
WHERE
    NOT EXISTS (
        SELECT *
        FROM Transactions T3
        WHERE
            T3.TenantID = T2.TenantID AND
            T3.TransactionType = 2 AND
            T3.TransactionDate < T1.TransactionDate AND
            T3.TransactionDate > T1.TransactionDate
        ) AND
    <your normal selection criteria>