# Dynamically Calculated Commulitive Column

 0 Hello All, I am stopped at a point.. i need some help from you intelligent guys.. Here is my table.. ID Product ConsignDate Price 1 Product1 01/01/2001 100 2 Product2 01/01/2001 140 3 Product3 01/01/2001 230 4 Product1 02/01/2001 160 5 Product2 02/01/2001 210 6 Product3 02/01/2001 270 7 Product1 03/01/2001 90 8 Product2 03/01/2001 180 9 Product3 03/01/2001 300 Now the question is i want to add fourth column Marginprice in my table through t-sql.. (i don't know why but my client has asked me to do so) Which is calculated as basic on this formula.. Based on every product If it is the first day then MarginPrice = Price else MarginPrice = (PreviousDay's MarginPrice - Price)*1.1 + PreviousDay's MarginPrice I am not finding any proper way to do this.. For example For Product1 MarginPrice on 2/1/2001 will be calculated as follows: PreviousDay's MarginPrice = 100 (as it is the first record) So, on 2/1/2001 MarginPrice = (100 - 160)*1.1 + 100 i.e. MarginPrice =34 Now on 3/1/2001.. MarginPrice will be calculated on basis of 34 So, on 3/1/2001 MarginPrice = (34 - 90)*1.1 + 34 i.e. MarginPrice = -27.6 Hope i was able to explain my question.. Thank you all..!! Regards, Paresh Rathod Paresh (VB.NET 2005) more ▼ asked Mar 28, 2010 at 04:06 PM in Default user-1028 (google) 1 ● 1 ● 1 ● 1 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 ```declare @sample table ( ID int, Product varchar(10), ConsignDate datetime, Price decimal(10,2) ) set dateformat MDY insert into @sample select 1, 'Product1', '01/01/2001', 100 union all select 2, 'Product2', '01/01/2001', 140 union all select 3, 'Product3', '01/01/2001', 230 union all select 4, 'Product1', '02/01/2001', 160 union all select 5, 'Product2', '02/01/2001', 210 union all select 6, 'Product3', '02/01/2001', 270 union all select 7, 'Product1', '03/01/2001', 90 union all select 8, 'Product2', '03/01/2001', 180 union all select 9, 'Product3', '03/01/2001', 300 `````` ``````; with cte as ( select ID, Product, ConsignDate, Price, row_no = row_number() over (partition by Product order by ConsignDate) from @sample ), Price as ( select ID, Product, ConsignDate, Price, MarginPrice = Price, row_no from cte where row_no = 1 `````` union all select c.ID, c.Product, c.ConsignDate, c.Price, MarginPrice = convert(decimal(10,2), (p.MarginPrice - c.Price) * 1.1 + p.MarginPrice), c.row_no from Price p inner join cte c on p.Product = c.Product and p.row_no = c.row_no - 1 ``````) select * from Price ``````ID Product ConsignDate Price MarginPrice row_no `````` ``````1 Product1 2001-01-01 00:00:00.000 100.00 100.00 1 2 Product2 2001-01-01 00:00:00.000 140.00 140.00 1 3 Product3 2001-01-01 00:00:00.000 230.00 230.00 1 6 Product3 2001-02-01 00:00:00.000 270.00 186.00 2 9 Product3 2001-03-01 00:00:00.000 300.00 60.60 3 5 Product2 2001-02-01 00:00:00.000 210.00 63.00 2 8 Product2 2001-03-01 00:00:00.000 180.00 -65.70 3 4 Product1 2001-02-01 00:00:00.000 160.00 34.00 2 7 Product1 2001-03-01 00:00:00.000 90.00 -27.60 3 ````(9 row(s) affected) ` more ▼ answered Mar 28, 2010 at 07:52 PM Squirrel 1 1.6k ● 1 ● 3 ● 5 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x22

asked: Mar 28, 2010 at 04:06 PM

Seen: 843 times

Last Updated: Mar 28, 2010 at 04:06 PM