x

Dynamically Calculated Commulitive Column

 

 

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 '10 at 04:06 PM in Default

user-1028 (google) gravatar image

user-1028 (google)
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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 '10 at 07:52 PM

Squirrel 1 gravatar image

Squirrel 1
1.6k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

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.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x20

asked: Mar 28 '10 at 04:06 PM

Seen: 636 times

Last Updated: Mar 28 '10 at 04:06 PM