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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Best tool for the job
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SimonG
Starting Member

United Kingdom
15 Posts

Posted - 02/15/2010 :  07:18:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 02/15/2010 :  07:34:22  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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/
Go to Top of Page

SimonG
Starting Member

United Kingdom
15 Posts

Posted - 02/16/2010 :  07:51:31  Show Profile  Reply with Quote
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
Go to Top of Page

SimonG
Starting Member

United Kingdom
15 Posts

Posted - 02/16/2010 :  10:20:17  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000