How to optimize this query

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