Home » SQL & PL/SQL » SQL & PL/SQL » Get Min Value Where It Is Not Null
Get Min Value Where It Is Not Null [message #607673] |
Mon, 10 February 2014 21:24 |
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 #607691 is a reply to message #607674] |
Tue, 11 February 2014 02:09 |
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 #607716 is a reply to message #607715] |
Tue, 11 February 2014 05:38 |
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 |
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 #607780 is a reply to message #607748] |
Tue, 11 February 2014 20:42 |
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 #607816 is a reply to message #607780] |
Wed, 12 February 2014 04:42 |
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 |
aimy
Messages: 225 Registered: June 2006
|
Senior Member |
|
|
pablolee wrote on Wed, 12 February 2014 18:42You 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 |
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 |
aimy
Messages: 225 Registered: June 2006
|
Senior Member |
|
|
pablolee wrote on Wed, 12 February 2014 23:54Quote: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 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
pablolee wrote on Wed, 12 February 2014 15:54Quote: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 |
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 |
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 |
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 |
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 |
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 #607954 is a reply to message #607934] |
Thu, 13 February 2014 20:08 |
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 |
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
*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 |
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 |
aimy
Messages: 225 Registered: June 2006
|
Senior Member |
|
|
Oh no.
Still not correct
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
|
|
|
|
Re: Get Min Value Where It Is Not Null [message #608016 is a reply to message #608009] |
Fri, 14 February 2014 04:17 |
aimy
Messages: 225 Registered: June 2006
|
Senior Member |
|
|
cookiemonster wrote on Fri, 14 February 2014 17:31what 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 #608269 is a reply to message #608252] |
Tue, 18 February 2014 04:31 |
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 |
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 |
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 |
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 #608619 is a reply to message #608613] |
Fri, 21 February 2014 07:12 |
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 |
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
|
|
|
|
|
Goto Forum:
Current Time: Thu Mar 28 09:24:47 CDT 2024
|