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 Go to next message
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 #656997 is a reply to message #656996] Tue, 25 October 2016 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
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
This is completely pointless for me, above when there are no dates in the rows.
How "dir" comes into play in the requirement?
How "report_number" does?
And "num"?
"3 records" define "3 records" or define "same 3 record combination".

There are too few rows in your "test case" to match all possible cases, complete it.
And post the result you want AS A CHART not a query, or do you want the result to be a query?
And ALIGN the columns in the result.

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 Go to previous messageGo to next message
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 #656999 is a reply to message #656998] Tue, 25 October 2016 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why balance is 19 in first row?
Add a new 'D' at 11-MOV, how the result is changed?
Your requirements are not compete.
Your test case is not complete.
Given the complexity it requires at least 15 rows with more than one "report_number" and more than 2 dates.

Re: Query : Need help on populating prev day data in current day if missing [message #657000 is a reply to message #656999] Tue, 25 October 2016 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Ah, you added some things when I was writing.

Quote:
Hope now i am clear with requirement
Not at all, you have now a new column in the result and not the same result in another column.
So it is clear as mud.

As I said many things are missing.

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 Go to previous messageGo to next message
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


Re: Query : Need help on populating prev day data in current day if missing [message #657022 is a reply to message #657001] Wed, 26 October 2016 06:13 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Michael,

Am i still unclear?

Thanks
Re: Query : Need help on populating prev day data in current day if missing [message #657037 is a reply to message #657022] Wed, 26 October 2016 10:14 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes.
Post what I requested if you want me to try to help you in this one.

Previous Topic: NULL VS ''
Next Topic: Purging old records
Goto Forum:
  


Current Time: Fri Mar 29 01:34:34 CDT 2024