CODEWITHSUNDEEP
sqlsql-server
Afrah
Afrah

Reputation: 11

How to sum a value from the prev column to create a new value for the next column

CLICK THE IMAGE PLEASELink to Image

Please see the image above to show you an example of what I am trying to do. Thanks all.

I have a sales column with sales figures, I want to create a new forecast column and start it where the sales column has ended in this case 472031 + 34546. See the example

Sales Column 

34546
56497
89245
122952
160134
187809
227312
264421
308869
342777
388785
430483
472031

the latest sales figure was 472031 by adding 34546 then the below column will be 506,576 and so on.

See below for examples. Thanks in advance.

Sales Forecast Column

506,576 
541,122 
575,668 
610,213 
644,759 
679,305 
713,850 
748,396 
782,942 
817,487 
852,033 
886,579 
921,124 
955,670 
990,216 
1,024,761 
1,059,307 
1,093,853 
1,128,398 
1,162,944 
1,197,490 
1,232,035

Upvotes: 0

Views: 77

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

To calculate the new sales, you can do

select s.*,
       (case when sales = 0
             then row_number() over (partition by sales order by period) * 34546
        end) as inc_sales
from sales s;

Then, you want to add in the sales on the date just before. It so happens that if sales are increasing then this is the maximum of the sales. The result is:

select s.*,
       ((case when sales = 0
              then row_number() over (partition by sales order by period) * 34546
         end) +
        max(sales) over ()
       ) as forecast
from sales s;

Upvotes: -1

Menelaos
Menelaos

Reputation: 25725

Create Script

    create table sales(
  id int, 
  sales int);

insert into sales(id, sales) values (1,34546);
insert into sales(id, sales) values (2,56497);
insert into sales(id, sales) values (3,89245);
insert into sales(id, sales) values (4,122952);
insert into sales(id, sales) values (5,160134);
insert into sales(id, sales) values (6,187809);
insert into sales(id, sales) values (7,227312);
insert into sales(id, sales) values (8,264421);
insert into sales(id, sales) values (9,308869);
insert into sales(id, sales) values (10,342777);
insert into sales(id, sales) values (11,388785);
insert into sales(id, sales) values (12,430483);
insert into sales(id, sales) values (13,472031);

Queries

Example 1

select sales.sales, (id *34546)+472031 from sales;

Example2

WITH CTE AS (
  SELECT
    rownum = ROW_NUMBER() OVER (ORDER BY id),
    (((ROW_NUMBER() OVER (ORDER BY id)) - 1)  * 34546) as extraSales
  FROM sales
)
SELECT
  472031 + cur.extraSales
FROM CTE cur
INNER JOIN CTE prev on prev.rownum = cur.rownum - 1

SQL Fiddles

http://sqlfiddle.com/#!6/4bca1/9

http://sqlfiddle.com/#!6/4bca1/30

Upvotes: 2

Related Questions