Skip to Main Content
  • Questions
  • Find number and date which break the rule

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, kamrul hasan.

Asked: May 30, 2017 - 4:47 am UTC

Last updated: June 01, 2017 - 10:24 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Dear Chris,

I have table like this

Account_number        Opening_date

1234                  16/05/2017
1235                  16/05/2017
1236                  23/04/2017
1237                  25/04/2017
1238                  25/04/2017
1239                  26/05/2017
1240                  11/05/2017


Here account number is generated with a opening date. the rule is when you open an account then the account number and account open date should be greater than the previous account number and date. But the sequence has broken some how. so i need a sql query that find the account number and date that break the rule.

For example i need 1236,1237,1238,1240 account number and open date by the query output as these account break the rule. I have thousands of account number and open date in the table. So i need to find those accounts and their opening date where there rule has been broken.

I have used a query given by you. The query is:

WITH RAW_DATA AS (
 SELECT  * FROM ACC3 
), PREV_DATA AS (
 SELECT R.*, LAG(ACCOUNT_OPENDATE,1) OVER (ORDER BY ACCOUNT_NUMBER) PREV_DATE
 FROM   RAW_DATA R
)
 SELECT * FROM PREV_DATA
 WHERE  ACCOUNT_OPENDATE < PREV_DATE;


But the problem is this query only return where the mismatch began. This query only give -->> 1236 23/04/2017 .

But i need to find the other account also. Please help me.


and Chris said...

So you want to find all the rows after the first that breaks the sequence?

If so, you can have a case expression to check when the current date is less than the previous ordered by id. Then return the min of these.

Finally return all rows where the current id is >= the value calculated above:

with rws as (
  select 1 id, date'2015-01-01' dt from dual union all 
  select 2 id, date'2017-01-01' dt from dual union all 
  select 3 id, date'2016-01-01' dt from dual union all 
  select 4 id, date'2018-01-01' dt from dual 
), prev as (
  select r.*, lag(dt) over (order by id) prev_dt
  from   rws r
), diff as (
  select p.*, min(case when dt < prev_dt then id end) over () diff_id from prev p
)
  select * from diff
  where  id >= diff_id;

ID  DT                    PREV_DT               DIFF_ID  
3   01-JAN-2016 00:00:00  01-JAN-2017 00:00:00  3        
4   01-JAN-2018 00:00:00  01-JAN-2016 00:00:00  3      

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

using Analytics with Window Clause

Rajeshwaran, Jeyabal, May 31, 2017 - 11:05 am UTC

....
the rule is when you open an account then the account number and account open date should be greater than the previous account number and date. But the sequence has broken some how. so i need a sql query that find the account number and date that break the rule.
....


drop table t purge;
create table t(acc_no int,open_dt date);
insert into t values( 1234 , to_date('16/05/2017','dd/mm/yyyy') );
insert into t values( 1235 , to_date('16/05/2017','dd/mm/yyyy') );
insert into t values( 1236 , to_date('23/04/2017','dd/mm/yyyy') );
insert into t values( 1237 , to_date('25/04/2017','dd/mm/yyyy') );
insert into t values( 1238 , to_date('25/04/2017','dd/mm/yyyy') );
insert into t values( 1239 , to_date('26/05/2017','dd/mm/yyyy') );
insert into t values( 1240 , to_date('11/05/2017','dd/mm/yyyy') );
commit;


demo@ORA11G> select acc_no,open_dt
  2  from  (
  3  select t.* ,
  4         max(open_dt) over( order by acc_no,open_dt
  5                rows between unbounded preceding and 1 preceding ) as max_dt,
  6         max(acc_no) over( order by acc_no,open_dt
  7            rows between unbounded preceding and 1 preceding ) as max_acc_no
  8  from t
  9        )
 10  where open_dt < max_dt or
 11        acc_no < max_acc_no
 12  /

    ACC_NO OPEN_DT
---------- -----------
      1236 23-APR-2017
      1237 25-APR-2017
      1238 25-APR-2017
      1240 11-MAY-2017

demo@ORA11G>

kamrul hasan moin, June 01, 2017 - 4:34 am UTC

dear chris, your query return
ID DT PREV_DT DIFF_ID
4 01-JAN-2018 00:00:00 01-JAN-2016 00:00:00 3

But this sequence is right as the account number and open date is in right sequence compare to the first row of ID no 1. so the query should not return the ID 4. Now what change i should do to find the right result as i describe with example in my question.


Chris Saxon
June 01, 2017 - 10:24 am UTC

I'm not clear exactly what you expect to see. It seems to me Rajesh's answer above will get what you're looking for.

Precisely what output should you get from your query? And with which data?

With Chris data set in place

Rajeshwaran, Jeyabal, June 01, 2017 - 6:12 am UTC

Did you tried with my above sql?

demo@ORA11G> select 1 acc_no, date'2015-01-01' open_dt from dual union all
  2  select 2 acc_no, date'2017-01-01' open_dt from dual union all
  3  select 3 acc_no, date'2016-01-01' open_dt from dual union all
  4  select 4 acc_no, date'2018-01-01' open_dt from dual;

    ACC_NO OPEN_DT
---------- -----------
         1 01-JAN-2015
         2 01-JAN-2017
         3 01-JAN-2016
         4 01-JAN-2018


so given this rule with the above data, the only violating record is acc_no=3.
....
. the rule is when you open an account then the account number and account open date should be greater than the previous account number and date. But the sequence has broken some how. so i need a sql query that find the account number and date that break the rule
....


so running this SQL, return this data (only acc_no=3).

demo@ORA11G> select acc_no,open_dt
  2  from  (
  3  select t.* ,
  4         max(open_dt) over( order by acc_no,open_dt
  5                rows between unbounded preceding and 1 preceding ) as max_dt,
  6         max(acc_no) over( order by acc_no,open_dt
  7            rows between unbounded preceding and 1 preceding ) as max_acc_no
  8  from ( select 1 acc_no, date'2015-01-01' open_dt from dual union all
  9             select 2 acc_no, date'2017-01-01' open_dt from dual union all
 10             select 3 acc_no, date'2016-01-01' open_dt from dual union all
 11             select 4 acc_no, date'2018-01-01' open_dt from dual ) t
 12        )
 13  where open_dt < max_dt or
 14        acc_no < max_acc_no
 15  /

    ACC_NO OPEN_DT
---------- -----------
         3 01-JAN-2016

demo@ORA11G>

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.