Query to count patient with 2 or more visits within 90 days of first service

[mchambers]

Hello,

I am trying to write a query to pull patients that have received a service within 90 days of their first services. I have tried running the below code and some other variations and have not been able to get the count. Any help would be great.

select bth.PatID,
from billing_table bth
where bth.date_of_service BETWEEN '2018-10-01' AND '2019-09-30'
and bth.primary_guarantor IN ('422')
and bth.PatID in
(
Select
count(bth.date_of_service)
from billing_table bth
where bth.date_of_service > Dateadd(day,-90, date_of_service)

and bth.PATID is not null
Group by bth.PATID
having count(bth.date_of_service) >1
)
order by bth.PATID

 

[fredericofonseca]

select first_time.PatID
from (select bth.PatID, min( bth.date_of_service) as first_service_date
      from billing_table bth
      where bth.primary_guarantor IN ('422')
      group by bth.PatID
      having min ( bth.date_of_service BETWEEN '2018-10-01' AND '2019-09-30'
     ) first_time
outer apply (select top 1 *
             from billing_table bth2
             where bth2.PatID = bth.PatID
             and bth2.date_of_service > bth.date_of_service
             and bth2.date_of_service < dateadd(day, 90, bth.date_of_service) 
            ) next_one
where next_one.PatID is not null

Now based on your scarse info the above will work
Explanation

Group BY - for each patient determine the oldest date_of_service - this is the first time the patient was serviced
Having - filter for patients that had their first time between the supplied dates (this may not be what you wanted)

outer apply - for each record found determine if there is another record for same patient with a date of service higher than the first date of service but within 90 days of said date

where --- filter where a service within 90 days was found


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[mchambers]

Hello,

Thank you for the reply. The error I receive after running the updated code:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "bth.PatID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "bth.date_of_service" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "bth.date_of_service" could not be bound.

 

[dhookom]

I'm not an SQL guru but I would try replace the last three "bth" with "first_time"

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[mchambers]

That worked! Thank you both! Have a wonderful day.

 

[johnherman]

How about giving them each a star for helping you do your job?

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity