SQLTeam.com | Weblogs | Forums

Help! I need to return records for appointments that occurred only from the past 30 days

#1

I'm working on code to return patients who have had abnormal labs that haven't yet been reviewed by a doctor. It runs fine! However, I need to limit the results to appointments that have occurred in the past 30 days (}ap.start). I've been through a number of forums and tried numerous ways of trying to do it, but haven't been successful. I can't seem to make DATEADD work with my code. Can somebody please help me? My code is below.

SELECT

_ lo.location_name AS Centre,_
_ DATE_FORMAT(_
_ ap.start,_
_ "%D/%M%Y") AS appointment_date,_
_ ind.individual_id AS IndividualID,_
_ CONCAT_ws(_
_ ' ',_
_ dr.title,_
_ dr.forename,_
_ dr.surname) AS doctor_name,_
_ ap.appointment_id AS AppointmentID,_
_ os.service_name as Service,_
_ ob.observation_value as Result,_
_ ob.reference_range As Reference_

FROM
_ observation_order AS oo_

INNER JOIN
_ appointment ap_
_ ON oo.appointment_id = ap.appointment_id_

INNER JOIN
individual dr
_ ON ap.primary_doctor_id = dr.individual_id_

INNER JOIN
_ individual ind_
_ ON ap.patient_id = ind.individual_id _

INNER JOIN
_ observation_set os_
_ ON oo.observation_order_id = os.observation_order_id_

INNER JOIN _
_ location lo

_ ON ap.location_id = lo.location_id_

INNER JOIN
_ observation ob_
_ ON os.observation_set_id = ob.observation_set_id_

WHERE

_ (ob.abnormal_flag = 'H' OR ob.abnormal_flag = 'L' AND ob.reviewed IS NULL)_
_ AND (ap.location_id IN LocationIDs_
_ OR ap.location_id IS NULL)_

ORDER BY ap.start DESC

0 Likes

#2
create table #lab(customerid int , startdate datetime)

insert into #lab
select 1 , DATEADD (d,1, getdate()) union
select 1 , DATEADD (d,2, getdate()) union
select 1 , DATEADD (d,3, getdate()) union
select 1 , DATEADD (d,4, getdate()) union
select 1 , DATEADD (d,5, getdate()) union
select 1 , DATEADD (d,6, getdate()) union
select 1 , DATEADD (d,7, getdate()) union
select 1 , DATEADD (d,8, getdate()) union
select 1 , DATEADD (d,9, getdate()) union
select 1 , DATEADD (d,10, getdate()) union
select 1 , DATEADD (d,11, getdate()) union
select 1 , DATEADD (d,12, getdate()) union
select 1 , DATEADD (d,-30, getdate()) union
select 1 , DATEADD (d,-31, getdate()) union
select 1 , DATEADD (d,-29, getdate()) union
select 1 , DATEADD (d,-28, getdate()) union
select 1 , DATEADD (d,-27, getdate()) union
select 1 , DATEADD (d,-26, getdate()) union
select 1 , DATEADD (d,-25, getdate()) 

select * 
  From #lab
--where startdate >= DATEADD (d,-30, getdate()) and startdate <= getdate()
 where startdate between DATEADD (d,-30, getdate()) and getdate() 

drop table #lab
0 Likes