This discussion is archived
2 Replies Latest reply: Nov 21, 2012 12:41 AM by BluShadow RSS

how to get the count of consecutive Records in a table

622930 Newbie
Currently Being Moderated
Hi,
create table ST_TI_SCHEDULE (iloan_code number, inst_num number, inst_amt number,inst_due_Date date, paid_flag  char(1 byte), payment_date date)

insert into ST_TI_SCHEDULE VALUES  VALUES (101,1,100,to_Date('01-jan-2012'),'Y',to_date('05-jan-2012'));
insert into ST_TI_SCHEDULE VALUES(101,2,100,to_Date('01-feb-2012'),'Y',to_date('03-feb-2012'));
insert into ST_TI_SCHEDULE VALUES(101,3,100,to_Date('01-mar-2012'),'Y',to_date('02-mar-2012'));

insert into ST_TI_SCHEDULE VALUES(102,1,100,to_Date('01-jan-2012'),'Y',to_date('05-jan-2012'));
insert into ST_TI_SCHEDULE VALUES(102,2,100,to_Date('01-feb-2012'),'Y',to_date('01-feb-2012'));
insert into ST_TI_SCHEDULE VALUES(102,2,100,to_Date('05-feb-2012'),'Y',to_date('04-feb-2012'));
insert into ST_TI_SCHEDULE VALUES(102,3,100,to_Date('01-mar-2012'),'Y',to_date('02-mar-2012'));


insert into ST_TI_SCHEDULE VALUES(103,1,100,to_Date('01-jan-2012'),'Y',to_date('04-jan-2012'));
insert into ST_TI_SCHEDULE VALUES(103,1,100,to_Date('03-jan-2012'),'Y',to_date('05-jan-2012'));
insert into ST_TI_SCHEDULE VALUES(103,2,100,to_Date('01-feb-2012'),'N',NULL);
insert into ST_TI_SCHEDULE VALUES(103,3,100,to_Date('01-mar-2012'),'N',to_date('02-mar-2012'));

insert into ST_TI_SCHEDULE VALUES(104,1,100,to_Date('01-APR-2012'),'Y',to_date('05-APR-2012'));
insert into ST_TI_SCHEDULE VALUES(104,2,100,to_Date('01-MAY-2012'),'N',TO_DATE('01-MAY-2012'));
insert into ST_TI_SCHEDULE VALUES(104,2,100,to_Date('15-JUN-2012'),'N',NULL);
insert into ST_TI_SCHEDULE VALUES(104,3,100,to_Date('01-JULY-2012'),'Y',to_date('02-JUL-2012'));
NOw I WOULD LIKE TO SELECT ILOAN_CODES THAT ARE CONSECUTIVELY OR SEQUENTIALLY placed AND PAYMENT_DATE MUST BE GREATER THAN INST_DUE_dATE AND THE COUNT SHOULD BE GREATER THAN OR EQUAL TO 2.
SELECT COUNT(*) FROM ST_TI_sCHEDULE WHERE ILOAN_CODE =  101 AND TRUNC(PAYMENT_DATE) > TRUNC(INST_DUE_dATE) AND PAID_FLAG = 'Y'.
THE COUNT SHOULD BE OF CONSECUTIVE RECORDS AND HENCE 101 SHALL BE THE CORRECT ILOAN_CODE AND THE QUERY SHOuld not give any result for iloan_code =102

Please suggest me how to get the count of the consecutive records through sql statement.

coming to iloan_codes 103 and 104......

I need to get that iloan_code whose payment_date is greater than inst_due_date and paid_flag= 'Y'
and the next record should be such that payment_date is null and paid_flag = 'N ' and the count can be grater than or equal to 2

I have tried like this......
SELECT COUNT(*) FROM ST_TI_sCHEDULE WHERE ILOAN_CODE =  103 AND TRUNC(PAYMENT_DATE) > TRUNC(INST_DUE_dATE) AND PAID_FLAG = 'Y'
and exists (select 1 from st_ti_Schedule b where b.iloan_code = a.iloan_code and b.paid_flag= 'N' and bb.payment_date is null and  b.inst_num > a.inst_num);
Here also we need the count of consecutive records and as such iloan_code 103 shall pass the requirement and iloan_code will be matching the requirement and iloan_code 104 should not give any result.

Please advise.....
  • 1. Re: how to get the count of consecutive Records in a table
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Thanks for posting the CREATE TABLE and INSERT statements. Don't forget to post the exact results you want from that sample data.
    If you want to pass parameters (such as an iloan_code) to the query, then post a few sets of parameters, and the results you want from the sample data for each set.

    Remember, the poeple who want to help you aren't as familiar with the application as you are. Define any special terms you use. For example:
    sri wrote:
    NOw I WOULD LIKE TO SELECT ILOAN_CODES THAT ARE CONSECUTIVELY OR SEQUENTIALLY placed AND PAYMENT_DATE MUST BE GREATER THAN INST_DUE_dATE AND THE COUNT SHOULD BE GREATER THAN OR EQUAL TO 2.
    What does it mean for an iloan_code to be consecutively placed? Which of the 4 iloan_codes in the given sample data are consecutively placed, and which ones are not?
    coming to iloan_codes 103 and 104......
    Is this a separate question? Post the exact results you want.
    I have tried like this......
    SELECT COUNT(*) FROM ST_TI_sCHEDULE WHERE ILOAN_CODE =  103 AND TRUNC(PAYMENT_DATE) > TRUNC(INST_DUE_dATE) AND PAID_FLAG = 'Y'
    and exists (select 1 from st_ti_Schedule b where b.iloan_code = a.iloan_code and b.paid_flag= 'N' and bb.payment_date is null and  b.inst_num > a.inst_num);
    Thanks for posting your code; that can be very helpful.
    Table aliases a and bb aren't defined anywhere. Perhaps this is what you wanted:
    SELECT    COUNT (*) 
    FROM       st_ti_schedule     a          -- Alias a defined here
    WHERE       iloan_code          =  103 
    AND       TRUNC (payment_date)      > inst_due_date     -- No need to TRUNCate both 
    AND       paid_flag          = 'Y'
    AND       EXISTS (
                     SELECT  1 
                   FROM    st_ti_schedule b 
                   WHERE   b.iloan_code      = a.iloan_code 
                   AND     b.paid_flag       = 'N' 
                   AND     b.payment_date   IS NULL     -- not bb.payment_date
                   AND     b.inst_num            > a.inst_num
               );
  • 2. Re: how to get the count of consecutive Records in a table
    BluShadow Guru Moderator
    Currently Being Moderated
    As Frank says, it's not clear what the output is you are wanting, and the logic seems to vary with different conditions....

    As a starting point take a look at the following:
    SQL> ed
    Wrote file afiedt.buf
    
      1  with x as (select iloan_code
      2                   ,inst_num
      3                   ,case when row_number() over (partition by iloan_code order by inst_num) != inst_num then 'ERR'
      4                    else 'OK'
      5                    end as chk
      6             from st_ti_schedule
      7            )
      8  select iloan_code, count(*) as cnt
      9  from   st_ti_schedule
     10  where  iloan_code not in (select iloan_code from x where chk = 'ERR')
     11  group by iloan_code
     12* order by 1
    SQL> /
    
    ILOAN_CODE        CNT
    ---------- ----------
           101          3
    This is generating a row number within each iloan_code based on the order of the inst_num and (assuming inst_num starts at 1 for each iloan_code) if that row number does not equal the inst_num then there is an issue with the sequence of consecutive records within that iloan_code group. That information can then be used to query the iloan_code's that don't have an issue and do a count for those.

    Maybe that will give you an idea how you can go about tackling your issue.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points