Dynamically Calculated Commulitive Column

Question

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)

Answer 1

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)

Dynamically Calculated Commulitive Column

Follow this question

Related Questions