Home » SQL & PL/SQL » SQL & PL/SQL » Query : Need help on populating prev day data in current day if missing (Oracle 11.2)
Query : Need help on populating prev day data in current day if missing [message #656996] |
Tue, 25 October 2016 10:13 |
srinivas.k2005
Messages: 404 Registered: August 2006
|
Senior Member |
|
|
Hi All,
Need help on populating prev day data in current day if missing
My Source table data is a below
Select * from
(
select 'A' report_number, '12-NOV-2015' buss_date, 12323 num, 'D' dir, 09 bal from dual
UNION ALL
select 'A' report_number, '11-NOV-2015' buss_date, 12323 num, 'D' dir, 10 bal from dual
UNION ALL
select 'A' report_number, '11-NOV-2015' buss_date, 12323 num, 'E' dir, 100 bal from dual
UNION ALL
select 'A' report_number, '11-NOV-2015' buss_date, 12323 num, 'E' dir, 9 bal from dual
) neg
need output as below, i tried with outer join condition and LEAD and LAG for generic query, as the table is 12GB it is taking lot of time
Basically if i have recived 3 records yesterday , i need to make sure i have the same 3 record combination today with balance as 0 even
if i have not received the record with same combination of NUM and DIR
Select * from
(
select 'A' report_number, '12-NOV-2015' buss_date, 12323 num, 'D' dir, 19 bal from dual
UNION ALL
select 'A' report_number, '12-NOV-2015' buss_date, 12323 num, 'E' dir, 0 bal from dual
UNION ALL
select 'A' report_number, '11-NOV-2015' buss_date, 12323 num, 'D' dir, 10 bal from dual
UNION ALL
select 'A' report_number, '11-NOV-2015' buss_date, 12323 num, 'E' dir, 109 bal from dual
) neg
|
|
|
|
Re: Query : Need help on populating prev day data in current day if missing [message #656998 is a reply to message #656997] |
Tue, 25 October 2016 11:06 |
srinivas.k2005
Messages: 404 Registered: August 2006
|
Senior Member |
|
|
My Source table data is a below
drop table negative_balance_mov
Create table negative_balance_mov
as
Select * from
(
select 'A' report_number, TO_DATE('12-NOV-2015','DD-MON-YYYY') buss_date, 12323 num, 'D' dir, 09 bal from dual
UNION ALL
select 'A' report_number, TO_DATE('11-NOV-2015','DD-MON-YYYY') buss_date, 12323 num, 'D' dir, 10 bal from dual
UNION ALL
select 'A' report_number, TO_DATE('11-NOV-2015','DD-MON-YYYY') buss_date, 12323 num, 'E' dir, 100 bal from dual
UNION ALL
select 'A' report_number, TO_DATE('11-NOV-2015','DD-MON-YYYY') buss_date, 12323 num, 'E' dir, 9 bal from dual
) neg
need output as below, i tried with outer join condition and LEAD and LAG for generic query, as the table is 12GB it is taking lot of time
Basically if i have received 3 records yesterday , i need to make sure i have the same 3 record combination today with balance as 0 even
if i have not received the record with same combination of NUM and DIR
Report_number Buss_date Num Dir Balance
A 12-NOV-2015 12323 D 19
A 12-NOV-2015 12323 E 0 --- This record did not exists in my 12 Nov 2015 combination is Num and Dir,
--i have to populate it with 0 as balance with current date as i have to
calculate balance movement from previous day to current day, if record does
not exists the current day with the Dir and Num values same as 11 nov 2015,
the balance is not getting calculated i need to just populate a dummy
record with 0
A 11-NOV-2015 12323 D 10
A 11-NOV-2015 12323 E 109 --- This is SUM(balance) grouped by report_number, buss_date,num and dir
Adding to above when i run a query as below
with neg_bal as
(select
report_number,
buss_date,
num,
dir,
sum(bal) bal
from negative_balance_mov
group by
report_number,
buss_date,
num,
dir)
select
report_number,
buss_date,
num,
dir,
bal,
(select bal from neg_bal
where buss_date = a.buss_date -1
and num = a.num
and dir = A.DIR
) prev_day_bal
from neg_bal a
I get ouput only with 3 records . but i need the 4th record als0
report_number buss_date num dir bal prev_day_bal
A 11/11/2015 12323 E 109
A 11/11/2015 12323 D 10
A 11/12/2015 12323 D 9 10
Missing 4th record
A 11/12/2015 12323 E 0 109
Hope now i am clear with requirement
[Updated on: Tue, 25 October 2016 11:27] Report message to a moderator
|
|
|
|
|
Re: Query : Need help on populating prev day data in current day if missing [message #657001 is a reply to message #657000] |
Tue, 25 October 2016 11:51 |
srinivas.k2005
Messages: 404 Registered: August 2006
|
Senior Member |
|
|
Sorry Michael
Ignore the previous thread, please see below hope i have tried to explain my requirement. The report number will be same for all records only the NUM and DIR column varies
drop table negative_balance_mov
Create table negative_balance_mov
as
Select * from
(
select 'A' report_number, TO_DATE('12-NOV-2015','DD-MON-YYYY') buss_date, 12323 num, 'D' dir, 09 bal from dual
UNION ALL
select 'A' report_number, TO_DATE('11-NOV-2015','DD-MON-YYYY') buss_date, 12323 num, 'D' dir, 10 bal from dual
UNION ALL
select 'A' report_number, TO_DATE('11-NOV-2015','DD-MON-YYYY') buss_date, 12323 num, 'E' dir, 100 bal from dual
UNION ALL
select 'A' report_number, TO_DATE('11-NOV-2015','DD-MON-YYYY') buss_date, 12323 num, 'E' dir, 9 bal from dual
) neg
select * from negative_balance_mov
report_number buss_date num dir bal
A 11/12/2015 12323 D 9
A 11/11/2015 12323 D 10
A 11/11/2015 12323 E 100
A 11/11/2015 12323 E 9
I am running below query to get current balance - previous balance, but becuase the 11th Nov with Dir E has not come in 12th Nov the whole record is missing
i need that record with balance as zero and the prev_day balance as shown below in my expected output
with neg_bal as
(select
report_number,
buss_date,
num,
dir,
sum(bal) bal
from negative_balance_mov
group by
report_number,
buss_date,
num,
dir)
select
report_number,
buss_date,
num,
dir,
bal,
(select bal from neg_bal
where buss_date = a.buss_date -1
and num = a.num
and dir = A.DIR
) prev_day_bal
from neg_bal a
I get ouput only with 3 records . but i need the 4th record als0
report_number buss_date num dir bal prev_day_bal
A 11/11/2015 12323 E 109
A 11/11/2015 12323 D 10
A 11/12/2015 12323 D 9 10
Missing 4th record - I need to get this record also, how to change above query to get below record also
A 11/12/2015 12323 E 0 109
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 06:45:29 CDT 2024
|