0

I need a logic in pl/sql to reduce valid_to date field by one day than new from_date.

Example: my current from and to dates are : From: Jan-18-2016 To: Dec-31-9999

When new from and to dates arrive: New From: Feb-1-2016 New To: Dec-31-9999

I want my old to_date to become Jan_31-2016 (ie, (Feb-1-2016) -1)

Currently I'm facing multiple data issue since there would be two valid_to dates at same time. Please help

Rakesh HM
  • 9
  • 1
  • 1
    'When new from and to dates arrive' arrive to where? Are you storing these dates in a table? Is so, what is the structure of the table? – Hawk Jan 18 '16 at 07:20
  • Does this answer your question? http://stackoverflow.com/questions/15631977/subtract-one-day-from-datetime – Halcyon Jan 18 '16 at 11:53

1 Answers1

0

I have created dummy table with following data:

<table>
  <tr>
    <th>PRODUCT</th>
    <th>FROM_DATE</th>
    <th>VALID_TO</th>
  </tr>
  <tr>
    <td>ABC</td>
    <td>18-Jan-2016</td>
    <td>31-Dec-9999</td>
  </tr>
  <tr>
    <td>ABC</td>
    <td>01-Feb-2016</td>
    <td>31-Dec-9999</td>
  </tr>
  <tr>
    <td>DEF</td>
    <td>01-Aug-2016</td>
    <td>31-Dec-9999</td>
  </tr>
  <tr>
    <td>DEF</td>
    <td>01-Sep-2016</td>
    <td>31-Dec-9999</td>
  </tr>
  <tr>
    <td>GHI</td>
    <td>01-Mar-2016</td>
    <td>31-Dec-9999</td>
  </tr>
</table>

The following block will update the date according to your requirement. Using LAG and LEAD function you can access more than one row without joining the same table. LAG is used to access the previous row and LEAD is used to access the next row.

BEGIN

  FOR I IN (SELECT NVL(LEAD(T.FROM_DATE)
                       OVER(PARTITION BY T.PRODUCT ORDER BY T.FROM_DATE) - 1,
                       T.VALID_TO) NEW_VALID_TO,
                   PRODUCT,
                   FROM_DATE
              FROM PRODUCT_TABLE T) LOOP

    UPDATE PRODUCT_TABLE A
       SET A.VALID_TO = I.NEW_VALID_TO
     WHERE A.PRODUCT = I.PRODUCT
       AND A.FROM_DATE = I.FROM_DATE;

     COMMIT;

  END LOOP;

END;

The output will be as shown below: 

<table>
  <tr>
    <th>PRODUCT</th>
    <th>FROM_DATE</th>
    <th>VALID_TO</th>
  </tr>
  <tr>
    <td>ABC</td>
    <td>18-Jan-2016</td>
    <td>31-Jan-2016</td>
  </tr>
  <tr>
    <td>ABC</td>
    <td>01-Feb-2016</td>
    <td>31-Dec-9999</td>
  </tr>
  <tr>
    <td>DEF</td>
    <td>01-Aug-2016</td>
    <td>31-Aug-2016</td>
  </tr>
  <tr>
    <td>DEF</td>
    <td>01-Sep-2016</td>
    <td>31-Dec-9999</td>
  </tr>
  <tr>
    <td>GHI</td>
    <td>01-Mar-2016</td>
    <td>31-Dec-9999</td>
  </tr>
</table>
Nitish
  • 1,686
  • 8
  • 23
  • 42