Best tool for the job - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Best tool for the job

Author

Topic

SimonG
Starting Member

15 Posts

Posted - 2010-02-15 : 07:18:17

Hi all,

Looking for some guidance on the best way to achieve the following as I can't seem to do this without some programming in SQL2000 (and I am a rookie!);

I have a DTS package that exports a set of data, and consolidates individual transactions into months of activity by contract. I cannot determine up front how many months/years of data this will produce nor how many contracts will be involved. Neither can I guarantee that there will be activity each concurrent month!;

I end up with a consolidated dataset like this after my initial DTS;
Contract,Year,Month,Total_Cost
CW0391, 2008, 5, 100.00
CW0392, 2008, 5, 250.00
CW0391, 2008, 6, 100.00
CW0392, 2008, 6, 250.00
etc..

My goal is to end up with a table of data that contains the above and a new cumulative cost column;
Contact,Year,Month,Total_Cost,Cumulative_Cost
CW0391, 2008, 5, 100.00, 100.00
CW0392, 2008, 5, 250.00, 250.00
CW0391, 2008, 6, 100.00, 200.00
CW0392, 2008, 6, 250.00, 500.00
etc..

I'm pulling my hair out trying to work out the best way of achieving this - I'm not looking for the solution just some guidance on the best way to tackle the problem.

Regards,

Simon

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-15 : 07:34:22

Most probably you will have to write a correlated subquery in this case which will sum up total_cost column based on Year, month and contract. Something like this:


select 
Contract,Year,Month,Total_Cost,
(Select sum(total_cost) from tbl t2 
where t2.contract = t1.contract
and t2.year <= t1.year and t2.month <= t1.month) as cumulative_cost
from tbl t1

Harsh Athalye
http://www.letsgeek.net/

SimonG
Starting Member

15 Posts

Posted - 2010-02-16 : 07:51:31

Thanks for this post.

It unfortunately breaks down when the new year starts;

CW0391 2008 5 28262.0400 28262.0400
CW0391 2008 6 46713.9600 74976.0000
CW0391 2008 7 282300.5300 357276.5300
CW0391 2008 8 124523.3400 481799.8700
CW0391 2008 9 35392.6800 517192.5500
CW0391 2008 10 15710.2600 532902.8100
CW0391 2008 11 36213.8700 569116.6800
CW0391 2008 12 12433.0800 581549.7600
CW0391 2009 1 30311.5800 30311.5800
CW0391 2009 2 -7136.3400 23175.2400
CW0391 2009 3 -1.0000 23174.2400
CW0391 2009 4 .0000 23174.2400
CW0391 2009 5 1.0000 51437.2800
CW0391 2009 6 -445.0000 97706.2400
CW0391 2009 8 8537.0000 513067.1100

Any ideas?

Simon

SimonG
Starting Member

15 Posts

Posted - 2010-02-16 : 10:20:17

Found the best way was to use the transaction date, convert to last day of the month and then use the correlated subquery as proposed via the date.

Thanks for the help.

Simon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources