Hi,
Sample data
Table - tbilldate
tbilldate.dt_bill
01/03/04
02/03/04
....
...
12/03/04
table - tbill
tbill.nmi tbill.dt_bill
1 01/03/04
1 02/03/04
1 05/03/04
1 06/03/04
2 03/03/04
2 .....
2 6/03/04
2 08/03/04
2 10/03/04
3 05/03/04
3 07/03/04
3 09/03/04
3 12/03/04
3 15/03/04
select distinct a.nmi,
(
select count(*)
from tbill, tbilldate
where tbill.dt_bill(+)=tbilldate.dt_bill
and nmi(+) = a.nmi
and tbilldate.dt_bill between (select min(dt_bill) from tbill where nmi = a.nmi) and
(select max(dt_bill) from tbill where nmi = a.nmi)
and nmi is null --and exists (select 1 from tbilldate n where n.dt_bill = tbill.dt_bill)
) count
from
tbill a;
The above query displays each nmi with missing total dt_bill of each nmi min(dt_bill) and max(dt_bill)
The query output will be like that ;-
nmi count
1 2
2 4
3 6
I need the output actually look like following scenario-
nmi count
1 1
2 3
3 4
Actually it counts the no. of breaks between the dates.
eg.
the nmi - 3 , 1 break between 05/03 and 07/03 and 2nd break between 07 and 09 and 3rd between 09 and 12 and 4th between 12 and 15. So, the total is 4
Can you pl. kindly help me on the above request ?
Thanks,
Raj
Sample data
Table - tbilldate
tbilldate.dt_bill
01/03/04
02/03/04
....
...
12/03/04
table - tbill
tbill.nmi tbill.dt_bill
1 01/03/04
1 02/03/04
1 05/03/04
1 06/03/04
2 03/03/04
2 .....
2 6/03/04
2 08/03/04
2 10/03/04
3 05/03/04
3 07/03/04
3 09/03/04
3 12/03/04
3 15/03/04
select distinct a.nmi,
(
select count(*)
from tbill, tbilldate
where tbill.dt_bill(+)=tbilldate.dt_bill
and nmi(+) = a.nmi
and tbilldate.dt_bill between (select min(dt_bill) from tbill where nmi = a.nmi) and
(select max(dt_bill) from tbill where nmi = a.nmi)
and nmi is null --and exists (select 1 from tbilldate n where n.dt_bill = tbill.dt_bill)
) count
from
tbill a;
The above query displays each nmi with missing total dt_bill of each nmi min(dt_bill) and max(dt_bill)
The query output will be like that ;-
nmi count
1 2
2 4
3 6
I need the output actually look like following scenario-
nmi count
1 1
2 3
3 4
Actually it counts the no. of breaks between the dates.
eg.
the nmi - 3 , 1 break between 05/03 and 07/03 and 2nd break between 07 and 09 and 3rd between 09 and 12 and 4th between 12 and 15. So, the total is 4
Can you pl. kindly help me on the above request ?
Thanks,
Raj