Home » SQL & PL/SQL » SQL & PL/SQL » Generate movements
Generate movements [message #570749] Thu, 15 November 2012 05:47 Go to next message
RMSoares
Messages: 46
Registered: September 2010
Location: Lisboa
Member

Good morning,
I have a table with the balances of customers, the information is only fulfilled in the days when there were moves by the client, if the client had no movements on that day does not appear any registration.

It is intended to create the records of the balance to the days when there were no movements for all dates between a range, and the value of balance to create equal to the balance of the previous day.

with
     data as (     
        SELECT '20121101' as DT_PROC, 'B12345' as CLIENT_ID,  100 as VALUE FROM DUAL
        UNION ALL
        SELECT '20121107' as DT_PROC, 'B12345' as CLIENT_ID,  1345 as VALUE FROM DUAL
        UNION ALL
        SELECT '20121109' as DT_PROC, 'B12345' as CLIENT_ID,  20 as VALUE FROM DUAL
        UNION ALL
        SELECT '20121114' as DT_PROC, 'B12345' as CLIENT_ID,  300 as VALUE FROM DUAL
     )
SELECT * FROM data


Output expected for a range of 01/11/2012 to 11/15/2012 is as follows

DT_PROC CLIENT_ID VALUE
20121101 B12345 100
20121102 B12345 100
20121103 B12345 100
20121104 B12345 100
20121105 B12345 100
20121106 B12345 100
20121107 B12345 1345
20121108 B12345 1345
20121109 B12345 20
20121110 B12345 20
20121111 B12345 20
20121112 B12345 20
20121113 B12345 20
20121114 B12345 300
20121115 B12345 300
Re: Generate movements [message #570752 is a reply to message #570749] Thu, 15 November 2012 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
(Right) Outer join your table with a date generator.

Regards
Michel
Re: Generate movements [message #570755 is a reply to message #570752] Thu, 15 November 2012 06:37 Go to previous messageGo to next message
RMSoares
Messages: 46
Registered: September 2010
Location: Lisboa
Member

Hi,
how to propagated the previus values ?

with
     data as (     
        SELECT '20121101' as DT_PROC, 'B12345' as CLIENT_ID,  100 as VALUE FROM DUAL
        UNION ALL
        SELECT '20121107' as DT_PROC, 'B12345' as CLIENT_ID,  1345 as VALUE FROM DUAL
        UNION ALL
        SELECT '20121109' as DT_PROC, 'B12345' as CLIENT_ID,  20 as VALUE FROM DUAL
        UNION ALL
        SELECT '20121114' as DT_PROC, 'B12345' as CLIENT_ID,  300 as VALUE FROM DUAL
     )
SELECT B.DT_PROC, A.CLIENT_ID, A.VALUE 
FROM data A,
(select TO_DATE('20121031', 'YYYYMMDD') +  level DT_PROC from dual connect by level 
<= (TO_DATE('20121115', 'YYYYMMDD') - TO_DATE('20121031', 'YYYYMMDD'))) B
WHERE B.DT_PROC = A.DT_PROC(+)

[Updated on: Thu, 15 November 2012 11:17] by Moderator

Report message to a moderator

Re: Generate movements [message #570756 is a reply to message #570755] Thu, 15 November 2012 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
how to propagated the previus values ?

Use LAST_VALUE function.

Regards
Michel
Re: Generate movements [message #570757 is a reply to message #570756] Thu, 15 November 2012 07:04 Go to previous messageGo to next message
RMSoares
Messages: 46
Registered: September 2010
Location: Lisboa
Member

I'm tru to use the LAST_VALUE funcrion, but it dosen't work

with
     data as (     
        SELECT '20121101' as DT_PROC, 'B12345' as CLIENT_ID,  100 as VALUE FROM DUAL
        UNION ALL
        SELECT '20121107' as DT_PROC, 'B12345' as CLIENT_ID,  1345 as VALUE FROM DUAL
        UNION ALL
        SELECT '20121109' as DT_PROC, 'B12345' as CLIENT_ID,  20 as VALUE FROM DUAL
        UNION ALL
        SELECT '20121114' as DT_PROC, 'B12345' as CLIENT_ID,  300 as VALUE FROM DUAL
     )
SELECT DT_PROC, 
DECODE(CLIENT_ID, NULL, lag (CLIENT_ID,1) over (ORDER BY CLIENT_ID, DT_PROC), CLIENT_ID) 
as CLIENT_ID, 
DECODE(VALUE, NULL, LAST_VALUE(VALUE) OVER (PARTITION BY CLIENT_ID order by DT_PROC) , VALUE)  
AS lv   
FROM 
(
SELECT B.DT_PROC, A.CLIENT_ID, A.VALUE
FROM data A,
(select TO_DATE('20121031', 'YYYYMMDD') +  level DT_PROC from dual 
connect by level <= (TO_DATE('20121115', 'YYYYMMDD') - TO_DATE('20121031', 'YYYYMMDD'))) B
WHERE B.DT_PROC = A.DT_PROC(+)
)
ORDER BY  1 



Any help ?

[Updated on: Thu, 15 November 2012 11:30] by Moderator

Report message to a moderator

Re: Generate movements [message #570760 is a reply to message #570757] Thu, 15 November 2012 07:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must use IGNORE NULLS option in LAST_VALUE.
It is the same thing for CLIENT_ID and VALUE fields.
No DECODE is needed (if the value is not null then the last value is the current one).

Regards
Michel
Re: Generate movements [message #570775 is a reply to message #570760] Thu, 15 November 2012 11:29 Go to previous message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a solution, fixing your test case to have consistent data types and adding a flag showing the original values:
SQL> with
  2       data as (     
  3          SELECT to_date('20121101','YYYYMMDD') DT_PROC, 'B12345' CLIENT_ID,  100 VALUE FROM DUAL
  4          UNION ALL
  5          SELECT to_date('20121107','YYYYMMDD') DT_PROC, 'B12345' CLIENT_ID,  1345 VALUE FROM DUAL
  6          UNION ALL
  7          SELECT to_date('20121109','YYYYMMDD') DT_PROC, 'B12345' CLIENT_ID,  20 VALUE FROM DUAL
  8          UNION ALL
  9          SELECT to_date('20121114','YYYYMMDD') DT_PROC, 'B12345' CLIENT_ID,  300 VALUE FROM DUAL
 10       ),
 11    cal as (
 12     select TO_DATE('20121031', 'YYYYMMDD') +  level DT_PROC 
 13     from dual 
 14     connect by level <= (TO_DATE('20121115','YYYYMMDD') - TO_DATE('20121031','YYYYMMDD'))
 15     )
 16  select c.dt_proc, d.client_id,
 17         last_value(d.value ignore nulls) over (partition by d.client_id order by c.dt_proc)
 18           value,
 19         decode(d.value, null, '', '<-- ') flag
 20  from cal c
 21         left outer join
 22       data d partition by (client_id)
 23         on d.dt_proc = c.dt_proc
 24  order by client_id, dt_proc
 25  /
DT_PROC    CLIENT      VALUE FLAG
---------- ------ ---------- ----
01/11/2012 B12345        100 <--
02/11/2012 B12345        100
03/11/2012 B12345        100
04/11/2012 B12345        100
05/11/2012 B12345        100
06/11/2012 B12345        100
07/11/2012 B12345       1345 <--
08/11/2012 B12345       1345
09/11/2012 B12345         20 <--
10/11/2012 B12345         20
11/11/2012 B12345         20
12/11/2012 B12345         20
13/11/2012 B12345         20
14/11/2012 B12345        300 <--
15/11/2012 B12345        300

Regards
Michel

[Updated on: Thu, 15 November 2012 11:29]

Report message to a moderator

Previous Topic: how we resolve INVALID object
Next Topic: Bitmap index rebuild - getting ORA-00054
Goto Forum:
  


Current Time: Tue Sep 02 01:09:54 CDT 2014

Total time taken to generate the page: 0.12786 seconds