CODEWITHSUNDEEP
sqloracle-databasejoinoracle10g
Hatik
Hatik

Reputation: 1159

Inserting the previous date's info if the current is empty Oracle

I have one table which hold following information

Table1 is a subquery, in order to simplify I will just use the resulted table:

| Account_No | Dept_ID | Currency| Amount | Date_2(dd/mm/yyyy)|
+------------+---------+---------+--------+-------------------+
| 1          | 1       | USD     | 50     | 03/01/2017        |
| 1          | 2       | EUR     | 25     | 01/01/2017        |
| 1          | 3       | USD     | 51     | 01/01/2017        |
| 1          | 1       | GBP     | 45     | 01/01/2017        |
| 1          | 2       | USD     | 65     | 02/01/2017        |

Amount is the amount of money at the end of the day of the account of specified currency of some department at that date. What is more that there can be variation of the same account by Currency and Dept_ID(Same Account_no but different currency and/or department ID) what I mean is that PK is combination of Account_no, Dept_ID and Currency.

I am trying to attach that table to the dates table which has everyday of some specified range:

Dates table:

|   Date_1   |
+------------+
| 01/01/2017 |
| 02/01/2017 |
| 03/01/2017 |
| 04/01/2017 |
| 05/01/2017 |
...

The expected result is:

|   Date_1   | Account_No | Dept_ID | Currency| Amount | Date_2(dd/mm/yyyy)|
+------------+------------+---------+---------+--------+-------------------+
| 01/01/2017 | 1          | 1       | USD     | 0      |                   |
| 01/01/2017 | 1          | 2       | USD     | 0      |                   |
| 01/01/2017 | 1          | 2       | EUR     | 25     | 01/01/2017        |
| 01/01/2017 | 1          | 3       | USD     | 51     | 01/01/2017        |
| 01/01/2017 | 1          | 1       | GBP     | 45     | 01/01/2017        |
| 02/01/2017 | 1          | 2       | USD     | 65     | 02/01/2017        |
| 02/01/2017 | 1          | 2       | EUR     | 25     |                   |
| 02/01/2017 | 1          | 3       | USD     | 51     |                   |
| 02/01/2017 | 1          | 1       | GBP     | 45     |                   |
| 02/01/2017 | 1          | 1       | USD     | 0      |                   |
| 03/01/2017 | 1          | 1       | USD     | 50     | 03/01/2017        |
| 03/01/2017 | 1          | 1       | GBP     | 45     |                   |
| 03/01/2017 | 1          | 3       | USD     | 51     |                   |
| 03/01/2017 | 1          | 2       | EUR     | 25     |                   |
| 03/01/2017 | 1          | 2       | USD     | 65     |                   |

So that for every date in Dates table I would have information from Table1, if the information is missing then it should select previous days' info. I have done the query of left joining but have no idea on how to fill the prev day's data into missing fields

SELECT * FROM DATES A LEFT JOIN TABLE1 B ON A.DATE_1 = B.DATE_2;

I get

|   Date_1   | Account_No | Dept_ID | Currency| Amount | Date_2(dd/mm/yyyy)|
+------------+------------+---------+---------+--------+-------------------+
| 01/01/2017 | 1          | 2       | EUR     | 25     | 01/01/2017        |
| 01/01/2017 | 1          | 3       | USD     | 51     | 01/01/2017        |
| 01/01/2017 | 1          | 1       | GBP     | 45     | 01/01/2017        |
| 02/01/2017 | 1          | 2       | USD     | 65     | 02/01/2017        |
| 03/01/2017 | 1          | 1       | USD     | 50     | 03/01/2017        |
| 04/01/2017 |            |         |         |        |                   |
...

Advice on how to proceed is appreciated

Upvotes: 0

Views: 45

Answers (3)

Boneist
Boneist

Reputation: 23588

You can also do this with a partition outer join plus a case statement that decides whether to put the current amount or the previous available amount if there isn't a current amount, like so:

WITH table1 AS (SELECT 1 account_no, 1 dept_id, 'USD' currency, 50 amount, to_date('03/01/2017', 'dd/mm/yyyy') date_2 FROM dual UNION ALL
                SELECT 1 account_no, 2 dept_id, 'EUR' currency, 25 amount, to_date('01/01/2017', 'dd/mm/yyyy') date_2 FROM dual UNION ALL
                SELECT 1 account_no, 3 dept_id, 'USD' currency, 51 amount, to_date('01/01/2017', 'dd/mm/yyyy') date_2 FROM dual UNION ALL
                SELECT 1 account_no, 1 dept_id, 'GBP' currency, 45 amount, to_date('01/01/2017', 'dd/mm/yyyy') date_2 FROM dual UNION ALL
                SELECT 1 account_no, 2 dept_id, 'USD' currency, 65 amount, to_date('02/01/2017', 'dd/mm/yyyy') date_2 FROM dual),
      dates AS (SELECT to_date('01/01/2017', 'dd/mm/yyyy') date_1 FROM dual UNION ALL
                SELECT to_date('02/01/2017', 'dd/mm/yyyy') date_1 FROM dual UNION ALL
                SELECT to_date('03/01/2017', 'dd/mm/yyyy') date_1 FROM dual UNION ALL
                SELECT to_date('04/01/2017', 'dd/mm/yyyy') date_1 FROM dual UNION ALL
                SELECT to_date('05/01/2017', 'dd/mm/yyyy') date_1 FROM dual)
SELECT d.date_1,
       t1.account_no,
       t1.dept_id,
       t1.currency,
       CASE WHEN t1.amount is NULL THEN
                 LAG(t1.amount, 1, 0) IGNORE NULLS OVER (PARTITION BY t1.account_no, t1.dept_id, t1.currency ORDER BY d.date_1)
            ELSE t1.amount
       END amount,
       t1.date_2
FROM   dates d
       LEFT OUTER JOIN table1 t1 PARTITION BY (t1.account_no, t1.dept_id, t1.currency)
         ON d.date_1 = t1.date_2
ORDER BY d.date_1,
         t1.account_no,
         t1.dept_id,
         t1.currency;

DATE_1      ACCOUNT_NO    DEPT_ID CURRENCY     AMOUNT DATE_2
----------- ---------- ---------- -------- ---------- -----------
01/01/2017           1          1 GBP              45 01/01/2017
01/01/2017           1          1 USD               0 
01/01/2017           1          2 EUR              25 01/01/2017
01/01/2017           1          2 USD               0 
01/01/2017           1          3 USD              51 01/01/2017
02/01/2017           1          1 GBP              45 
02/01/2017           1          1 USD               0 
02/01/2017           1          2 EUR              25 
02/01/2017           1          2 USD              65 02/01/2017
02/01/2017           1          3 USD              51 
03/01/2017           1          1 GBP              45 
03/01/2017           1          1 USD              50 03/01/2017
03/01/2017           1          2 EUR              25 
03/01/2017           1          2 USD              65 
03/01/2017           1          3 USD              51 
04/01/2017           1          1 GBP              45 
04/01/2017           1          1 USD              50 
04/01/2017           1          2 EUR              25 
04/01/2017           1          2 USD              65 
04/01/2017           1          3 USD              51 
05/01/2017           1          1 GBP              45 
05/01/2017           1          1 USD              50 
05/01/2017           1          2 EUR              25 
05/01/2017           1          2 USD              65 
05/01/2017           1          3 USD              51

N.B. If you are on a version of Oracle before 11.2, lag won't know about ignore nulls. You can use the following instead to simulate the same effect:

nvl(last_value(t1.amount) IGNORE NULLS OVER (PARTITION BY t1.account_no, t1.dept_id, t1.currency ORDER BY d.date_1), 0)

Upvotes: 1

Buddhi
Buddhi

Reputation: 421

WITH table1 AS
    (
        SELECT 1 account_no,
            1 dept_id,
            'USD' currency,
            50 amount,
            to_date('03/01/2017', 'dd/mm/yyyy') date_2
        FROM dual
        UNION ALL
        SELECT 1 account_no,
            2 dept_id,
            'EUR' currency,
            25 amount,
            to_date('01/01/2017', 'dd/mm/yyyy') date_2
        FROM dual
        UNION ALL
        SELECT 1 account_no,
            3 dept_id,
            'USD' currency,
            51 amount,
            to_date('01/01/2017', 'dd/mm/yyyy') date_2
        FROM dual
        UNION ALL
        SELECT 1 account_no,
            1 dept_id,
            'GBP' currency,
            45 amount,
            to_date('01/01/2017', 'dd/mm/yyyy') date_2
        FROM dual
        UNION ALL
        SELECT 1 account_no,
            2 dept_id,
            'USD' currency,
            65 amount,
            to_date('02/01/2017', 'dd/mm/yyyy') date_2
        FROM dual
    )
    ,
    dates AS
    (
        SELECT to_date('01/01/2017', 'dd/mm/yyyy') date_1 FROM dual
        UNION ALL
        SELECT to_date('02/01/2017', 'dd/mm/yyyy') date_1 FROM dual
        UNION ALL
        SELECT to_date('03/01/2017', 'dd/mm/yyyy') date_1 FROM dual
        UNION ALL
        SELECT to_date('04/01/2017', 'dd/mm/yyyy') date_1 FROM dual
        UNION ALL
        SELECT to_date('05/01/2017', 'dd/mm/yyyy') date_1 FROM dual
    )
SELECT d.date_1,
    t1.account_no,
    t1.dept_id,
    t1.currency,
    t1.amount,
    CASE (t1.date_2)
        WHEN d.date_1
        THEN t1.date_2
        ELSE NULL
    END
FROM table1 t1,
    dates d
ORDER BY d.date_1,
    t1.account_no,
    t1.dept_id,
    t1.currency;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270421

You can do this using lag() with the ignore nulls option. I think this is what you want:

select d.date_1, a.account_no,
       coalesce(dept_id,
                lag(dept_id ignore nulls) over (partition by t1.account_no order by d.date_1)
               ) as dept_id,
       coalesce(currency,
                lag(currency ignore nulls) over (partition by t1.account_no order by d.date_1)
               ) as currency,
       coalesce(amount,
                lag(amount ignore nulls) over (partition by t1.account_no order by d.date_1)
               ) as amount
from dates d CROSS JOIN
     (select distinct account_no from table1) a left join
     table1 t1
     on d.DATE_1 = t1.DATE_2 and a.account_no = t1.account_no;

Upvotes: 1

Related Questions