Create Cost Period based on Start Date - PL/SQL

Question

Oracle 11g

I want to set End Date for each cost based on start date for new cost

For data below set the end date

Input:

Part number Site Cost           Start Date  End Date

000118-X    SR  8.926035461     10/1/2011   
000118-X    SR  10.7990022141   12/1/2011   
000118-X    SR  10.79900221     10/1/2012   
000118-X    SR  11.1574523796   12/1/2012   
000118-X    SR  11.15745238     10/1/2013   
000118-X    SR  14.054703823    12/1/2013   
000118-X    SR  16.4416037192   12/1/2014

Desired Output: End Date is Start Date-1 from next record

Part number Site Cost           Start Date  End Date

000118-X    SR  8.926035461     10/1/2011   11/30/2011
000118-X    SR  10.7990022141   12/1/2011   09/30/2012
000118-X    SR  10.79900221     10/1/2012   11/31/2012
000118-X    SR  11.1574523796   12/1/2012   09/30/2013
000118-X    SR  11.15745238     10/1/2013   11/30/2013
000118-X    SR  14.054703823    12/1/2013   11/30/2014
000118-X    SR  16.4416037192   12/1/2014

"Based on start date for new cost" is not clear in the example. — Alfabravo, Sep 01 '15 at 03:39
Each line has a new cost and new date so i need to put and end date to previous cost. output is already attached with end date which i want. — Arvind Singh Kamlakar, Sep 01 '15 at 03:42
But in your output the end date is randomly assigned. Is this something like fix values which you want to insert into the table? — Abhis, Sep 01 '15 at 03:45
Yes it is a fixed value. e.g the end date on first record is start date -1 from 2nd record and so on. — Arvind Singh Kamlakar, Sep 01 '15 at 03:47
Ok, the end date is the start date of next row minus one. Have you tried something? — Alfabravo, Sep 01 '15 at 03:47
Hello arvind welcome to StackOverflow, next time try to provide a [**SqlFiddle**](http://sqlfiddle.com/#!15/5368b/6) so we can understand the problem better and give you an answer much faster – Also please read [**How to ask**](http://stackoverflow.com/help/how-to-ask) and [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve) And for sql questions please include your rdbms and data sample with input and desire output. — Juan Carlos Oropeza, Sep 01 '15 at 03:47
We need rrdbms because date functions are different in each db — Juan Carlos Oropeza, Sep 01 '15 at 03:49
ok, thanks for the suggestion. I am new here. I am using Oracle 11g — Arvind Singh Kamlakar, Sep 01 '15 at 03:50

score 2 · Answer 1 · answered Sep 01 '15 at 05:31

You need to use LEAD() analytic function.

For schema setup, see SQL Fiddle Demo

SQL> WITH cost AS
  2    (SELECT lead(start_date) OVER (ORDER BY start_date) rn,
  3      t.*
  4    FROM t
  5    )
  6  SELECT part_number ,
  7         site,
  8         cost,
  9         start_date,
 10         rn -1 end_date
 11  FROM cost;

PART_NUMBER SITE       COST START_DATE END_DATE
----------- ---- ---------- ---------- ----------
000118-X    SR        8.926 10/01/2011 11/30/2011
000118-X    SR         10.8 12/01/2011 09/30/2012
000118-X    SR         10.8 10/01/2012 11/30/2012
000118-X    SR        11.16 12/01/2012 09/30/2013
000118-X    SR        11.16 10/01/2013 11/30/2013
000118-X    SR        14.05 12/01/2013 11/30/2014
000118-X    SR        16.44 12/01/2014

7 rows selected.

SQL>

Juan Carlos Oropeza · Answer 2 · 2015-09-01T04:27:19.263

1

First you asign a row number to each element

Then join to match each row with the next one.
And substract one day to the date

SQL Fiddle Demo

with cost as (
    SELECT         
        ROW_NUMBER() OVER (ORDER BY start_date) as R, t.*
    FROM testSQL t
)
SELECT 
    c1.part_number ,
    c1.site,
    c1.cost,  
    c1.start_date,
    c2.start_date - 1
FROM cost c1
left join cost c2 on c1.r = c2.r - 1

edited Sep 01 '15 at 04:27

answered Sep 01 '15 at 04:14

Juan Carlos Oropeza

47,252
12
78
118

1

No need for the costly join operation, simply use `LEAD()`. – Lalit Kumar B Sep 01 '15 at 05:31

Create Cost Period based on Start Date - PL/SQL

2 Answers2