SQLTeam.com | Weblogs | Forums

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

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

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