Home » SQL & PL/SQL » SQL & PL/SQL » Generate movements
| Generate movements [message #570749] |
Thu, 15 November 2012 05:47  |
RMSoares
Messages: 45
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 #570755 is a reply to message #570752] |
Thu, 15 November 2012 06:37  |
RMSoares
Messages: 45
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 #570757 is a reply to message #570756] |
Thu, 15 November 2012 07:04 |
RMSoares
Messages: 45
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 #570775 is a reply to message #570760] |
Thu, 15 November 2012 11:29 |
 |
Michel Cadot
Messages: 54127
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 |
|
|
|
Goto Forum:
Current Time: Sun May 19 01:37:18 CDT 2013
Total time taken to generate the page: 0.16732 seconds
|
] 
Members
Search
Help
Register
Login
Home
