Pfthb

I have a table 'transactions(txDate, amount, txType) where txType can "credit" or "debit".
I need to get an opening and closing balance when returning transactions between two dates.

The first row of the results should be the Opening Balance, then a list of all tx between the dates and the last row to be a Closing balance

Getting the list isn't a train smash but for the balances, I currently have the following

SELECT SUM(amount) AS [Opening Balance]
FROM 
(
 SELECT SUM([Amount]) amount
 FROM [dbo].[Transaction]
 WHERE [txDate] <= @startDate
 AND [txType] = 'credit'

 UNION ALL

 SELECT 0 - SUM([Amount]) amount
 FROM [dbo].[Transaction]
 WHERE [TransactionDate] <= @startDate
 AND [txType] = 'debit'
) Transactions

this gives a very big amount than what it should be.

And for the Closing balance, I have no idea how to go about it

You could use CASE in SUM

select sum(case when txType = 'credit' and transactionDate <= @startDate 
 then amount end) - 
 sum(case when txType = 'debit' and transactionDate <= @startDate 
 then amount end)[Opening Balance],
 sum(case when txType = 'credit' and transactionDate <= @endDate 
 then amount end) - 
 sum(case when txType = 'debit' and transactionDate <= @endDate 
 then amount end)[Closing Balance]
from transaction

I think you could use SUM() OVER () window function to calculate running sum over dates:

WITH txn_data AS ( 
 SELECT TransactionDate, txType, Amount
 FROM dbo.Transaction
 UNION ALL
 SELECT @startDate, 'CF', 0
 UNION ALL
 SELECT @endDate, 'BAL', 0
), txn_sums AS ( 
 SELECT
 TransactionDate, txType, Amount,
 SUM(IIF(txType = 'debit', -1, 1) * Amount) OVER (ORDER BY TransactionDate, CASE txType WHEN 'CF' THEN -1 WHEN 'BAL' THEN 1 ELSE 0 END) AS Balance
 FROM txn_data
)
SELECT *
FROM txn_sums
WHERE TransactionDate >= @startDate AND TransactionDate < DATEADD(DAY, 1, @endDate)

Note that each row will contain the sum of all previous transactions plus the current transaction.