July 18, 2003 at 7:13 am
Hi all,
I would like to know how to write a query which would fetch me a cumulative total. An illustration of my requirement is given below for better understanding.
This is the table structure and I need a query to update 'YTDDebit' cumulatively.
AccountMonthPTDDebitYTDDebit
---------------------------------------
1000101100
1000102150
1000103200
1000201200
1000202300
1000203350
After I run the query, the resulting table should be like the one given below
AccountMonthPTDDebitYTDDebit
---------------------------------------
1000101100100
1000102150250
1000103200450
1000201200200
1000202300500
1000203350850
Can someone help me with an UPDATE statement to get this done?
July 18, 2003 at 11:00 am
create table test(Account int, fMonth int, PTDDebit int, YTDDebit int)
insert test select 10001 ,01, 100,null
union all
select
10001 ,02, 150,null
union all
select
10001, 03 ,200,null
union all
select
10002, 01, 200,null
union all
select
10002,02 ,300,null
union all
select
10002, 03, 350 ,null
go
create view view1 as
SELECT a.fmonth, a.account, a.PTDDebit,
SUM(b.PTDDebit)AS RunningTotal
FROM test a
CROSS JOIN test b
WHERE (a.account=b.account and b.fmonth <= a.fmonth)
GROUP BY a.account,a.fmonth,a.PTDDebit
go
update a set a.YTDDebit=b.RunningTotal
FROM test a
join view1 b on a.account= b.account and a.fmonth=b.fmonth
go
drop view view1
go
select*from test
go
drop table test
go
July 21, 2003 at 7:44 am
Thank you, Your suggestion helped me out of the problem. The only modification I made to your query was to use an INNER JOIN instead of a CROSS JOIN. A CROSS JOIN with a WHERE clause works much similar to an INNER JOIN
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply