sql server 2008 - Compare SQL table to itself to find missing timesheet dates -

the sql query below works takes forever respond because of sub-queries being used. i'm hoping can spot way me optimize query.

select     a.userid, a.date, a.[total hrs], a.[load date],                   c.[first name], c.[last name]          dbo.timesheetreport                    inner join                   candidates c on a.userid = c.userid  , a.date >= c.[confirmed start date]  , (a.date <= c.[planned end date] or c.[planned end date] null) a.[load date] = @ddlenddate  , a.date not in  (select b.date dbo.timesheetreport b  b.[load date] = @ddlstartdate , a.userid = b.userid) , a.date < (select max(date) dbo.timesheetreport c  c.[load date] = @ddlstartdate) 

each subset of data created based on when data loaded (i.e. load date). looks days current load (@ddlenddate) missing previous load (@ddlstartdate).

i tried using left outer join on itself, 0 records returned , know exist.

any ideas?

you may want use e common table expression (see link more information: https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx) or temp table first sub-query.

the second 1 can stored in variable , not need defined subquery (so executed once)

declare @madate datetime  select @madate = max([date])  dbo.timesheetreport c  c.[load date] = @ddlstartdate  select     a.userid, a.date, a.[total hrs], a.[load date],                   c.[first name], c.[last name]          dbo.timesheetreport   inner join   candidates c on a.userid = c.userid          , a.date >= c.[confirmed start date]          , (a.date <= c.[planned end date] or c.[planned end date] null) a.[load date] = @ddlenddate      , a.date not in          (select b.date dbo.timesheetreport b           b.[load date] = @ddlstartdate , a.userid = b.userid)     , a.date < @madate