# 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.. IDProductConsignDatePrice 1Product101/01/2001100 2Product201/01/2001140 3Product301/01/2001230 4Product102/01/2001160 5Product202/01/2001210 6Product302/01/2001270 7Product103/01/200190 8Product203/01/2001180 9Product303/01/2001300 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 MDYinsert into @sampleselect 1, 'Product1', '01/01/2001', 100 union allselect 2, 'Product2', '01/01/2001', 140 union allselect 3, 'Product3', '01/01/2001', 230 union allselect 4, 'Product1', '02/01/2001', 160 union allselect 5, 'Product2', '02/01/2001', 210 union allselect 6, 'Product3', '02/01/2001', 270 union allselect 7, 'Product1', '03/01/2001', 90 union allselect 8, 'Product2', '03/01/2001', 180 union allselect 9, 'Product3', '03/01/2001', 300 `````; withcteas( select ID, Product, ConsignDate, Price, row_no = row_number() over (partition by Product order by ConsignDate) from @sample),Priceas( 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 12 Product2 2001-01-01 00:00:00.000 140.00 140.00 13 Product3 2001-01-01 00:00:00.000 230.00 230.00 16 Product3 2001-02-01 00:00:00.000 270.00 186.00 29 Product3 2001-03-01 00:00:00.000 300.00 60.60 35 Product2 2001-02-01 00:00:00.000 210.00 63.00 28 Product2 2001-03-01 00:00:00.000 180.00 -65.70 34 Product1 2001-02-01 00:00:00.000 160.00 34.00 27 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 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

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

Topics:

x21

asked: Mar 28, 2010 at 04:06 PM

Seen: 733 times

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