Selecting the previous row based on different types in SQL Server

Question

Suppose I have the following table:

and i want to achieve the following:

I using Microsoft SQL Server to find the for all withdrawals and then find the first deposit before that withdraw when it was made.

I have tried using ROW_NUMBER with PARTITION BY however I am not managing because I am getting first deposit from the table and not the first one before that specific withdrawal. Anyone has suggestions?

question from:https://stackoverflow.com/questions/66047086/selecting-the-previous-row-based-on-different-types-in-sql-server

Answer 1

One easy way to do this is using OUTER APPLY:

SELECT t1.CustomerID
   , t1.TransType
   , t2.[DateTime] AS DepositDate
   , t1.[DateTime] AS WithdrawalDate
FROM t t1
OUTER APPLY 
(
   SELECT TOP 1 [DateTime]
   FROM t t2
   WHERE 
       t1.CustomerID = t2.CustomerID 
       AND 
       t2.[DateTime] < t1.[DateTime] 
       AND 
       TransType = 'Deposit'
   ORDER BY [DateTime] DESC
)
WHERE t1.TransType = 'Withdrawal'