lundi 25 avril 2016

Statement of account from transactions SQL

I have a list of transactions in a table for a user which records, the UserID, DateTime, TransactionType and Value. TransactionID is the Primary Key auto increment 1. The TransactionType defines a Deposit (1) or Withdrawal (2) so all values are positive in the table. I am trying to create a statement of account with a running total.

TransactionID    UserID    DateTime           TransactionTypeID    Value
     1            3112     01-04-2016 12:00          1              5.00
     3            3112     01-04-2016 13:00          2              2.00
     5            3112     01-04-2016 13:25          2              1.00
     8            3112     02-04-2016 12:00          1              10.00
     9            3112     02-04-2016 12:35          2              4.00

Basically I want to create a running statement of account query with a Total value to create:

DateTime           TransactionTypeID    Deposit    Withdrawal    Balance
01-04-2016 12:00          1              5.00                     5.00
01-04-2016 13:00          2                          2.00         3.00
01-04-2016 13:25          2                          1.00         2.00
02-04-2016 12:00          1              10.00                   12.00
02-04-2016 12:35          2                          4.00         8.00

I have tried using OUTER APPLY to select the previous transaction but with no prevail in a single query. Any assistance would be appreciated

SELECT 
    [UserID], [DateTime], 
    T.[Value] * 
    (CASE 
        WHEN [TransactionTypeID] IN (1, -- deposit
                                     2 -- withdrawal
                                    )
            THEN -1
            ELSE 1
     END),
    T2.Value AS PrevValue
FROM 
    [Transaction] T
OUTER APPLY 
    (SELECT TOP 1 T2.[Value]
     FROM [Transaction] T2
     WHERE UserID = 3112 
       AND T2.[TransactionID] > T.TransactionID
    ORDER BY T2.TransactionID) AS T2
WHERE 
    T.[UserID] = 3112
ORDER BY 
    T.[TransactionID] DESC

Aucun commentaire:

Enregistrer un commentaire