Querying multiple transactions into a single line

Question

I am trying to create a small home finance app. I am trying a sort of Double Entry type design, but really battling to find a way to efficialy generate a staement.

So I have created a dummy script that I am using to test. What I have is:

1. Accounts table (entities that I can pay to, and from).
2. Budgets table (budgets that I can assign expenses to, so that I can allocate portions of transactions to them. Note that a transaction can be split amoungst different budgets, as an example I have provided)
3. Transaction table (Holding the header info for a transaction)
4. TransactionLine table (A breakdown of the transaction, including amount and account it comes from)

What I am then trying to do, is achieve the following:

I need to efficiently present data to render a statement. So, given an accountID, I want to see the transactions.

But because TransactionLines are split onto multiple lines, I'm finding it hard to get the data in a single line:

Date-- Who I paid or recieved money from in this transaction -- the amount -- If it's a debit or credit.

So the raw data I have to work with:

And then I am trying to break that down to:

So I have mocked up the data, and tried to explain what I need. The script uses table variables, so is re-runnable.

DECLARE @Account TABLE (
    Id INT NOT NULL,
    Name VARCHAR(20)
)
INSERT INTO @Account VALUES (1, 'My Bank Account')
INSERT INTO @Account VALUES (2, 'My Work')
INSERT INTO @Account VALUES (3, 'A restaurant')
INSERT INTO @Account VALUES (4, 'A coffee shop')
INSERT INTO @Account VALUES (5, 'A Department Store')

DECLARE @Budget TABLE (
    Id INT NOT NULL,
    Name VARCHAR(20) 
)
INSERT INTO @Budget VALUES (1, 'My Budget')
INSERT INTO @Budget VALUES (2, 'My Clothing Budget')


DECLARE @Transaction TABLE (
    Id INT NOT NULL ,
    Date DATETIME NOT NULL,
    Description VARCHAR(20)
)

DECLARE @TransactionLine TABLE (
    Id INT NOT NULL,
    TransactionId INT NOT NULL,
    AccountId INT,
    BudgetId INT NULL,
    DebitAmount DECIMAL NOT NULL,
    CreditAmount DECIMAL NOT NULL
)


-- Got paid, from My Work to My Account
INSERT INTO @Transaction VALUES (1, GETUTCDATE(), 'Got Paid')
INSERT INTO @TransactionLine VALUES (1, 1, 1, NULL, 0, 1000) -- Credit My Bank ccount
INSERT INTO @TransactionLine VALUES (2, 1, 2, NULL, 1000, 0) -- Debit My Work

-- Got a coffee, from My Account to A Coffee Shop
INSERT INTO @Transaction VALUES (2, GETUTCDATE(), 'Got a Coffee')
INSERT INTO @TransactionLine VALUES (3, 2, 1, NULL, 5, 0) -- Debit My Account
INSERT INTO @TransactionLine VALUES (4, 2, 4, NULL, 0, 5) -- Credit a Coffee shop

-- Went to dinner, from My Account to A restaurant. This comes off My Budget
INSERT INTO @Transaction VALUES (3, GETUTCDATE(), 'Went to Dinner')
INSERT INTO @TransactionLine VALUES (5, 3, 1, 1, 25, 0) -- Debit My Account
INSERT INTO @TransactionLine VALUES (6, 3, 3, NULL, 0, 25) -- Credit A restaurant


INSERT INTO @Transaction VALUES (4, GETUTCDATE(), 'Did weekly shopping')
INSERT INTO @TransactionLine VALUES (9, 4, 1, 1, 25, 0) -- Debit My Account with 25, and assign it to My Budget
INSERT INTO @TransactionLine VALUES (9, 4, 1, 2, 75, 0) -- Debit My Account with 75, and assign it to My Clothing Budget
INSERT INTO @TransactionLine VALUES (11, 4, 5, NULL, 0, 50) -- Credit tghe Department store with 100



-- View the raw data.
SELECT t.id, Date, Description, AccountId, a.Name as AccountName, DebitAmount, CreditAmount, b.Name as BudgetName
FROM @Transaction t
INNER JOIN @TransactionLine tl
ON tl.TransactionId = t.Id
INNER JOIN @Account a
ON a.id = tl.AccountId
LEFT JOIN @Budget b
ON b.id = tl.BudgetId

-- View the raw data based on a select Account ID. i.e. I'm viewing a statement for 'My Bank Account'
SELECT t.id, Date, Description, AccountId, a.Name as AccountName, DebitAmount, CreditAmount, b.Name as BudgetName
FROM @Transaction t
INNER JOIN @TransactionLine tl
ON tl.TransactionId = t.Id
INNER JOIN @Account a
ON a.id = tl.AccountId
LEFT JOIN @Budget b
ON b.id = tl.BudgetId
WHERE AccountId = 1

-- Need to get:
SELECT 1 AS Id, GETUTCDATE() AS Date, 'Got Paid' AS Description, 1 AS AccountId, 'My Bank Account' as AccountName, 'My Work' AS OtherAccountName, 'Credit' as Type, 1000 as Amount, NULL AS Budget
UNION 
SELECT 2 AS Id, GETUTCDATE() AS Date, 'Got a Coffee' AS Description, 1 AS AccountId, 'My Bank Account' as AccountName, 'A coffee shop' AS OtherAccountName, 'Debit' as Type, 5 as Amount, NULL AS Budget
UNION 
SELECT 3 AS Id, GETUTCDATE() AS Date, 'Went to Dinner' AS Description, 1 AS AccountId, 'My Bank Account' as AccountName, 'A restaurant' AS OtherAccountName, 'Debit' as Type, 25 as Amount, 'My Budget' AS Budget
UNION 
SELECT 4 AS Id, GETUTCDATE() AS Date, 'Did weekly shopping' AS Description, 1 AS AccountId, 'My Bank Account' as AccountName, 'A Department Store' AS OtherAccountName, 'Debit' as Type, 100 as Amount, '* Multiple Mudgets' AS Budget

-- So that I can create a statement fro My Bank Account.
SELECT '2019-07-28   My Work                +1000' UNION
SELECT '2019-07-28   A coffee shop          -5' UNION
SELECT '2019-07-28   A restaurant*          -25' UNION
SELECT '2019-07-28   A department Store*    -100' 

-- Where the * in the description indicates it has a Budget assigned.

The main issue I find is: given I have an AccountID, I can find the transactions related to that account, but ... how do I get the OTHER account to which the transaction had an effect.

Answer 1

Firstly, well structured question and thank you for the data. Secondly, you're after window functions to solve your problem. Take special note of the ROWS BETWEEN section.

Please note that you'll usually see people declare a CTE like this (which you shouldn't do):

;with cteFooBar AS

This is because a CTE must come after a semi-colon, so make sure you put a ; on the end of all your statements so the below works:

With transactions AS
(
SELECT t.id, Date, Description, AccountId, a.Name as AccountName, DebitAmount, CreditAmount,
IIF(
COUNT(1) OVER (PARTITION BY Description ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) > 1, 'Multiple Budgets', b.Name) as BudgetName
FROM @Transaction t
INNER JOIN @TransactionLine tl ON tl.TransactionId = t.Id
INNER JOIN @Account a ON a.id = tl.AccountId
LEFT JOIN @Budget b ON b.id = tl.BudgetId
WHERE a.Name = 'My Bank Account'
AND AccountId = 1 
)


SELECT id, Date, Description, AccountId, AccountName, SUM(DebitAmount) [DebitAmount], SUM(CreditAmount) [CreditAmount], BudgetName
FROM transactions
GROUP BY id, Date, Description, AccountId, AccountName, BudgetName;


DECLARE @AccountName VARCHAR(50) = 'A Department Store';

With myAccount AS
(
SELECT t.id, Date, Description, AccountId, a.Name as AccountName, DebitAmount, CreditAmount
FROM @Transaction t
INNER JOIN @TransactionLine tl ON tl.TransactionId = t.Id
INNER JOIN @Account a ON a.id = tl.AccountId
LEFT JOIN @Budget b ON b.id = tl.BudgetId
)


SELECT CAST(Date AS DATE) [Date], AccountName,  IIF(SUM(-DebitAmount + CreditAmount) > 0, '-','+') + CAST(SUM(DebitAmount + CreditAmount) AS VARCHAR(1000)) [Amount]
FROM myAccount
WHERE (AccountName = @AccountName OR
(
@AccountName IS NULL AND AccountName != 'My Bank Account'
))

GROUP BY CAST(Date AS DATE), Description, AccountName
ORDER BY AccountName;