I created the following query which works fine. I am trying to figure out how to optimize this query where I don't have to create a temp table and another join to pull the data I am trying to retrieve. Please provide same examples.
Thank you
create table #temp( **
** PatientID int,
** CollectionDateTime Datetime**
** )**
** use CLGMMCMARTPROD**
declare @NumOfYears int
insert #temp
select B.PatientId, min(a.CollectionDateTime) from dbo.Labs a
** inner join Patients b**
** on a.PatientID = b.PatientID**
** where a.LabtestID = 2 and a.CollectionDateTime >= '06/01/02' and a.CollectionDateTime < '06/01/03' and a.NumericLabResult >= 9.5 **
** and (CAST(CONVERT(char(8),a.CollectionDateTime, 112) AS int) - CAST(CONVERT(char(8), DOB, 112) AS int)) / 10000 >= 21 and (CAST(CONVERT(char(8),a.CollectionDateTime, 112) AS int) - CAST(CONVERT(char(8), DOB, 112) AS int)) / 10000 < 65 -- and min(a.CollectionDateTime)**
** group by b.PatientID**
** order by b.PatientID**
** select distinct a.PatientID from dbo.Labs a**
** inner join #temp b**
** on a.PatientID = b.PatientID **
** where a.LabtestID = 2 and a.CollectionDateTime >= 180 + b.CollectionDateTime and a.CollectionDateTime <= 365 + b.CollectionDateTime **
** order by a.PatientID**
I'm seeing stars....literally!
How much of this query are you actually running?
I tried to format the t-sql and it added stars on every line. Please ignore all the stars.
Thank you.
The automatic "guess the format" tool here gets in the way most of the time. If you can re-edit your post and do the following it should format nicely:
```sql
... your code here ...
```
I think this get close to what you want:
select a.patientid
from patients as p
inner join dbo.labs as l1
on l1.patientid=p.patientid
and l1.labtestid=2
and l1.numericlabresult>=9.5
and l1.collectiondatetime>=cast('2006-02-01' as datetime)
and l1.collectiondatetime< cast('2006-03-01' as datetime)
and datediff(day,p.dob,l1.collectiondatetime)/365.25>=21.0
and datediff(day,p.dob,l1.collectiondatetime)/365.25< 65.0
where exists (select 1
from dbo.labs as l2
where l2.patientid=l1.patientid
and l2.labtestid=l1.labtestid
and l2.collectiondatetime>=dateadd(day,datediff(day,365,l1.collectiondatetime),0)
and l2.collectiondatetime< dateadd(day,datediff(day,182,l1.collectiondatetime),0)
)
group by p.patientid
order by p.patientid
;
select distinct a.PatientID
from dbo.Labs a
inner join (
select a.PatientId, min(a.CollectionDateTime) AS CollectionDateTime
from dbo.Labs a
inner join Patients b
on a.PatientID = b.PatientID
where a.LabtestID = 2
and a.CollectionDateTime >= '06/01/02'
and a.CollectionDateTime < '06/01/03'
and a.NumericLabResult >= 9.5
and YEAR(a.CollectionDateTime) - YEAR(b.DOB) >= 21
and YEAR(a.CollectionDateTime) - YEAR(b.DOB) < 65
group by b.PatientID
) as b on b.PatientID = a.PatientID
and a.CollectionDateTime >= 180 + b.CollectionDateTime
and a.CollectionDateTime <= 365 + b.CollectionDateTime
where a.LabtestID = 2
order by a.PatientID