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 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply