Home » SQL & PL/SQL » SQL & PL/SQL » Get Min Value Where It Is Not Null
icon14.gif  Get Min Value Where It Is Not Null [message #607673] Mon, 10 February 2014 21:24 Go to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
Hi all.

With this set of data
BILL_DATE	ACCOUNT_NUM	PAYMENT_DATE	PAYMENT_AMT
22-JAN-2013 12:00:00 AM	1009233022	11-FEB-2013 12:18:17 AM	-340
22-FEB-2013 12:00:00 AM	1009233022	14-MAR-2013 12:00:00 AM	-150
22-MAR-2013 12:00:00 AM	1009233022	10-APR-2013 10:03:49 AM	-150
22-APR-2013 12:00:00 AM	1009233022	19-JUN-2013 06:45:01 AM	
22-MAY-2013 12:00:00 AM	1009233022	19-JUN-2013 06:45:01 AM	-400
22-JUN-2013 12:00:00 AM	1009233022	19-OCT-2013 03:09:08 PM	
22-JUL-2013 12:00:00 AM	1009233022	19-OCT-2013 03:09:08 PM	
22-AUG-2013 12:00:00 AM	1009233022	19-OCT-2013 03:09:08 PM	
22-SEP-2013 12:00:00 AM	1009233022	19-OCT-2013 03:09:08 PM	-300
22-OCT-2013 12:00:00 AM	1009233022	26-OCT-2013 02:58:51 PM	-150
22-NOV-2013 12:00:00 AM	1009233022		
22-DEC-2013 12:00:00 AM	1009233022		

How would it be possible for me to achieve
BILL_DATE	ACCOUNT_NUM	PAYMENT_DATE	PAYMENT_AMT
22-JAN-2013 12:00:00 AM	1009233022	11-FEB-2013 12:18:17 AM	-340
22-FEB-2013 12:00:00 AM	1009233022	14-MAR-2013 12:00:00 AM	-150
22-MAR-2013 12:00:00 AM	1009233022	10-APR-2013 10:03:49 AM	-150
22-APR-2013 12:00:00 AM	1009233022	19-JUN-2013 06:45:01 AM	-400
22-JUN-2013 12:00:00 AM	1009233022	19-OCT-2013 03:09:08 PM	-300
22-OCT-2013 12:00:00 AM	1009233022	26-OCT-2013 02:58:51 PM	-150		


Thank yo very much.
Re: Get Min Value Where It Is Not Null [message #607674 is a reply to message #607673] Mon, 10 February 2014 21:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
what does this post have to do with SQL or PL/SQL?

Garbage In, Garbage Out

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Get Min Value Where It Is Not Null [message #607691 is a reply to message #607674] Tue, 11 February 2014 02:09 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
Sorry..

create table & insert data
create table V_TMP_RPT_UNIFI_V2 (
BILL_DATE      date,            
ACCOUNT_NUM    varchar2(10),
PAYMENT_DATE   date,          
PAYMENT_AMT    number);

Insert
---------------------------------------------------------------------------------------
insert into v_tmp_rpt_unifi_v2 (to_date('22-Jan-13','DD-Mon-YY'), '1009233022',to_date('11-Feb-13','DD-Mon-YY'), -340);
insert into v_tmp_rpt_unifi_v2 (to_date('22-Feb-13','DD-Mon-YY'), '1009233022',to_date('14-Mar-13','DD-Mon-YY'), -150);
insert into v_tmp_rpt_unifi_v2 (to_date('22-Mar-13','DD-Mon-YY'), '1009233022',to_date('10-Apr-13','DD-Mon-YY'), -150);
insert into v_tmp_rpt_unifi_v2 (to_date('22-Apr-13','DD-Mon-YY'), '1009233022',null, null);
insert into v_tmp_rpt_unifi_v2 (to_date('22-May-13','DD-Mon-YY'), '1009233022',to_date('19-Jun-13','DD-Mon-YY'), -400);
insert into v_tmp_rpt_unifi_v2 (to_date('22-Jun-13','DD-Mon-YY'), '1009233022',null, null);
insert into v_tmp_rpt_unifi_v2 (to_date('22-Jul-13','DD-Mon-YY'), '1009233022',null, null);
insert into v_tmp_rpt_unifi_v2 (to_date('22-Aug-13','DD-Mon-YY'), '1009233022',null, null);
insert into v_tmp_rpt_unifi_v2 (to_date('22-Sep-13','DD-Mon-YY'), '1009233022',to_date('19-Oct-13','DD-Mon-YY'), -300);
insert into v_tmp_rpt_unifi_v2 (to_date('22-Oct-13','DD-Mon-YY'), '1009233022',to_date('26-Oct-13','DD-Mon-YY'), -150);
insert into v_tmp_rpt_unifi_v2 (to_date('22-Nov-13','DD-Mon-YY'), '1009233022',null, null);
insert into v_tmp_rpt_unifi_v2 (to_date('22-Dec-13','DD-Mon-YY'), '1009233022',null, null);


Input V_TMP_RPT_UNIFI_V2:
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE             PAYMENT_AMT\
---------------------------------------------------------------------------------------
22-JAN-2013 12:00:00 AM  1009233022     11-FEB-2013 12:18:17 AM  -340
22-FEB-2013 12:00:00 AM  1009233022     14-MAR-2013 12:00:00 AM  -150
22-MAR-2013 12:00:00 AM  1009233022     10-APR-2013 10:03:49 AM  -150
22-APR-2013 12:00:00 AM  1009233022     19-JUN-2013 06:45:01 AM  
22-MAY-2013 12:00:00 AM  1009233022     19-JUN-2013 06:45:01 AM  -400
22-JUN-2013 12:00:00 AM  1009233022     19-OCT-2013 03:09:08 PM  
22-JUL-2013 12:00:00 AM  1009233022     19-OCT-2013 03:09:08 PM  
22-AUG-2013 12:00:00 AM  1009233022     19-OCT-2013 03:09:08 PM  
22-SEP-2013 12:00:00 AM  1009233022     19-OCT-2013 03:09:08 PM  -300
22-OCT-2013 12:00:00 AM  1009233022     26-OCT-2013 02:58:51 PM  -150
22-NOV-2013 12:00:00 AM  1009233022          
22-DEC-2013 12:00:00 AM  1009233022     


Desired output:
   
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE             PAYMENT_AMT
---------------------------------------------------------------------------------------
22-JAN-2013 12:00:00 AM  1009233022     11-FEB-2013 12:18:17 AM  -340
22-FEB-2013 12:00:00 AM  1009233022     14-MAR-2013 12:00:00 AM  -150
22-MAR-2013 12:00:00 AM  1009233022     10-APR-2013 10:03:49 AM  -150
22-APR-2013 12:00:00 AM  1009233022     19-JUN-2013 06:45:01 AM  -400
22-JUN-2013 12:00:00 AM  1009233022     19-OCT-2013 03:09:08 PM  -300
22-OCT-2013 12:00:00 AM  1009233022     26-OCT-2013 02:58:51 PM  -150 


Objective:
To get the first next row of PAYMENT_DATE and its corresponding PAYMENT_AMT once null PAYMENT_DATE is encountered.
For example, for BILL_DATE 22-APR-2013 12:00:00 AM, there is no PAYMENT_DATE, thus the next payment available is 19-JUN-2013 with PAYMENT_AMT of 400 .


I've tried using LEAD that seems to solved the objective but I failed to make it show the correct BILL_DATE
select
bill_Date,
account_num,
case when payment_date is null then lead(PAYMENT_DATE,1) OVER (ORDER BY PAYMENT_DATE) else payment_date end PAYMENT_DATE,
case when payment_date is null then lead(PAYMENT_amt,1) OVER (ORDER BY PAYMENT_DATE) else payment_amt end PAYMENT_AMT 
from V_TMP_RPT_UNIFI_V2


Thank you.

[Updated on: Tue, 11 February 2014 02:23]

Report message to a moderator

Re: Get Min Value Where It Is Not Null [message #607715 is a reply to message #607691] Tue, 11 February 2014 05:34 Go to previous messageGo to next message
msol25
Messages: 396
Registered: June 2011
Senior Member
Hi Aimy,

values clause missing in insert statement
Re: Get Min Value Where It Is Not Null [message #607716 is a reply to message #607715] Tue, 11 February 2014 05:38 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
For example:

insert into v_tmp_rpt_unifi_v2 (to_date('22-Jan-13','DD-Mon-YY'), '1009233022',to_date('11-Feb-13','DD-Mon-YY'), -340);


Should be:

insert into v_tmp_rpt_unifi_v2 (BILL_DATE, ACCOUNT_NUM, PAYMENT_DATE, PAYMENT_AMT) VALUES (to_date('22-Jan-13','DD-Mon-YY'), '1009233022',to_date('11-Feb-13','DD-Mon-YY'), -340);


HTH
-g

[Edit: formatting]

[Updated on: Tue, 11 February 2014 05:42]

Report message to a moderator

Re: Get Min Value Where It Is Not Null [message #607721 is a reply to message #607673] Tue, 11 February 2014 06:46 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
OK sorry again...

insert into v_tmp_rpt_unifi_v2 values (to_date('22-Jan-13','DD-Mon-YY'), '1009233022',to_date('11-Feb-13','DD-Mon-YY'), -340);
insert into v_tmp_rpt_unifi_v2 values (to_date('22-Feb-13','DD-Mon-YY'), '1009233022',to_date('14-Mar-13','DD-Mon-YY'), -150);
insert into v_tmp_rpt_unifi_v2 values (to_date('22-Mar-13','DD-Mon-YY'), '1009233022',to_date('10-Apr-13','DD-Mon-YY'), -150);
insert into v_tmp_rpt_unifi_v2 values (to_date('22-Apr-13','DD-Mon-YY'), '1009233022',null, null);
insert into v_tmp_rpt_unifi_v2 values (to_date('22-May-13','DD-Mon-YY'), '1009233022',to_date('19-Jun-13','DD-Mon-YY'), -400);
insert into v_tmp_rpt_unifi_v2 values (to_date('22-Jun-13','DD-Mon-YY'), '1009233022',null, null);
insert into v_tmp_rpt_unifi_v2 values (to_date('22-Jul-13','DD-Mon-YY'), '1009233022',null, null);
insert into v_tmp_rpt_unifi_v2 values (to_date('22-Aug-13','DD-Mon-YY'), '1009233022',null, null);
insert into v_tmp_rpt_unifi_v2 values (to_date('22-Sep-13','DD-Mon-YY'), '1009233022',to_date('19-Oct-13','DD-Mon-YY'), -300);
insert into v_tmp_rpt_unifi_v2 values (to_date('22-Oct-13','DD-Mon-YY'), '1009233022',to_date('26-Oct-13','DD-Mon-YY'), -150);
insert into v_tmp_rpt_unifi_v2 values (to_date('22-Nov-13','DD-Mon-YY'), '1009233022',null, null);
insert into v_tmp_rpt_unifi_v2 values (to_date('22-Dec-13','DD-Mon-YY'), '1009233022',null, null);
Re: Get Min Value Where It Is Not Null [message #607722 is a reply to message #607721] Tue, 11 February 2014 06:48 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
So, is it possible to achieve my objective?

Thank you.
Re: Get Min Value Where It Is Not Null [message #607724 is a reply to message #607722] Tue, 11 February 2014 07:16 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Look at the First Analytic
Re: Get Min Value Where It Is Not Null [message #607735 is a reply to message #607724] Tue, 11 February 2014 08:43 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
Thanks.

But I'm still lost how to use that function in this case.

Can you please help.

Thank you.
Re: Get Min Value Where It Is Not Null [message #607748 is a reply to message #607735] Tue, 11 February 2014 09:50 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Did you read the documentation, did you make any attempt to use the function with your data to see what it would do?
I assume that you have made some attempts prior to posting back? If so, post what you tried, we'll try to steer you from there Smile
Re: Get Min Value Where It Is Not Null [message #607780 is a reply to message #607748] Tue, 11 February 2014 20:42 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
I've tried it but there's nothing difference that I could see.

Finally, I go along with the conventional way which requires separate package to be executed:
select a.*, b.payment_amt from
(SELECT bill_date, account_num, payment_date
  FROM (select
        bill_date, account_num,
        PKG_RPT_UNIFI_v1.bill_payment_min(account_num,bill_date) payment_date,
        min(bill_date) OVER (partition by PKG_RPT_UNIFI_v1.bill_payment_min(account_num,bill_date)) maxcol
        from V_TMP_RPT_UNIFI_V2)
 WHERE bill_date=maxcol) a, v_tmp_rpt_unifi_v2 b
where a.account_num=b.account_num(+) and a.payment_date=b.payment_date(+)
order by 1,2,3;


Package
FUNCTION bill_payment_min (v_account_num in varchar2, v_bill_Date in date)
---------------------------------------------------------------------------------------
RETURN date
DETERMINISTIC
PARALLEL_ENABLE
IS v_count date;

BEGIN
       select min(payment_date) into v_Count
       FROM v_tmp_rpt_unifi_v2
       WHERE
       account_num = v_account_num and payment_Date >= v_bill_Date and payment_amt is not null;

RETURN (v_Count);

END;


Really hope that I could get it work through one single script.

Thank you.
Re: Get Min Value Where It Is Not Null [message #607795 is a reply to message #607780] Wed, 12 February 2014 00:27 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
Could someone proved me that the FIRST analytic function would work for this?

Thank you.
Re: Get Min Value Where It Is Not Null [message #607816 is a reply to message #607780] Wed, 12 February 2014 04:42 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
You say that you have tried it, but the code that you posted doesn't use it. Why not? I asked you to post your code where you tried to use the FIRST function, you posted code that does NOT use the first function (unless I'm missing something). I don't really understand why you would post the code that you did, it seems irrelevant to the documentation that I linked you to.
Re: Get Min Value Where It Is Not Null [message #607833 is a reply to message #607816] Wed, 12 February 2014 07:50 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
pablolee wrote on Wed, 12 February 2014 18:42
You say that you have tried it, but the code that you posted doesn't use it. Why not? I asked you to post your code where you tried to use the FIRST function, you posted code that does NOT use the first function (unless I'm missing something). I don't really understand why you would post the code that you did, it seems irrelevant to the documentation that I linked you to.

As I've mentioned to you, there is no difference when I'm using the FIRST. The code I used:
select
bill_date,
account_num,
min(payment_date) KEEP (DENSE_RANK FIRST ORDER BY bill_date) payment_date
from V_TMP_RPT_UNIFI_V2
group by account_num,
bill_date

The result is exactly the same as the input.

I already read the documentation but I could not digest it, that's why I'm asking for help here.

What's the point of me asking if no one is helping but only to ask me to read the documentation. Of course there's a lot of documentation out there.

Then you asked me why did I post those codes? It's simply because I am working on the solution by hook or by crook.

Thanks anyway.
Re: Get Min Value Where It Is Not Null [message #607842 is a reply to message #607833] Wed, 12 February 2014 09:54 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Quote:
As I've mentioned to you, there is no difference when I'm using the FIRST. The code I used:

I'm confused. You appear to be implying that you have written code using the FIRST syntax, yet you post code that is clearly not using the FIRST syntax and you also say:
Quote:
I already read the documentation but I could not digest it

Which would imply that you didn't understand the documentation and that therefore you were not able to use the FIRST syntax. Could you clarify which is the correct scenario?
In addition, cam you tell me what is wrong with using:
select *
from v_tmp_rpt_unifi_v2
where payment is not null


As it appears to return the results that you want from the data that you have supplied.
Re: Get Min Value Where It Is Not Null [message #607844 is a reply to message #607842] Wed, 12 February 2014 10:07 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
pablolee wrote on Wed, 12 February 2014 23:54
Quote:
As I've mentioned to you, there is no difference when I'm using the FIRST. The code I used:

I'm confused. You appear to be implying that you have written code using the FIRST syntax, yet you post code that is clearly not using the FIRST syntax and you also say:
Quote:
I already read the documentation but I could not digest it

Which would imply that you didn't understand the documentation and that therefore you were not able to use the FIRST syntax. Could you clarify which is the correct scenario?
In addition, cam you tell me what is wrong with using:
select *
from v_tmp_rpt_unifi_v2
where payment is not null


As it appears to return the results that you want from the data that you have supplied.

I did use FIRST as in the document example:
SELECT department_id,
MIN(salary) KEEP (DENSE_RANK [b]FIRST[/b] ORDER BY commission_pct) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best"
   FROM employees
   GROUP BY department_id;

My case
select
bill_date,
account_num,
min(payment_date) KEEP (DENSE_RANK [b]FIRST[/b] ORDER BY bill_date) payment_date
from V_TMP_RPT_UNIFI_V2
group by account_num,
bill_date

Maybe you just straight to the point of the question Get Min Value Where It Is Not Null
But actually if you read again my 1st post.
BILL_DATE	ACCOUNT_NUM	PAYMENT_DATE	PAYMENT_AMT
22-JAN-2013 12:00:00 AM	1009233022	11-FEB-2013 12:18:17 AM	-340
22-FEB-2013 12:00:00 AM	1009233022	14-MAR-2013 12:00:00 AM	-150
22-MAR-2013 12:00:00 AM	1009233022	10-APR-2013 10:03:49 AM	-150
22-MAY-2013 12:00:00 AM	1009233022	19-JUN-2013 06:45:01 AM	-400
22-SEP-2013 12:00:00 AM	1009233022	19-OCT-2013 03:09:08 PM	-300
22-OCT-2013 12:00:00 AM	1009233022	26-OCT-2013 02:58:51 PM	-150
is not what I've been looking for.

Instead I want the output to come out like this
BILL_DATE	ACCOUNT_NUM	PAYMENT_DATE	PAYMENT_AMT
22-JAN-2013 12:00:00 AM	1009233022	11-FEB-2013 12:18:17 AM	-340
22-FEB-2013 12:00:00 AM	1009233022	14-MAR-2013 12:00:00 AM	-150
22-MAR-2013 12:00:00 AM	1009233022	10-APR-2013 10:03:49 AM	-150
22-APR-2013 12:00:00 AM	1009233022	19-JUN-2013 06:45:01 AM	-400
22-JUN-2013 12:00:00 AM	1009233022	19-OCT-2013 03:09:08 PM	-300
22-OCT-2013 12:00:00 AM	1009233022	26-OCT-2013 02:58:51 PM	-150	


Thanks for your concern anyway.

Well, I guess I could never achieve it with extra work perhaps. That's why still nobody answered my question correctly.
Re: Get Min Value Where It Is Not Null [message #607845 is a reply to message #607842] Wed, 12 February 2014 10:12 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
pablolee wrote on Wed, 12 February 2014 15:54
Quote:
As I've mentioned to you, there is no difference when I'm using the FIRST. The code I used:

I'm confused. You appear to be implying that you have written code using the FIRST syntax, yet you post code that is clearly not using the FIRST syntax


You're definitely confused, query in the post above the one I quote pretty much exactly matches the example in the documentation page you've linked to. Did you actually mean first_value?
Re: Get Min Value Where It Is Not Null [message #607889 is a reply to message #607845] Thu, 13 February 2014 03:04 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Here's one way:

SQL> WITH bill_dates AS (SELECT bill_date,
  2                             account_num,
  3                             row_number() OVER (PARTITION BY account_num ORDER BY bill_date) bill_order
  4                      FROM (SELECT bill_date,
  5                                   account_num,
  6                                   LAG(payment_date,1, payment_date)
  7                                       OVER (PARTITION BY account_num ORDER BY bill_date) last_pay
  8                            FROM V_TMP_RPT_UNIFI_V2)
  9                      WHERE last_pay IS NOT NULL
 10                     ),
 11  pay_dates AS (SELECT payment_date,
 12                       payment_amt,
 13                       account_num,
 14                       row_number() OVER (PARTITION BY account_num ORDER BY bill_date) payment_order
 15                FROM V_TMP_RPT_UNIFI_V2 v
 16                WHERE payment_date IS NOT NULL
 17               )
 18  SELECT b.bill_date, b.account_num, p.payment_date, p.payment_amt
 19  FROM bill_dates b, pay_dates p
 20  WHERE b.account_num = p.account_num
 21  AND b.bill_order = p.payment_order
 22  ORDER BY bill_date;
 
BILL_DATE   ACCOUNT_NUM PAYMENT_DATE PAYMENT_AMT
----------- ----------- ------------ -----------
22/01/2013  1009233022  11/02/2013          -340
22/02/2013  1009233022  14/03/2013          -150
22/03/2013  1009233022  10/04/2013          -150
22/04/2013  1009233022  19/06/2013          -400
22/06/2013  1009233022  19/10/2013          -300
22/10/2013  1009233022  26/10/2013          -150
 
6 rows selected
 
SQL> 


I split the data into two sets. One is the list of payment dates and payment amounts ordered by bill_date.
The other is the set of bill_dates that should be in the output.
I assigned a row_number to each set and used that to stitch them back together.
If you want to see what it's doing just run the two with clauses individually.
Re: Get Min Value Where It Is Not Null [message #607908 is a reply to message #607889] Thu, 13 February 2014 07:16 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
Finally...

Thanks a lot cookiemonster.

But I'm sorry to inform that with these set of data, your given solution as above seems invalid.
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE   PAYMENT_AMT
01-JAN-2013 12:00:00 AM  1000085249          
01-FEB-2013 12:00:00 AM  1000085249     04-FEB-2013 04:00:10 PM  -600
01-MAR-2013 12:00:00 AM  1000085249          
01-APR-2013 12:00:00 AM  1000085249          
01-MAY-2013 12:00:00 AM  1000085249     07-MAY-2013 07:16:06 AM  -152.9
01-JUN-2013 12:00:00 AM  1000085249          
01-JUL-2013 12:00:00 AM  1000085249          
01-AUG-2013 12:00:00 AM  1000085249          
01-SEP-2013 12:00:00 AM  1000085249          
01-OCT-2013 12:00:00 AM  1000085249          
01-NOV-2013 12:00:00 AM  1000085249          
01-DEC-2013 12:00:00 AM  1000085249          

insert data
insert into v_tmp_rpt_unifi_v2 values (to_date('01-JAN-2013','DD-Mon-YY'), '1000085249',null, null);
insert into v_tmp_rpt_unifi_v2 values (to_date('01-FEB-2013','DD-Mon-YY'), '1000085249',to_date('04-FEB-2013','DD-Mon-YY'), -600);   
insert into v_tmp_rpt_unifi_v2 values (to_date('01-MAR-2013','DD-Mon-YY'), '1000085249',null, null);
insert into v_tmp_rpt_unifi_v2 values (to_date('01-APR-2013','DD-Mon-YY'), '1000085249',null, null);
insert into v_tmp_rpt_unifi_v2 values (to_date('01-MAY-2013','DD-Mon-YY'), '1000085249',to_date('07-MAY-2013','DD-Mon-YY'), -152.9); 
insert into v_tmp_rpt_unifi_v2 values (to_date('01-JUN-2013','DD-Mon-YY'), '1000085249',null, null);
insert into v_tmp_rpt_unifi_v2 values (to_date('01-JUL-2013','DD-Mon-YY'), '1000085249',null, null);
insert into v_tmp_rpt_unifi_v2 values (to_date('01-AUG-2013','DD-Mon-YY'), '1000085249',null, null);
insert into v_tmp_rpt_unifi_v2 values (to_date('01-SEP-2013','DD-Mon-YY'), '1000085249',null, null);
insert into v_tmp_rpt_unifi_v2 values (to_date('01-OCT-2013','DD-Mon-YY'), '1000085249',null, null);
insert into v_tmp_rpt_unifi_v2 values (to_date('01-NOV-2013','DD-Mon-YY'), '1000085249',null, null);
insert into v_tmp_rpt_unifi_v2 values (to_date('01-DEC-2013','DD-Mon-YY'), '1000085249',null, null);

would output
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE             PAYMENT_AMT
01-MAR-2013 12:00:00 AM  1000085249     04-FEB-2013 04:00:10 PM  -600
01-JUN-2013 12:00:00 AM  1000085249     07-MAY-2013 07:16:06 AM  -152.9

which should be
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE             PAYMENT_AMT
01-JAN-2013 12:00:00 AM  1000085249     04-FEB-2013 04:00:10 PM  -600
01-MAR-2013 12:00:00 AM  1000085249     07-MAY-2013 07:16:06 AM  -152.9

instead.

Any help?

Thank you.
Re: Get Min Value Where It Is Not Null [message #607914 is a reply to message #607908] Thu, 13 February 2014 07:44 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
That would be because of this bit:
SQL> SELECT bill_date,
  2         account_num,
  3         LAG(payment_date,1, payment_date)
  4             OVER (PARTITION BY account_num ORDER BY bill_date) last_pay
  5  FROM V_TMP_RPT_UNIFI_V2;
 
BILL_DATE   ACCOUNT_NUM LAST_PAY
----------- ----------- -----------
01/01/2013  1000085249  
01/02/2013  1000085249  
01/03/2013  1000085249  04/02/2013
01/04/2013  1000085249  
01/05/2013  1000085249  
01/06/2013  1000085249  07/05/2013
01/07/2013  1000085249  
01/08/2013  1000085249  
01/09/2013  1000085249  
01/10/2013  1000085249  
01/11/2013  1000085249  
01/12/2013  1000085249  
 
12 rows selected
 
SQL> 


The third parameter to lag is a default value to be used where there is no record in the window that can be checked. So it applies to the first record of a given partition. I used payment_date, which worked with the orginal data set but not this one. Change it to a value that can never be null - sysdate for example - and it'll work. The way the query is contructed it doesn't matter what exact value lag returns, just that it's not null.
Re: Get Min Value Where It Is Not Null [message #607930 is a reply to message #607914] Thu, 13 February 2014 09:29 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
Thanks again cookie.

But again Im really sorry, there's another rule, if the payment_date is less than the bill_date, it should be ignore and proceed with the next available payment date.

For example, this set of data
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE             PAYMENT_AMT
31-JAN-2013 12:00:00 AM  1000071082     29-JAN-2013 06:45:59 PM  -705.1
28-FEB-2013 12:00:00 AM  1000071082          
28-MAR-2013 12:00:00 AM  1000071082          
28-APR-2013 12:00:00 AM  1000071082     15-MAY-2013 12:21:14 PM  -469.95
28-MAY-2013 12:00:00 AM  1000071082          
28-JUN-2013 12:00:00 AM  1000071082          
28-JUL-2013 12:00:00 AM  1000071082     05-AUG-2013 05:07:31 PM  -470.2
28-AUG-2013 12:00:00 AM  1000071082     04-SEP-2013 05:16:57 PM  -470
28-SEP-2013 12:00:00 AM  1000071082          
28-OCT-2013 12:00:00 AM  1000071082     05-NOV-2013 08:26:48 AM  -470
28-NOV-2013 12:00:00 AM  1000071082          
28-DEC-2013 12:00:00 AM  1000071082     20-JAN-2014 03:18:39 PM  -470

INSERT INTO V_TMP_INSERT (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('31/01/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1000071082',to_date('29/01/2013 06:45:59', 'dd/mm/rrrr hh:mi:ss'),-705.1);
INSERT INTO V_TMP_INSERT (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('28/02/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1000071082',to_date('', 'dd/mm/rrrr hh:mi:ss'),null);
INSERT INTO V_TMP_INSERT (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('28/03/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1000071082',to_date('', 'dd/mm/rrrr hh:mi:ss'),null);
INSERT INTO V_TMP_INSERT (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('28/04/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1000071082',to_date('15/05/2013 12:21:14', 'dd/mm/rrrr hh:mi:ss'),-469.95);
INSERT INTO V_TMP_INSERT (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('28/05/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1000071082',to_date('', 'dd/mm/rrrr hh:mi:ss'),null);
INSERT INTO V_TMP_INSERT (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('28/06/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1000071082',to_date('', 'dd/mm/rrrr hh:mi:ss'),null);
INSERT INTO V_TMP_INSERT (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('28/07/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1000071082',to_date('05/08/2013 05:07:31', 'dd/mm/rrrr hh:mi:ss'),-470.2);
INSERT INTO V_TMP_INSERT (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('28/08/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1000071082',to_date('04/09/2013 05:16:57', 'dd/mm/rrrr hh:mi:ss'),-470);
INSERT INTO V_TMP_INSERT (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('28/09/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1000071082',to_date('', 'dd/mm/rrrr hh:mi:ss'),null);
INSERT INTO V_TMP_INSERT (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('28/10/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1000071082',to_date('05/11/2013 08:26:48', 'dd/mm/rrrr hh:mi:ss'),-470);
INSERT INTO V_TMP_INSERT (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('28/11/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1000071082',to_date('', 'dd/mm/rrrr hh:mi:ss'),null);
INSERT INTO V_TMP_INSERT (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('28/12/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1000071082',to_date('20/01/2014 03:18:39', 'dd/mm/rrrr hh:mi:ss'),-470);


The desired output is
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE             PAYMENT_AMT
31-JAN-2013 12:00:00 AM  1000071082     15-MAY-2013 12:21:14 PM  -469.95
28-MAY-2013 12:00:00 AM  1000071082     05-AUG-2013 05:07:31 PM  -470.2
28-AUG-2013 12:00:00 AM  1000071082     04-SEP-2013 05:16:57 PM  -470
28-SEP-2013 12:00:00 AM  1000071082     05-NOV-2013 08:26:48 AM  -470
28-NOV-2013 12:00:00 AM  1000071082     20-JAN-2014 03:18:39 PM  -470

instead of
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE             PAYMENT_AMT
31-JAN-2013 12:00:00 AM  1000071082     29-JAN-2013 06:45:59 PM  -705.1
28-FEB-2013 12:00:00 AM  1000071082     15-MAY-2013 12:21:14 PM  -469.95
28-MAY-2013 12:00:00 AM  1000071082     05-AUG-2013 05:07:31 PM  -470.2
28-AUG-2013 12:00:00 AM  1000071082     04-SEP-2013 05:16:57 PM  -470
28-SEP-2013 12:00:00 AM  1000071082     05-NOV-2013 08:26:48 AM  -470
28-NOV-2013 12:00:00 AM  1000071082     20-JAN-2014 03:18:39 PM  -470

For the pay_dates portion, I think it could be modified as simply as
pay_dates AS (SELECT payment_date,
payment_amt,
account_num,
row_number() OVER (PARTITION BY account_num ORDER BY bill_date) payment_order
FROM V_TMP_RPT_UNIFI_V1 v
WHERE payment_date IS NOT NULL and payment_date >= bill_date
and account_num='1000071082'
)


But I have no idea on how I am going to order the bill_dates portion.

Your solution is actually significantly faster, so really appreciate if you could help.

Thank you.
Re: Get Min Value Where It Is Not Null [message #607933 is a reply to message #607930] Thu, 13 February 2014 09:43 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You're right on the payment_Dates bit. To fix the other side you need to suppress payment_Date when it's invalid so that lag doesn't pick it up.
Do that by adding another level of nesting and a case statement to only return payment_date if it's valid:

SQL> WITH bill_dates AS (SELECT bill_date,
  2                                 account_num,
  3                                 row_number() OVER (PARTITION BY account_num ORDER BY bill_date) bill_order
  4                          FROM (SELECT bill_date,
  5                                       account_num,
  6                                       LAG(payment_date,1, SYSDATE)
  7                                           OVER (PARTITION BY account_num ORDER BY bill_date) last_pay
  8                                FROM (SELECT bill_date,
  9                                       account_num,
 10                                       CASE WHEN payment_date >= bill_date THEN payment_date END AS payment_date
 11                                      FROM V_TMP_RPT_UNIFI_V2))
 12                          WHERE last_pay IS NOT NULL
 13                        ),
 14     pay_dates AS (SELECT payment_date,
 15                          payment_amt,
 16                          account_num,
 17                          row_number() OVER (PARTITION BY account_num ORDER BY bill_date) payment_order
 18                   FROM V_TMP_RPT_UNIFI_V2 v
 19                   WHERE payment_date IS NOT NULL and payment_date >= bill_date
 20                  )
 21     SELECT b.bill_date, b.account_num, p.payment_date, p.payment_amt
 22     FROM bill_dates b, pay_dates p
 23     WHERE b.account_num = p.account_num
 24     AND b.bill_order = p.payment_order
 25     ORDER BY bill_date;
 
BILL_DATE   ACCOUNT_NUM PAYMENT_DATE PAYMENT_AMT
----------- ----------- ------------ -----------
31/01/2013  1000071082  15/05/2013 1     -469.95
28/05/2013  1000071082  05/08/2013 0      -470.2
28/08/2013  1000071082  04/09/2013 0        -470
28/09/2013  1000071082  05/11/2013 0        -470
28/11/2013  1000071082  20/01/2014 0        -470
 
SQL> 


And in future please try and keep consistent table names for your examples.
Re: Get Min Value Where It Is Not Null [message #607934 is a reply to message #607933] Thu, 13 February 2014 09:45 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
Sorry about the table names, forget to change it back. Coz I'm using script to generate it Very Happy

Thanks.
Re: Get Min Value Where It Is Not Null [message #607954 is a reply to message #607934] Thu, 13 February 2014 20:08 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
I'm sorry but there are still few more cases that need to be cater.

For example, with this set of data
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE             PAYMENT_AMT
16-JAN-2013 12:00:00 AM  1006977209     23-JAN-2013 03:35:27 PM  -150
16-FEB-2013 12:00:00 AM  1006977209     02-MAR-2013 12:00:00 AM  -150
16-MAR-2013 12:00:00 AM  1006977209     02-APR-2013 07:58:39 PM  -150
16-APR-2013 12:00:00 AM  1006977209     05-MAY-2013 03:30:59 PM  -300
16-MAY-2013 12:00:00 AM  1006977209     31-MAY-2013 07:40:25 AM  -150
16-JUN-2013 12:00:00 AM  1006977209     08-JUL-2013 06:02:11 PM  -150
16-JUL-2013 12:00:00 AM  1006977209          
16-AUG-2013 12:00:00 AM  1006977209     29-AUG-2013 10:50:44 AM  -310
16-SEP-2013 12:00:00 AM  1006977209     04-OCT-2013 10:28:43 AM  -150
16-OCT-2013 12:00:00 AM  1006977209     10-NOV-2013 12:12:19 PM  -150
16-NOV-2013 12:00:00 AM  1006977209     10-DEC-2013 05:58:54 PM  -150
28-NOV-2013 12:00:00 AM  1006977209          
28-DEC-2013 12:00:00 AM  1006977209     30-DEC-2013 12:03:19 PM  -150

INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/01/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('23/01/2013 03:35:27', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/02/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('02/03/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/03/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('02/04/2013 07:58:39', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/04/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('05/05/2013 03:30:59', 'dd/mm/rrrr hh:mi:ss'),-300);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/05/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('31/05/2013 07:40:25', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/06/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('08/07/2013 06:02:11', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/07/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('', 'dd/mm/rrrr hh:mi:ss'),null);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/08/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('29/08/2013 10:50:44', 'dd/mm/rrrr hh:mi:ss'),-310);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/09/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('04/10/2013 10:28:43', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/10/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('10/11/2013 12:12:19', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/11/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('10/12/2013 05:58:54', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('28/11/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('', 'dd/mm/rrrr hh:mi:ss'),null);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('28/12/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('30/12/2013 12:03:19', 'dd/mm/rrrr hh:mi:ss'),-150);


For each month, need only to pick up the min bill_Date. So, that the output would be:
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE             PAYMENT_AMT
16-JAN-2013 12:00:00 AM  1006977209     23-JAN-2013 03:35:27 PM  -150
16-FEB-2013 12:00:00 AM  1006977209     02-MAR-2013 12:00:00 AM  -150
16-MAR-2013 12:00:00 AM  1006977209     02-APR-2013 07:58:39 PM  -150
16-APR-2013 12:00:00 AM  1006977209     05-MAY-2013 03:30:59 PM  -300
16-MAY-2013 12:00:00 AM  1006977209     31-MAY-2013 07:40:25 AM  -150
16-JUN-2013 12:00:00 AM  1006977209     08-JUL-2013 06:02:11 PM  -150
16-JUL-2013 12:00:00 AM  1006977209     29-AUG-2013 10:50:44 AM  -310
16-SEP-2013 12:00:00 AM  1006977209     04-OCT-2013 10:28:43 AM  -150
16-OCT-2013 12:00:00 AM  1006977209     10-NOV-2013 12:12:19 PM  -150
16-NOV-2013 12:00:00 AM  1006977209     10-DEC-2013 05:58:54 PM  -150
28-DEC-2013 12:00:00 AM  1006977209     30-DEC-2013 12:03:19 PM  -150

instead of
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE             PAYMENT_AMT
16-JAN-2013 12:00:00 AM  1006977209     23-JAN-2013 03:35:27 PM  -150
16-FEB-2013 12:00:00 AM  1006977209     02-MAR-2013 12:00:00 AM  -150
16-MAR-2013 12:00:00 AM  1006977209     02-APR-2013 07:58:39 PM  -150
16-APR-2013 12:00:00 AM  1006977209     05-MAY-2013 03:30:59 PM  -300
16-MAY-2013 12:00:00 AM  1006977209     31-MAY-2013 07:40:25 AM  -150
16-JUN-2013 12:00:00 AM  1006977209     08-JUL-2013 06:02:11 PM  -150
16-JUL-2013 12:00:00 AM  1006977209     29-AUG-2013 10:50:44 AM  -310
16-SEP-2013 12:00:00 AM  1006977209     04-OCT-2013 10:28:43 AM  -150
16-OCT-2013 12:00:00 AM  1006977209     10-NOV-2013 12:12:19 PM  -150
16-NOV-2013 12:00:00 AM  1006977209     10-DEC-2013 05:58:54 PM  -150
28-NOV-2013 12:00:00 AM  1006977209     30-DEC-2013 12:03:19 PM  -150

Would it be possible?

Thank you.
Re: Get Min Value Where It Is Not Null [message #607955 is a reply to message #607954] Thu, 13 February 2014 20:50 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
It could be done with
min(bill_date) OVER (PARTITION BY to_char(bill_date, 'MON-YYYY')) AS min_bill
but where do I put it?

Since
SELECT bill_date, 
       account_num, 
       Row_number() 
         over ( 
           PARTITION BY account_num 
           ORDER BY bill_date) bill_order 
FROM   (SELECT bill_date, 
               account_num, 
               Lag(payment_date, 1, SYSDATE) 
                 over ( 
                   PARTITION BY account_num 
                   ORDER BY bill_date)                          last_pay, 
               Min(bill_date) 
                 over ( 
                   PARTITION BY To_char(bill_date, 'MON-YYYY')) AS min_bill 
        FROM   (SELECT bill_date, 
                       account_num, 
                       CASE 
                         WHEN payment_date >= bill_date THEN payment_date 
                       END AS payment_date 
                FROM   v_tmp_rpt_unifi_v2)) 
WHERE  last_pay IS NOT NULL 
       AND bill_date = min_bill 

would always eliminate the last bill_date Sad

*BlackSwan formatted SQL. Please do so yourself in the future. http://www.dpriver.com/pp/sqlformat.htm?ref=g_wangz

[Updated on: Thu, 13 February 2014 20:54] by Moderator

Report message to a moderator

Re: Get Min Value Where It Is Not Null [message #607956 is a reply to message #607955] Thu, 13 February 2014 21:43 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
OK.

I got it..
WITH bill_dates AS
   (SELECT bill_date,
    account_num,
    row_number() OVER (PARTITION BY account_num ORDER BY bill_date) bill_order
      FROM (SELECT bill_date,
            account_num,
            LAG(payment_date,1, SYSDATE)
            OVER (PARTITION BY account_num ORDER BY bill_date) last_pay
              FROM (SELECT bill_date,
                    account_num,
                    CASE WHEN payment_date >= bill_date THEN payment_date END AS payment_date,
                    min(bill_date) OVER (PARTITION BY account_num, to_char(bill_date, 'MON-YYYY')) AS min_bill
                    FROM V_TMP_RPT_UNIFI_V2
--                     where account_num='1009520352'
                   ) where bill_date = min_bill
             )
      WHERE last_pay IS NOT NULL
      )


Thanks a lot cookiemonster.

[Updated on: Thu, 13 February 2014 22:10]

Report message to a moderator

Re: Get Min Value Where It Is Not Null [message #607957 is a reply to message #607956] Thu, 13 February 2014 22:12 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
Oh no.

Still not correct Sad

BILL_DATE	ACCOUNT_NUM	PAYMENT_DATE	PAYMENT_AMT
19-JAN-2013 12:00:00 AM	1001871324		
19-FEB-2013 12:00:00 AM	1001871324	06-MAR-2013 12:00:00 AM	-300
19-MAR-2013 12:00:00 AM	1001871324		
25-MAR-2013 12:00:00 AM	1001871324	23-APR-2013 03:34:04 PM	-160
25-APR-2013 12:00:00 AM	1001871324	25-APR-2013 07:43:28 AM	-158
25-MAY-2013 12:00:00 AM	1001871324	30-MAY-2013 08:00:18 AM	-160
25-JUN-2013 12:00:00 AM	1001871324	02-JUL-2013 05:17:50 AM	-160
25-JUL-2013 12:00:00 AM	1001871324	14-AUG-2013 03:35:19 PM	-175.4
25-AUG-2013 12:00:00 AM	1001871324	14-SEP-2013 03:56:34 PM	-304
25-SEP-2013 12:00:00 AM	1001871324		
25-OCT-2013 12:00:00 AM	1001871324	12-NOV-2013 01:12:22 PM	-150
25-NOV-2013 12:00:00 AM	1001871324	15-DEC-2013 06:35:49 PM	-154
25-DEC-2013 12:00:00 AM	1001871324	12-JAN-2014 09:03:21 AM	-152

INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('19/01/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1001871324',to_date('', 'dd/mm/rrrr hh:mi:ss'),null);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('19/02/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1001871324',to_date('06/03/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),-300);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('19/03/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1001871324',to_date('', 'dd/mm/rrrr hh:mi:ss'),null);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/03/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1001871324',to_date('23/04/2013 03:34:04', 'dd/mm/rrrr hh:mi:ss'),-160);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/04/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1001871324',to_date('25/04/2013 07:43:28', 'dd/mm/rrrr hh:mi:ss'),-158);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/05/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1001871324',to_date('30/05/2013 08:00:18', 'dd/mm/rrrr hh:mi:ss'),-160);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/06/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1001871324',to_date('02/07/2013 05:17:50', 'dd/mm/rrrr hh:mi:ss'),-160);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/07/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1001871324',to_date('14/08/2013 03:35:19', 'dd/mm/rrrr hh:mi:ss'),-175.4);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/08/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1001871324',to_date('14/09/2013 03:56:34', 'dd/mm/rrrr hh:mi:ss'),-304);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/09/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1001871324',to_date('', 'dd/mm/rrrr hh:mi:ss'),null);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/10/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1001871324',to_date('12/11/2013 01:12:22', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/11/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1001871324',to_date('15/12/2013 06:35:49', 'dd/mm/rrrr hh:mi:ss'),-154);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/12/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1001871324',to_date('12/01/2014 09:03:21', 'dd/mm/rrrr hh:mi:ss'),-152);

would incorrectly yields
BILL_DATE	ACCOUNT_NUM	PAYMENT_DATE	PAYMENT_AMT
19-JAN-2013 12:00:00 AM	1001871324	06-MAR-2013 12:00:00 AM	-300
19-MAR-2013 12:00:00 AM	1001871324	23-APR-2013 03:34:04 PM	-160
25-MAY-2013 12:00:00 AM	1001871324	25-APR-2013 07:43:28 AM	-158
25-JUN-2013 12:00:00 AM	1001871324	30-MAY-2013 08:00:18 AM	-160
25-JUL-2013 12:00:00 AM	1001871324	02-JUL-2013 05:17:50 AM	-160
25-AUG-2013 12:00:00 AM	1001871324	14-AUG-2013 03:35:19 PM	-175.4
25-SEP-2013 12:00:00 AM	1001871324	14-SEP-2013 03:56:34 PM	-304
25-NOV-2013 12:00:00 AM	1001871324	12-NOV-2013 01:12:22 PM	-150
25-DEC-2013 12:00:00 AM	1001871324	15-DEC-2013 06:35:49 PM	-154

Sad
Re: Get Min Value Where It Is Not Null [message #608009 is a reply to message #607957] Fri, 14 February 2014 03:31 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
what is the correct output for the above data?
Re: Get Min Value Where It Is Not Null [message #608016 is a reply to message #608009] Fri, 14 February 2014 04:17 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
cookiemonster wrote on Fri, 14 February 2014 17:31
what is the correct output for the above data?

It should be this
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE             PAYMENT_AMT
19-JAN-2013 12:00:00 AM  1001871324     06-MAR-2013 12:00:00 AM  -300
19-MAR-2013 12:00:00 AM  1001871324     23-APR-2013 03:34:04 PM  -160
25-APR-2013 12:00:00 AM  1001871324     25-APR-2013 07:43:28 AM  -158
25-MAY-2013 12:00:00 AM  1001871324     30-MAY-2013 08:00:18 AM  -160
25-JUN-2013 12:00:00 AM  1001871324     02-JUL-2013 05:17:50 AM  -160
25-JUL-2013 12:00:00 AM  1001871324     14-AUG-2013 03:35:19 PM  -175.4
25-AUG-2013 12:00:00 AM  1001871324     14-SEP-2013 03:56:34 PM  -304
25-SEP-2013 12:00:00 AM  1001871324     12-NOV-2013 01:12:22 PM  -150
25-NOV-2013 12:00:00 AM  1001871324     15-DEC-2013 06:35:49 PM  -154
25-DEC-2013 12:00:00 AM  1001871324     12-JAN-2014 09:03:21 AM  -152


Thank you.
Re: Get Min Value Where It Is Not Null [message #608252 is a reply to message #608016] Tue, 18 February 2014 02:47 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
Ermm... Any help @cookiemonster

Re: Get Min Value Where It Is Not Null [message #608269 is a reply to message #608252] Tue, 18 February 2014 04:31 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I suspect the problem is that the data isn't what you think it is:

SQL> SELECT to_char(bill_date, 'dd-mon-yyyy hh:mi:ss am'),
  2  to_char(payment_date, 'dd-mon-yyyy hh:mi:ss am')
  3  FROM V_TMP_RPT_UNIFI_V2 ORDER BY bill_date;
 
TO_CHAR(BILL_DATE,'DD-MON-YYYY TO_CHAR(PAYMENT_DATE,'DD-MON-Y
------------------------------ ------------------------------
19-jan-2013 12:00:00 PM        
19-feb-2013 12:00:00 PM        06-mar-2013 12:00:00 PM
19-mar-2013 12:00:00 PM        
25-mar-2013 12:00:00 PM        23-apr-2013 03:34:04 AM
25-apr-2013 12:00:00 PM        25-apr-2013 07:43:28 AM
25-may-2013 12:00:00 PM        30-may-2013 08:00:18 AM
25-jun-2013 12:00:00 PM        02-jul-2013 05:17:50 AM
25-jul-2013 12:00:00 PM        14-aug-2013 03:35:19 AM
25-aug-2013 12:00:00 PM        14-sep-2013 03:56:34 AM
25-sep-2013 12:00:00 PM        
25-oct-2013 12:00:00 PM        12-nov-2013 01:12:22 AM
25-nov-2013 12:00:00 PM        15-dec-2013 06:35:49 AM
25-dec-2013 12:00:00 PM        12-jan-2014 09:03:21 AM
 
13 rows selected
 
SQL> 

All those bill_dates are 12PM, which is midday, not midnight.
Which means this:
SQL> SELECT to_char(bill_date, 'dd-mon-yyyy hh:mi:ss am'),
  2  to_char(payment_date, 'dd-mon-yyyy hh:mi:ss am')
  3  FROM V_TMP_RPT_UNIFI_V2
  4  WHERE payment_date < bill_date
  5  ORDER BY bill_date;
 
TO_CHAR(BILL_DATE,'DD-MON-YYYY TO_CHAR(PAYMENT_DATE,'DD-MON-Y
------------------------------ ------------------------------
25-apr-2013 12:00:00 PM        25-apr-2013 07:43:28 AM
 
SQL> 

I believe you wanted that date included, but by your rules it won't be.

Fix your inserts to use 24 hour times and then we'll see where we are.
Re: Get Min Value Where It Is Not Null [message #608357 is a reply to message #608269] Wed, 19 February 2014 01:15 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
Ok, my bad.

I'm really sorry about that.

OK, here's the data:
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('19/01/2013 00:00:00', 'dd/mm/rrrr hh24:mi:ss'),'1001871324',to_date('', 'dd/mm/rrrr hh24:mi:ss'),null);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('19/02/2013 00:00:00', 'dd/mm/rrrr hh24:mi:ss'),'1001871324',to_date('06/03/2013 00:00:00', 'dd/mm/rrrr hh24:mi:ss'),-300);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('19/03/2013 00:00:00', 'dd/mm/rrrr hh24:mi:ss'),'1001871324',to_date('', 'dd/mm/rrrr hh24:mi:ss'),null);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/03/2013 00:00:00', 'dd/mm/rrrr hh24:mi:ss'),'1001871324',to_date('23/04/2013 03:34:04', 'dd/mm/rrrr hh24:mi:ss'),-160);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/04/2013 00:00:00', 'dd/mm/rrrr hh24:mi:ss'),'1001871324',to_date('25/04/2013 07:43:28', 'dd/mm/rrrr hh24:mi:ss'),-158);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/05/2013 00:00:00', 'dd/mm/rrrr hh24:mi:ss'),'1001871324',to_date('30/05/2013 08:00:18', 'dd/mm/rrrr hh24:mi:ss'),-160);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/06/2013 00:00:00', 'dd/mm/rrrr hh24:mi:ss'),'1001871324',to_date('02/07/2013 05:17:50', 'dd/mm/rrrr hh24:mi:ss'),-160);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/07/2013 00:00:00', 'dd/mm/rrrr hh24:mi:ss'),'1001871324',to_date('14/08/2013 03:35:19', 'dd/mm/rrrr hh24:mi:ss'),-175.4);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/08/2013 00:00:00', 'dd/mm/rrrr hh24:mi:ss'),'1001871324',to_date('14/09/2013 03:56:34', 'dd/mm/rrrr hh24:mi:ss'),-304);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/09/2013 00:00:00', 'dd/mm/rrrr hh24:mi:ss'),'1001871324',to_date('', 'dd/mm/rrrr hh24:mi:ss'),null);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/10/2013 00:00:00', 'dd/mm/rrrr hh24:mi:ss'),'1001871324',to_date('12/11/2013 01:12:22', 'dd/mm/rrrr hh24:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/11/2013 00:00:00', 'dd/mm/rrrr hh24:mi:ss'),'1001871324',to_date('15/12/2013 06:35:49', 'dd/mm/rrrr hh24:mi:ss'),-154);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('25/12/2013 00:00:00', 'dd/mm/rrrr hh24:mi:ss'),'1001871324',to_date('12/01/2014 09:03:21', 'dd/mm/rrrr hh24:mi:ss'),-152);


Really need your help.

Thank you very much.
Re: Get Min Value Where It Is Not Null [message #608362 is a reply to message #608357] Wed, 19 February 2014 02:48 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think this is what you need:
SQL> WITH bill_dates AS
  2     (SELECT bill_date,
  3             account_num,
  4             row_number() OVER (PARTITION BY account_num ORDER BY bill_date) bill_order
  5      FROM (SELECT bill_date,
  6                   account_num,
  7                   last_pay
  8            FROM (SELECT bill_date,
  9                         account_num,
 10                         CASE WHEN payment_date >= bill_date THEN payment_date END AS payment_date,
 11                         min(bill_date)
 12                             OVER (PARTITION BY account_num, to_char(bill_date, 'MON-YYYY')) AS min_bill,
 13                         LAG(payment_date,1, SYSDATE)
 14                             OVER (PARTITION BY account_num ORDER BY bill_date) last_pay
 15                  FROM V_TMP_RPT_UNIFI_V2
 16                 )
 17            WHERE bill_date = min_bill
 18           )
 19      WHERE last_pay IS NOT NULL
 20     )
 21  ,
 22       pay_dates AS (SELECT payment_date,
 23                            payment_amt,
 24                            account_num,
 25                            row_number() OVER (PARTITION BY account_num ORDER BY bill_date) payment_order
 26                     FROM V_TMP_RPT_UNIFI_V2 v
 27                     WHERE payment_date IS NOT NULL and payment_date >= bill_date
 28                    )
 29  SELECT b.bill_date, b.account_num, p.payment_date, p.payment_amt
 30  FROM bill_dates b, pay_dates p
 31  WHERE b.account_num = p.account_num
 32  AND b.bill_order = p.payment_order
 33  ORDER BY bill_date;
 
BILL_DATE   ACCOUNT_NUM PAYMENT_DATE PAYMENT_AMT
----------- ----------- ------------ -----------
19-Jan-13   1001871324  06-Mar-13           -300
19-Mar-13   1001871324  23-Apr-13 03        -160
25-Apr-13   1001871324  25-Apr-13 07        -158
25-May-13   1001871324  30-May-13 08        -160
25-Jun-13   1001871324  02-Jul-13 05        -160
25-Jul-13   1001871324  14-Aug-13 03      -175.4
25-Aug-13   1001871324  14-Sep-13 03        -304
25-Sep-13   1001871324  12-Nov-13 01        -150
25-Nov-13   1001871324  15-Dec-13 06        -154
25-Dec-13   1001871324  12-Jan-14 09        -152
 
10 rows selected
 
SQL> 

I just moved the last_pay calculation down a level. It needs to be calculated over all the rows, not just the ones outputted from the bill_date = min_bill check.
Re: Get Min Value Where It Is Not Null [message #608566 is a reply to message #608362] Fri, 21 February 2014 02:21 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
Thanks so much cookie.

You're real helper.

But there's still little missing to cater this data
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE             PAYMENT_AMT
16-JAN-2013 12:00:00 AM  1006977209     23-JAN-2013 03:35:27 PM  -150
16-FEB-2013 12:00:00 AM  1006977209     02-MAR-2013 12:00:00 AM  -150
16-MAR-2013 12:00:00 AM  1006977209     02-APR-2013 07:58:39 PM  -150
16-APR-2013 12:00:00 AM  1006977209     05-MAY-2013 03:30:59 PM  -300
16-MAY-2013 12:00:00 AM  1006977209     31-MAY-2013 07:40:25 AM  -150
16-JUN-2013 12:00:00 AM  1006977209     08-JUL-2013 06:02:11 PM  -150
16-JUL-2013 12:00:00 AM  1006977209          
16-AUG-2013 12:00:00 AM  1006977209     29-AUG-2013 10:50:44 AM  -310
16-SEP-2013 12:00:00 AM  1006977209     04-OCT-2013 10:28:43 AM  -150
16-OCT-2013 12:00:00 AM  1006977209     10-NOV-2013 12:12:19 PM  -150
16-NOV-2013 12:00:00 AM  1006977209     10-DEC-2013 05:58:54 PM  -150
28-NOV-2013 12:00:00 AM  1006977209          
28-DEC-2013 12:00:00 AM  1006977209     30-DEC-2013 12:03:19 PM  -150

INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/01/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('23/01/2013 03:35:27', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/02/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('02/03/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/03/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('02/04/2013 07:58:39', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/04/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('05/05/2013 03:30:59', 'dd/mm/rrrr hh:mi:ss'),-300);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/05/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('31/05/2013 07:40:25', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/06/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('08/07/2013 06:02:11', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/07/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('', 'dd/mm/rrrr hh:mi:ss'),null);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/08/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('29/08/2013 10:50:44', 'dd/mm/rrrr hh:mi:ss'),-310);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/09/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('04/10/2013 10:28:43', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/10/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('10/11/2013 12:12:19', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('16/11/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('10/12/2013 05:58:54', 'dd/mm/rrrr hh:mi:ss'),-150);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('28/11/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('', 'dd/mm/rrrr hh:mi:ss'),null);
INSERT INTO V_TMP_RPT_UNIFI_V2 (BILL_DATE,ACCOUNT_NUM,PAYMENT_DATE,PAYMENT_AMT)
VALUES (to_date('28/12/2013 12:00:00', 'dd/mm/rrrr hh:mi:ss'),'1006977209',to_date('30/12/2013 12:03:19', 'dd/mm/rrrr hh:mi:ss'),-150);



Desired output
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE             PAYMENT_AMT
16-JAN-2013 12:00:00 AM  1006977209     23-JAN-2013 03:35:27 PM  -150
16-FEB-2013 12:00:00 AM  1006977209     02-MAR-2013 12:00:00 AM  -150
16-MAR-2013 12:00:00 AM  1006977209     02-APR-2013 07:58:39 PM  -150
16-APR-2013 12:00:00 AM  1006977209     05-MAY-2013 03:30:59 PM  -300
16-MAY-2013 12:00:00 AM  1006977209     31-MAY-2013 07:40:25 AM  -150
16-JUN-2013 12:00:00 AM  1006977209     08-JUL-2013 06:02:11 PM  -150
16-JUL-2013 12:00:00 AM  1006977209     29-AUG-2013 10:50:44 AM  -310
16-SEP-2013 12:00:00 AM  1006977209     04-OCT-2013 10:28:43 AM  -150
16-OCT-2013 12:00:00 AM  1006977209     10-NOV-2013 12:12:19 PM  -150
16-NOV-2013 12:00:00 AM  1006977209     10-DEC-2013 05:58:54 PM  -150
28-DEC-2013 12:00:00 AM  1006977209     30-DEC-2013 12:03:19 PM  -150

But in this case the last row didn't come out
28-DEC-2013 12:00:00 AM  1006977209     30-DEC-2013 12:03:19 PM  -150


Thank you.

[Updated on: Fri, 21 February 2014 02:22]

Report message to a moderator

Re: Get Min Value Where It Is Not Null [message #608569 is a reply to message #608566] Fri, 21 February 2014 02:36 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Shouldn't the missing row be:
28-NOV-2013 12:00:00 AM  1006977209         30-DEC-2013 12:03:19 PM  -150
Re: Get Min Value Where It Is Not Null [message #608613 is a reply to message #608569] Fri, 21 February 2014 06:46 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
Nope. Because NOV bill is already in
16-NOV-2013 12:00:00 AM  1006977209     10-DEC-2013 05:58:54 PM  -150


Thank you.
Re: Get Min Value Where It Is Not Null [message #608619 is a reply to message #608613] Fri, 21 February 2014 07:12 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
right, so that's a direct knock-on from the previous change.
I'm afraid I'm getting confused myself at this point. We appear to have overlapping rules and I'm losing track of which takes precedence.
It would help a lot of you restated the rules as a simple list. The solution should be more obvious then.
Re: Get Min Value Where It Is Not Null [message #608638 is a reply to message #608619] Fri, 21 February 2014 08:38 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
Thanks for your concern.

OK, the rules:
1. Bill date for each month should only appear once, take the minimum.
2. The payment date should be greater than or equal to the bill date (only need to compare date parts).
3. The payment date is taken in such a way that it is the nearest next payment made as compared to the bill date.
4. The payment date should not be repeated and unique for each bill date.
5. Sample input/output

Input:
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE             PAYMENT_AMT
16-JAN-2013 12:00:00 AM  1006977209     23-JAN-2013 03:35:27 PM  -150
16-FEB-2013 12:00:00 AM  1006977209     02-MAR-2013 12:00:00 AM  -150
16-MAR-2013 12:00:00 AM  1006977209     02-APR-2013 07:58:39 PM  -150
16-APR-2013 12:00:00 AM  1006977209     05-MAY-2013 03:30:59 PM  -300
16-MAY-2013 12:00:00 AM  1006977209     31-MAY-2013 07:40:25 AM  -150
16-JUN-2013 12:00:00 AM  1006977209     08-JUL-2013 06:02:11 PM  -150
16-JUL-2013 12:00:00 AM  1006977209          
16-AUG-2013 12:00:00 AM  1006977209     29-AUG-2013 10:50:44 AM  -310
16-SEP-2013 12:00:00 AM  1006977209     04-OCT-2013 10:28:43 AM  -150
16-OCT-2013 12:00:00 AM  1006977209     10-NOV-2013 12:12:19 PM  -150
16-NOV-2013 12:00:00 AM  1006977209     10-DEC-2013 05:58:54 PM  -150
28-NOV-2013 12:00:00 AM  1006977209          
28-DEC-2013 12:00:00 AM  1006977209     30-DEC-2013 12:03:19 PM  -150
---------------------------------------------------------------------------------------
19-JAN-2013 12:00:00 AM  1001871324          
19-FEB-2013 12:00:00 AM  1001871324     06-MAR-2013 12:00:00 AM  -300
19-MAR-2013 12:00:00 AM  1001871324          
25-MAR-2013 12:00:00 AM  1001871324     23-APR-2013 03:34:04 PM  -160
25-APR-2013 12:00:00 AM  1001871324     25-APR-2013 07:43:28 AM  -158
25-MAY-2013 12:00:00 AM  1001871324     30-MAY-2013 08:00:18 AM  -160
25-JUN-2013 12:00:00 AM  1001871324     02-JUL-2013 05:17:50 AM  -160
25-JUL-2013 12:00:00 AM  1001871324     14-AUG-2013 03:35:19 PM  -175.4
25-AUG-2013 12:00:00 AM  1001871324     14-SEP-2013 03:56:34 PM  -304
25-SEP-2013 12:00:00 AM  1001871324          
25-OCT-2013 12:00:00 AM  1001871324     12-NOV-2013 01:12:22 PM  -150
25-NOV-2013 12:00:00 AM  1001871324     15-DEC-2013 06:35:49 PM  -154
25-DEC-2013 12:00:00 AM  1001871324     12-JAN-2014 09:03:21 AM  -152
---------------------------------------------------------------------------------------
31-JAN-2013 12:00:00 AM  1000071082     29-JAN-2013 06:45:59 PM  -705.1
28-FEB-2013 12:00:00 AM  1000071082          
28-MAR-2013 12:00:00 AM  1000071082          
28-APR-2013 12:00:00 AM  1000071082     15-MAY-2013 12:21:14 PM  -469.95
28-MAY-2013 12:00:00 AM  1000071082          
28-JUN-2013 12:00:00 AM  1000071082          
28-JUL-2013 12:00:00 AM  1000071082     05-AUG-2013 05:07:31 PM  -470.2
28-AUG-2013 12:00:00 AM  1000071082     04-SEP-2013 05:16:57 PM  -470
28-SEP-2013 12:00:00 AM  1000071082          
28-OCT-2013 12:00:00 AM  1000071082     05-NOV-2013 08:26:48 AM  -470
28-NOV-2013 12:00:00 AM  1000071082          
28-DEC-2013 12:00:00 AM  1000071082     20-JAN-2014 03:18:39 PM  -470
---------------------------------------------------------------------------------------
01-JAN-2013 12:00:00 AM  1000085249          
01-FEB-2013 12:00:00 AM  1000085249     04-FEB-2013 04:00:10 PM  -600
01-MAR-2013 12:00:00 AM  1000085249          
01-APR-2013 12:00:00 AM  1000085249          
01-MAY-2013 12:00:00 AM  1000085249     07-MAY-2013 07:16:06 AM  -152.9
01-JUN-2013 12:00:00 AM  1000085249          
01-JUL-2013 12:00:00 AM  1000085249          
01-AUG-2013 12:00:00 AM  1000085249          
01-SEP-2013 12:00:00 AM  1000085249          
01-OCT-2013 12:00:00 AM  1000085249          
01-NOV-2013 12:00:00 AM  1000085249          
01-DEC-2013 12:00:00 AM  1000085249          


Output:
BILL_DATE                ACCOUNT_NUM    PAYMENT_DATE             PAYMENT_AMT
16-JAN-2013 12:00:00 AM  1006977209     23-JAN-2013 03:35:27 PM  -150
16-FEB-2013 12:00:00 AM  1006977209     02-MAR-2013 12:00:00 AM  -150
16-MAR-2013 12:00:00 AM  1006977209     02-APR-2013 07:58:39 PM  -150
16-APR-2013 12:00:00 AM  1006977209     05-MAY-2013 03:30:59 PM  -300
16-MAY-2013 12:00:00 AM  1006977209     31-MAY-2013 07:40:25 AM  -150
16-JUN-2013 12:00:00 AM  1006977209     08-JUL-2013 06:02:11 PM  -150
16-JUL-2013 12:00:00 AM  1006977209     29-AUG-2013 10:50:44 AM  -310
16-SEP-2013 12:00:00 AM  1006977209     04-OCT-2013 10:28:43 AM  -150
16-OCT-2013 12:00:00 AM  1006977209     10-NOV-2013 12:12:19 PM  -150
16-NOV-2013 12:00:00 AM  1006977209     10-DEC-2013 05:58:54 PM  -150
28-DEC-2013 12:00:00 AM  1006977209     30-DEC-2013 12:03:19 PM  -150
---------------------------------------------------------------------------------------
19-JAN-2013 12:00:00 AM  1001871324     06-MAR-2013 12:00:00 AM  -300
19-MAR-2013 12:00:00 AM  1001871324     23-APR-2013 03:34:04 PM  -160
25-APR-2013 12:00:00 AM  1001871324     25-APR-2013 07:43:28 AM  -158
25-MAY-2013 12:00:00 AM  1001871324     30-MAY-2013 08:00:18 AM  -160
25-JUN-2013 12:00:00 AM  1001871324     02-JUL-2013 05:17:50 AM  -160
25-JUL-2013 12:00:00 AM  1001871324     14-AUG-2013 03:35:19 PM  -175.4
25-AUG-2013 12:00:00 AM  1001871324     14-SEP-2013 03:56:34 PM  -304
25-SEP-2013 12:00:00 AM  1001871324     12-NOV-2013 01:12:22 PM  -150
25-NOV-2013 12:00:00 AM  1001871324     15-DEC-2013 06:35:49 PM  -154
25-DEC-2013 12:00:00 AM  1001871324     12-JAN-2014 09:03:21 AM  -152
---------------------------------------------------------------------------------------
31-JAN-2013 12:00:00 AM  1000071082     15-MAY-2013 12:21:14 PM  -469.95
28-MAY-2013 12:00:00 AM  1000071082     05-AUG-2013 05:07:31 PM  -470.2
28-AUG-2013 12:00:00 AM  1000071082     04-SEP-2013 05:16:57 PM  -470
28-SEP-2013 12:00:00 AM  1000071082     05-NOV-2013 08:26:48 AM  -470
28-NOV-2013 12:00:00 AM  1000071082     20-JAN-2014 03:18:39 PM  -470
---------------------------------------------------------------------------------------
01-JAN-2013 12:00:00 AM  1000085249     04-FEB-2013 04:00:10 PM  -600
01-MAR-2013 12:00:00 AM  1000085249     07-MAY-2013 07:16:06 AM  -152.9


Thank you.

[Updated on: Fri, 21 February 2014 08:40]

Report message to a moderator

Re: Get Min Value Where It Is Not Null [message #608727 is a reply to message #608638] Sun, 23 February 2014 21:33 Go to previous messageGo to next message
aimy
Messages: 225
Registered: June 2006
Senior Member
cookiemonster, any help?

Thank you.
Re: Get Min Value Where It Is Not Null [message #608728 is a reply to message #608727] Sun, 23 February 2014 21:47 Go to previous messageGo to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
aimy wrote on Sun, 23 February 2014 19:33
cookiemonster, any help?

Thank you.


he is in bed & asleep at the present.
Nobody owes you any response; especially over a weekend!
Previous Topic: Need help to build xml tag in oracle sql queries
Next Topic: Open Cursor for with an Exception Clause
Goto Forum:
  


Current Time: Thu Mar 28 09:24:47 CDT 2024