select 75 records for each field 1

[scottshiv]

I have a doctor table with doctor and patient ID's. I want to select 75 patients for each doctor whose insurance date is currently still active. How would I do that?

Doctor Table
Doctor ID
Patient ID
insurance begin date
insurance end date

 

[Golom]

Select DoctorID, PatientID, BeginDate, EndDate

From DoctorTable D1

Where PatientID IN

          (Select TOP 75 PatientID From DoctorTable D2
           Where D2.DoctorID = D1.DoctorID
             AND BeginDate <= Date() AND EndDate >= Date())

Order by DoctorID, PatientID

 

[scottshiv]

PERFECT!

Thank you