Running total for previous year

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 2005 Forums

Transact-SQL (2005)

Running total for previous year

Author

Topic

koushik
Starting Member

11 Posts

Posted - 2010-09-09 : 02:04:34

Hi,

I have the following set of data. Please consider only first 5 columns because the last column I want to derive (which for the time being I put manually to explain).

Cust_Id Prod_Id Month_Id Qty YTD_Qty PYTD_Qty
C1 P1 20081001 8400 8400 Null
C1 P1 20081101 8400 16800 Null
C1 P1 20081201 8800 25600 Null
C1 P1 20090101 10400 10400 Null
C1 P1 20090201 7400 17800 Null
C1 P1 20090301 5600 23400 Null
C1 P1 20090401 6200 29600 Null
C1 P1 20090501 4800 34400 Null
C1 P1 20090601 7000 41400 Null
C1 P1 20090701 5200 46600 Null
C1 P1 20090801 4940 51540 Null
C1 P1 20090901 5000 56540 Null
C1 P1 20091001 4600 61140 8400
C1 P1 20091101 4400 65540 16800
C1 P1 20091201 5200 70740 25600
C1 P1 20100101 3800 3800 10400
C1 P1 20100201 6200 10000 17800
C1 P1 20100301 3400 13400 23400
C1 P1 20100401 5000 18400 29600
C1 P1 20100501 3600 22000 34400
C1 P1 20100601 4800 26800 41400
C1 P1 20100701 400 27200 46600

YTD_Qty is "Year-To-Date Quantity" here it is Year-To-Month actually.
It is actually the running total for a year.

Now PYTD_Qty is the "Previous-Year-To-Date Quantity" i.e. for Month_Id=20090201 YTD_Qty is 17800. The same value should get reflected under Month_Id=20100201 for PYTD_Qty (as shown in the above example).

Now my question is can I get this value with out using a self join on the table/view?

Thanks in advance.

Regards,
Koushik Chandra

Sachin.Nand

2937 Posts

Posted - 2010-09-09 : 02:35:51

Now my question is can I get this value with out using a self join on the table/view?

Any particular reason for this?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

koushik
Starting Member

11 Posts

Posted - 2010-09-09 : 02:40:55

The resultset I have derived is using a view (which is slow as accessing millions of records) and now on top of this if I self-join again this view, then performance will be really bad.

Regards,
Koushik Chandra

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources