Help in query 1

[R1972]

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

 

[djbjr]

I figured out a pretty simple way to do this

Try this:

SELECT nmi,
SUM(CASE WHEN dt_rank + 1 <> next_dt THEN 1 ELSE 0 END)
FROM
(SELECT tbill.nmi,
tbilldate.dt_rank
LEAD(tbilldate.dt_rank)
OVER (PARTITION BY tbill.nmi
ORDER BY tbill.nmi, tbilldate.dt_rank ) next_dt
FROM
tbill,
(SELECT dt_bill,
RANK() OVER (ORDER BY dt_bill) AS dt_rank,
FROM tbilldate) tbilldate
where tbill.dt_bill(+)=tbilldate.dt_bill)
GROUP BY nmi

------
I tested this with some sample data on my end and it worked but I changed the names for you so sorry if there is a typo.

Let me know how it works

 

[R1972]

Hi djbr,

yeah...It's wonderful...Its working....

Can you please explain how it works bcoz I'm not aware of Rank and lead function ?

Thanks,
Raj

 

[dbtoo2001]

He is using analytic functions:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/function.htm#81409