March 17, 2008 at 6:59 pm
Hello friends , I have table (MoneyTrans) with following structure
[Id] [bigint] NOT NULL,
[TransDate] [smalldatetime] NOT NULL,
[TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw'
[Amount] [money] NOT NULL
I need to write a query to generate following output
Trans Date, total deposits, total withdrawls, closing balance
i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate )
I am working on this for past two days with out getting a right solution. Any help is appreciated
Sara
March 17, 2008 at 7:19 pm
Can you post some sample data and the expected results?
March 17, 2008 at 8:36 pm
The following should produce what you are after
CREATE TABLE MoneyTrans
([Id] [bigint] NOT NULL,
[TransDate] [smalldatetime] NOT NULL,
[TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw'
[Amount] [money] NOT NULL
)
insert into moneytrans values(1, '1 jan 2006', 'Deposit', 10)
insert into moneytrans values(1, '2 jan 2006', 'Deposit', 11)
insert into moneytrans values(1, '3 jan 2006', 'Deposit', 12)
insert into moneytrans values(1, '4 jan 2006', 'Deposit', 13)
insert into moneytrans values(1, '5 jan 2006', 'Deposit', 14)
insert into moneytrans values(1, '1 jan 2006', 'Withdraw', 1)
insert into moneytrans values(1, '2 jan 2006', 'Withdraw', 11)
insert into moneytrans values(1, '4 jan 2006', 'Withdraw', 9)
insert into moneytrans values(1, '5 jan 2006', 'Withdraw', 7)
insert into moneytrans values(1, '6 jan 2006', 'Withdraw', 7)
SELECT TransDate
, SUM ([Total deposits]) As 'Total deposits'
, SUM ([Total withdrawals]) As 'Total withdrawals'
, SUM (ClosingBalance)AS ClosingBalance
FROM (
SELECT TransDate
, ISNULL (SUM (CASE WHEN TransName = 'Deposit' Then Amount Else NULL END), 0) AS 'Total deposits'
, ISNULL (SUM (CASE WHEN TransName = 'Withdraw' Then Amount Else NULL END), 0) AS 'Total withdrawals'
, 0 AS ClosingBalance
FROM MoneyTrans
GROUP BY Transdate
UNION ALL
SELECT TransDate
, 0 AS 'Total deposits'
, 0 AS 'Total withdrawals'
, ISNULL ((SELECT SUM (CASE WHEN MT2.TransName = 'Deposit' Then MT2.Amount Else NULL END) FROM MoneyTRans MT2 WHERE MT2.TransDate <= MT.TransDate), 0)
- ISNULL ((SELECT SUM (CASE WHEN MT2.TransName = 'Withdraw' Then MT2.Amount Else NULL END) FROM MoneyTRans MT2 WHERE MT2.TransDate <= MT.TransDate), 0)
FROM MoneyTrans MT
GROUP BY Transdate
) AS X
GROUP BY TRANSDATE
The output from the above is as follows:
TransDate Total deposits Total withdrawals ClosingBalance
----------------------- --------------------- --------------------- ---------------------
2006-01-01 00:00:00 10.00 1.00 9.00
2006-01-02 00:00:00 11.00 11.00 9.00
2006-01-03 00:00:00 12.00 0.00 21.00
2006-01-04 00:00:00 13.00 9.00 25.00
2006-01-05 00:00:00 14.00 7.00 32.00
2006-01-06 00:00:00 0.00 7.00 25.00
March 17, 2008 at 8:53 pm
You cannot calculate a Closing_Balance without an Opening_Balance, only a Net_total.
Try this:
Select TransDate,
Sum( Case TransName When 'Deposit' Then amount Else 0.0 End ) as TotalDeposits,
Sum( Case TransName When 'Withdraw' Then amount Else 0.0 End ) as TotalWithdrawls,
( Sum( Case TransName When 'Deposit' Then amount Else 0.0 End )
+ Sum( Case TransName When 'Withdraw' Then amount Else 0.0 End ) ) as NetTotal
From MoneyTrans
Group by TransDate
Order by TransDate
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 17, 2008 at 8:59 pm
Well, if the transaction table has all of the transactions you are able to calculate the closing balance for any date. If, for some reason the transaction table is not complete, I agree that you can only calculate a net figure.
The original post asked for the closing balance - RBarryYoung's solution doesn't quite do this. That solution produces the net change in the balance for a single day.
March 17, 2008 at 9:22 pm
Happycat59,
Just curious why you chose to do a subquery with a union all when you have the same group by in both queries. You could have done something like this.
CREATE TABLE #MoneyTrans
([Id] [bigint] NOT NULL,
[TransDate] [smalldatetime] NOT NULL,
[TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw'
[Amount] [money] NOT NULL
)
insert into #moneytrans values(1, '1 jan 2006', 'Deposit', 10)
insert into #moneytrans values(1, '2 jan 2006', 'Deposit', 11)
insert into #moneytrans values(1, '3 jan 2006', 'Deposit', 12)
insert into #moneytrans values(1, '4 jan 2006', 'Deposit', 13)
insert into #moneytrans values(1, '5 jan 2006', 'Deposit', 14)
insert into #moneytrans values(1, '1 jan 2006', 'Withdraw', 1)
insert into #moneytrans values(1, '2 jan 2006', 'Withdraw', 11)
insert into #moneytrans values(1, '4 jan 2006', 'Withdraw', 9)
insert into #moneytrans values(1, '5 jan 2006', 'Withdraw', 7)
insert into #moneytrans values(1, '6 jan 2006', 'Withdraw', 7)
SELECT a.transdate,
a.Deposit,
a.Withdraw,
a.ClosingBal
FROM(
SELECT transdate,
SUM(case when MT.transname = 'Deposit' then
MT.Amount
else 0
end) as [Deposit],
SUM(case when MT.transname = 'Withdraw' then
MT.Amount
else 0
end) as [Withdraw],
ISNULL(
(SELECT SUM(CASE WHEN MT2.TransName = 'Deposit' Then
MT2.Amount Else NULL END)
FROM #MoneyTRans MT2
WHERE MT2.TransDate <= MT.TransDate)
, 0)
- ISNULL(
(SELECT SUM(CASE WHEN MT2.TransName = 'Withdraw' Then
MT2.Amount Else NULL END)
FROM #MoneyTRans MT2
WHERE MT2.TransDate <= MT.TransDate)
, 0) AS [ClosingBal]
FROM #MoneyTrans MT
GROUP BY transdate
) AS a
ORDER BY a.transdate
drop table #MoneyTrans
March 17, 2008 at 9:41 pm
No real reason other than separating the logic so that it was obvious - yep, they could be in one select statement
March 17, 2008 at 9:47 pm
No real reason other than separating the logic so that it was obvious - yep, they could be in one select statement
Cool. I was just curious. You logic works nice and should meet the OP needs. 🙂
March 17, 2008 at 9:54 pm
Ah well, if you're sure you've got all of the transaction history, then I guess I could add a few lines to give you Closing_Balance:
With DailyTrans( TransDate, TotalDeposits, TotalWithdrawls ) AS
(
Select TransDate,
Sum( Case TransName When 'Deposit' Then amount Else 0.0 End ),
Sum( Case TransName When 'Withdraw' Then amount Else 0.0 End )
From MoneyTrans
Group by TransDate
)
Select TransDate,
TotalDeposits,
TotalWithdrawls,
(Select Sum(TotalDeposits)+Sum(TotalWithdrawls)
From DailyTrans C Where C.TransDate <= D.TransDate) as ClosingBalance
From DailyTrans D
Order by TransDate
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 17, 2008 at 11:02 pm
Wow! quick replies! Heart felt thanks friends
I have arrived at the query i needed
SELECT TransDate,
SUM(CASE WHEN TransName='Deposit' THEN Amount ELSE 0 END) as TotalDeposits,
SUM(CASE WHEN TransName='Withdraw' THEN Amount ELSE 0 END) as TotalWithdrawals,
(SELECT SUM(CASE WHEN TransName='Deposit' THEN Amount ELSE 0 END)-SUM(CASE WHEN TransName='Withdraw' THEN Amount ELSE 0 END) FROM MoneyTrans m2 WHERE m2.TransDate<=m1.TransDate) as ClosingBalance
FROM MoneyTrans m1
GROUP BY TransDate
once again Thank you all very much
Sara
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply