help in using query in SQL Server2000

[R1972]

Hi,

I'm new to sqlserver.

The following query using in oracle db. I would like to convert the query according to SQL Server2000 DB. Any help would be appreciated.

Query :
--------
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) COUNT
FROM tbill a


Thanks in advance.

Raj

 

[bertrandkis]

Could you may be tell us what the query is supposed to return?

 

[R1972]

eg:-

Sample data

tbilldate.dt_bill
01/03/04
02/03/04
....
...
12/03/04

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


The 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


This the output using that query in Oracle. I couldn't transfer the outerjoin in sql server of that query.


One more requirement with that query -

i.e. the output actually look like
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

I hope you can understand that the above scenario. Can you pl. kindly help me on the above request ?

Thanks,
Raj