Access date between startdate and enddate

[aunixguru]

I cannot figure out how to do the following:

I have table A

patientid
therapistid
dateofservice

B-->A one to many records

I have table B

patientid
therapistid
authorization#
startdate
enddate
#ofunitsauthorized

I need a query that will calculate the # of records from table A that fall within the specified startdate and enddate from table B for the records that match
(patientid=patientid and therapistid=therapistid)

I don't get it. Been trying to figure it out for days.

Any help would be greatly appreciated.

 

[MichaelRed]

Well, my sample data generated as JUNQUE isn't likely to be reasonable. but


PatientId TherapistId DateOfService
261647386 335012146 11/22/84
261647386 335012146 12/16/84
261647386 335012146 1/23/85
261647386 335012146 4/19/85
261647386 335012146 8/21/85
261647386 335012146 1/15/86
261647386 335012146 6/16/86
261647386 335012146 3/13/87
261647386 335012146 12/21/86
261647386 335012146 1/12/87
261647386 335012146 1/15/87
261647386 375012146 11/22/84
261647386 375012146 12/21/84
261647386 375012146 1/23/86
261647386 375012146 3/19/85
261647386 375012146 8/21/86
261647386 385012146 1/23/86
261647386 385012146 6/19/86
261647386 385012146 3/1/87
261647386 115012146 12/5/86
261647386 115012146 2/12/87
261647386 115012146 3/15/87
256747386 115012146 11/22/84
256747386 115012146 12/16/84
256747386 115012146 1/23/85
256747386 115012146 4/19/85
256747386 225012146 8/21/85
256747386 225012146 1/15/86
256747386 225012146 6/16/86
256747386 225012146 3/13/87
256747386 225012146 12/21/86
431647386 225012146 1/12/87
431647386 445012146 1/15/87
431647386 445012146 11/22/84
431647386 445012146 12/21/84
431647386 445012146 1/23/86
431647386 445012146 3/19/85
431647386 445012146 8/21/86
431647386 385012146 1/23/86
431647386 385012146 6/19/86
431647386 385012146 3/1/87
431647386 385012146 12/5/86
431647386 385012146 2/12/87
431647386 385012146 3/15/87


PatientId TherapistId AuthNum StartDate EndDate NumAuth
261647386 335012146 123456 11/19/84 3/1/87 12
256747386 335012146 123457 12/1/84 2/1/87 10
431647386 335012146 123458 1/15/85 1/1/87 14
261647386 115012146 123459 1/1/85 8/1/86 5
256747386 115012146 123460 2/1/88 2/1/89 23
431647386 115012146 123461 1/1/84 1/1/85 7
261647386 225012146 123462 10/15/84 10/15/86 15
256747386 225012146 123463 10/1/85 10/1/86 6
431647386 225012146 123464 11/1/84 5/1/85 18
261647386 445012146 123465 5/1/85 11/1/85 9
256747386 445012146 123466 11/1/85 11/1/85 21
431647386 445012146 123467 11/1/84 12/31/84 13
261647386 385012146 132468 1/1/85 12/31/85 14
256747386 385012146 123469 11/1/84 12/31/85 48
431647386 385012146 123470 12/1/84 12/1/85 31


Results

PatientId Num
256747386 2
261647386 10
431647386 2


The Query
SELECT tblSrvcDates.PatientId, Count(tblSvcAuth.PatientId) AS Num
FROM tblSvcAuth LEFT JOIN tblSrvcDates ON (tblSvcAuth.TherapistId = tblSrvcDates.TherapistId) AND (tblSvcAuth.PatientId = tblSrvcDates.PatientId)
WHERE (((tblSvcAuth.StartDate)<=[DateOfService]) AND ((tblSvcAuth.EndDate)>=[DateOfService]))
GROUP BY tblSrvcDates.PatientId;




MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over