SQL Server return result for row when no entry on joined table where clause

Question

I've got an issue where I'm trying to pull timesheets from periods for a person. If they have an entry, I get a row with their name and timesheet status. But if they have not submitted either of the two timesheets in a period, they don't show up at all in the query (meaning they'd be missed when someone goes to followup with them).

Here's a simplified example query that returns nothing as the resource_id has no data in period_end_date greater than the where clause limits:

SELECT time_status_id, last_name
FROM dbo.wh_resource
LEFT JOIN wh_time_report ON wh_time_report.creator_resource_id = wh_resource.resource_id
WHERE wh_resource.resource_id = '31100670'
AND period_end_date >= '2017-01-29'
ORDER BY last_name

I found that making it (period_end_date >= '2017-01-29' OR period_end_date IS NULL) won't work, as they have entered data in the past (if they never had, it'd pull them up).

Is there a way to put a simple 0 if there was no data found due to the period_end_date? I was looking at CASE WHERE but didn't succeed at getting anywhere.

I'd much rather get a time_status_id of 0 and the person's name if they don't have an entry newer than what's searched for. Is that possible fairly simply?

I was looking to simplify the calls, but worst comes to worst I could pull all last names, then pull all timesheet statuses, and then find any entries that aren't there. The following does this work great (but missing people who didn't submit either timesheet)

SELECT wh_resource.resource_id, last_name, first_name, narrative_full_name, department_name,
ISNULL(MAX(CASE WHEN wh_time_report.period_end_date = '2017-02-04' THEN time_status_id END),0) as timesheet_status1,
SUM(CASE WHEN date_worked >= '2017-01-29' AND date_worked <= '2017-02-04' THEN ROUND(wh_time_subitem.hours_worked,2) END) as hours_total1,
SUM(CASE WHEN date_worked >= '2017-01-29' AND date_worked <= '2017-02-04' THEN wh_time_subitem.hours_worked END) as hours_total_old1, 
SUM(CASE WHEN allocation_code_id = '91207' AND date_worked >= '2017-01-29' AND date_worked <= '2017-02-04' THEN wh_time_subitem.hours_worked END) as sick_leave1,
SUM(CASE WHEN allocation_code_id = '91206' AND date_worked >= '2017-01-29' AND date_worked <= '2017-02-04' THEN wh_time_subitem.hours_worked END) as vacation1, 
SUM(CASE WHEN allocation_code_id = '91209' AND date_worked >= '2017-01-29' AND date_worked <= '2017-02-04' THEN wh_time_subitem.hours_worked END) as personal_leave1,
SUM(CASE WHEN allocation_code_id = '91208' AND date_worked >= '2017-01-29' AND date_worked <= '2017-02-04' THEN wh_time_subitem.hours_worked END) as holiday1,
SUM(CASE WHEN allocation_code_id = '30091631' AND date_worked >= '2017-01-29' AND date_worked <= '2017-02-04' THEN wh_time_subitem.hours_worked END) as funeral_leave1, 
ISNULL(MAX(CASE WHEN wh_time_report.period_end_date = '2017-02-11' THEN time_status_id END),0) as timesheet_status2,
SUM(CASE WHEN date_worked >= '2017-02-05' AND date_worked <= '2017-02-11' THEN ROUND(wh_time_subitem.hours_worked,2) END) as hours_total2,
SUM(CASE WHEN date_worked >= '2017-02-05' AND date_worked <= '2017-02-11' THEN wh_time_subitem.hours_worked END) as hours_total_old2, 
SUM(CASE WHEN allocation_code_id = '91207' AND date_worked >= '2017-02-05' AND date_worked <= '2017-02-11' THEN wh_time_subitem.hours_worked END) as sick_leave2,
SUM(CASE WHEN allocation_code_id = '91206' AND date_worked >= '2017-02-05' AND date_worked <= '2017-02-11' THEN wh_time_subitem.hours_worked END) as vacation2, 
SUM(CASE WHEN allocation_code_id = '91209' AND date_worked >= '2017-02-05' AND date_worked <= '2017-02-11' THEN wh_time_subitem.hours_worked END) as personal_leave2,
SUM(CASE WHEN allocation_code_id = '91208' AND date_worked >= '2017-02-05' AND date_worked <= '2017-02-11' THEN wh_time_subitem.hours_worked END) as holiday2,
SUM(CASE WHEN allocation_code_id = '30091631' AND date_worked >= '2017-02-05' AND date_worked <= '2017-02-11' THEN wh_time_subitem.hours_worked END) as funeral_leave2
FROM dbo.wh_resource
JOIN dbo.wh_department_resource ON wh_resource.resource_id = wh_department_resource.resource_id 
JOIN dbo.wh_department ON wh_department_resource.department_id = wh_department.department_id
LEFT JOIN wh_time_item ON wh_time_item.user_id =  wh_resource.resource_id
LEFT JOIN wh_time_subitem ON wh_time_subitem.time_item_id = wh_time_item.time_item_id
LEFT JOIN wh_time_report ON wh_time_report.creator_resource_id = wh_department_resource.resource_id 
WHERE wh_department_resource.is_default_department = 1 
AND wh_resource.is_active = 1 
AND last_name != 'API.User' 
AND wh_department.department_id = '30091606'
AND department_name NOT IN ('Sales', 'Marketing', 'Operations', '') 
AND (wh_time_report.period_end_date IN ('2017-02-04', '2017-02-11') OR wh_time_report.period_end_date IS NULL)
GROUP BY wh_resource.resource_id, last_name, first_name, narrative_full_name, hire_date, department_name
ORDER BY last_name

That one query returns 73 results and there's 76 people, so if I did a query to pull people with

SELECT * FROM dbo.wh_resource 
JOIN wh_department_resource ON wh_resource.resource_id = wh_department_resource.resource_id
WHERE wh_resource.is_active = 1
AND department_id = '30091606' 
AND is_default_department ='1'
ORDER BY last_name, first_name

I could get all the data I need in two queries in PHP by checking if a name exists. But I'd much rather get it all in one query!

Edit: Using SqlZim's push, I have the following query working well for me. It may be messy, but it runs quickly (much faster than the 1000 queries this one is replacing, no joke). The dates will be provided via PHP, as will the department id if needed.

SELECT wh_resource.resource_id, last_name, first_name, narrative_full_name, department_name,
ISNULL(MAX(CASE WHEN T1.period_end_date = '2017-02-04' THEN T1.time_status_id END),0) as timesheet_status1,
SUM(CASE WHEN S1.date_worked >= '2017-01-29' AND S1.date_worked <= '2017-02-04' THEN ROUND(S1.hours_worked,2) END) as hours_total1,
SUM(CASE WHEN S1.date_worked >= '2017-01-29' AND S1.date_worked <= '2017-02-04' THEN S1.hours_worked END) as hours_total_old1, 
SUM(CASE WHEN S1.allocation_code_id = '91207' AND S1.date_worked >= '2017-01-29' AND S1.date_worked <= '2017-02-04' THEN S1.hours_worked END) as sick_leave1,
SUM(CASE WHEN S1.allocation_code_id = '91206' AND S1.date_worked >= '2017-01-29' AND S1.date_worked <= '2017-02-04' THEN S1.hours_worked END) as vacation1, 
SUM(CASE WHEN S1.allocation_code_id = '91209' AND S1.date_worked >= '2017-01-29' AND S1.date_worked <= '2017-02-04' THEN S1.hours_worked END) as personal_leave1,
SUM(CASE WHEN S1.allocation_code_id = '91208' AND S1.date_worked >= '2017-01-29' AND S1.date_worked <= '2017-02-04' THEN S1.hours_worked END) as holiday1,
SUM(CASE WHEN S1.allocation_code_id = '30091631' AND S1.date_worked >= '2017-01-29' AND S1.date_worked <= '2017-02-04' THEN S1.hours_worked END) as funeral_leave1, 
ISNULL(MAX(CASE WHEN T2.period_end_date = '2017-02-11' THEN T2.time_status_id END),0) as timesheet_status2,
SUM(CASE WHEN S2.date_worked >= '2017-02-05' AND S2.date_worked <= '2017-02-11' THEN ROUND(S2.hours_worked,2) END) as hours_total2,
SUM(CASE WHEN S2.date_worked >= '2017-02-05' AND S2.date_worked <= '2017-02-11' THEN S2.hours_worked END) as hours_total_old2, 
SUM(CASE WHEN S2.allocation_code_id = '91207' AND S2.date_worked >= '2017-02-05' AND S2.date_worked <= '2017-02-11' THEN S2.hours_worked END) as sick_leave2,
SUM(CASE WHEN S2.allocation_code_id = '91206' AND S2.date_worked >= '2017-02-05' AND S2.date_worked <= '2017-02-11' THEN S2.hours_worked END) as vacation2, 
SUM(CASE WHEN S2.allocation_code_id = '91209' AND S2.date_worked >= '2017-02-05' AND S2.date_worked <= '2017-02-11' THEN S2.hours_worked END) as personal_leave2,
SUM(CASE WHEN S2.allocation_code_id = '91208' AND S2.date_worked >= '2017-02-05' AND S2.date_worked <= '2017-02-11' THEN S2.hours_worked END) as holiday2,
SUM(CASE WHEN S2.allocation_code_id = '30091631' AND S2.date_worked >= '2017-02-05' AND S2.date_worked <= '2017-02-11' THEN S2.hours_worked END) as funeral_leave2
FROM dbo.wh_resource
JOIN dbo.wh_department_resource ON wh_resource.resource_id = wh_department_resource.resource_id 
JOIN dbo.wh_department ON wh_department_resource.department_id = wh_department.department_id
LEFT JOIN wh_time_item ON wh_time_item.user_id =  wh_resource.resource_id
LEFT JOIN wh_time_report T1 ON T1.creator_resource_id = wh_resource.resource_id AND T1.period_end_date = '2017-02-04'
LEFT JOIN wh_time_subitem S1 ON S1.time_item_id = wh_time_item.time_item_id AND S1.date_worked >= '2017-01-29' AND S1.date_worked < '2017-02-05'
LEFT JOIN wh_time_report T2 ON T2.creator_resource_id = wh_resource.resource_id AND T2.period_end_date = '2017-02-11'
LEFT JOIN wh_time_subitem S2 ON S2.time_item_id = wh_time_item.time_item_id AND S2.date_worked >= '2017-02-05' AND S2.date_worked < '2017-02-12'
WHERE wh_department_resource.is_default_department = 1 
AND wh_resource.is_active = 1 
AND last_name != 'API.User' 
AND wh_department.department_id = '30091606'
AND department_name NOT IN ('Sales', 'Marketing', 'Operations', '') 
GROUP BY wh_resource.resource_id, last_name, first_name, narrative_full_name, hire_date, department_name
ORDER BY last_name

Answer 1

Execuse me, but I havent enough time to operate with full query you provided. You can try OUTER APPLY to achieve desired result - get zero at case, when no records matched cirteria in table. Short example, how to do this:

SELECT 
     [time_status_id] = a.time_status_id
    ,[last_name] = a.last_name
    ,[count] = isnull(b.cnt, 0)
FROM 
    dbo.wh_resource as a
outer apply
    (
        select
            cnt = count(*)
        from
            wh_time_report
        where
             creator_resource_id = a.resource_id             
    ) as b     
WHERE a.resource_id = '31100670'
AND a.period_end_date >= '2017-01-29'
ORDER BY a.last_name

Answer 2

Since you don't give table for period_end_date and time_status_id not sure
But try this

SELECT time_status_id, last_name
FROM dbo.wh_resource
LEFT JOIN wh_time_report 
       ON wh_time_report.creator_resource_id = wh_resource.resource_id
      AND period_end_date >= '2017-01-29'
WHERE wh_resource.resource_id = '31100670'

ORDER BY last_name

Answer 3

If you are referencing columns of an outer join table (left in this case) in your where without allowing for nulls, you will be eliminating rows.

If you move your where conditions to your join conditions, you can deal with nulls in your select with isnull() and/or coalesce().

For your query that would look something like this:

select 
    wr.resource_id
  , last_name
  , first_name
  , narrative_full_name
  , department_name
  , timesheet_status1 = isnull(max(case when wtr.period_end_date = '2017-02-04' then time_status_id end),0)
  , hours_total1      = sum(case when date_worked >= '2017-01-29' and date_worked <= '2017-02-04' then round(wts.hours_worked,2) end)
  , hours_total_old1  = sum(case when date_worked >= '2017-01-29' and date_worked <= '2017-02-04' then wts.hours_worked end)
  , sick_leave1       = sum(case when allocation_code_id = '91207'    and date_worked >= '2017-01-29' and date_worked <= '2017-02-04' then wts.hours_worked end)
  , vacation1         = sum(case when allocation_code_id = '91206'    and date_worked >= '2017-01-29' and date_worked <= '2017-02-04' then wts.hours_worked end)
  , personal_leave1   = sum(case when allocation_code_id = '91209'    and date_worked >= '2017-01-29' and date_worked <= '2017-02-04' then wts.hours_worked end)
  , holiday1          = sum(case when allocation_code_id = '91208'    and date_worked >= '2017-01-29' and date_worked <= '2017-02-04' then wts.hours_worked end)
  , funeral_leave1    = sum(case when allocation_code_id = '30091631' and date_worked >= '2017-01-29' and date_worked <= '2017-02-04' then wts.hours_worked end)
  , timesheet_status2 = isnull(max(case when wtr.period_end_date = '2017-02-11' then time_status_id end),0)
  , hours_total2      = sum(case when date_worked >= '2017-02-05' and date_worked <= '2017-02-11' then round(wts.hours_worked,2) end)
  , hours_total_old2  = sum(case when date_worked >= '2017-02-05' and date_worked <= '2017-02-11' then wts.hours_worked end)
  , sick_leave2       = sum(case when allocation_code_id = '91207'    and date_worked >= '2017-02-05' and date_worked <= '2017-02-11' then wts.hours_worked end)
  , vacation2         = sum(case when allocation_code_id = '91206'    and date_worked >= '2017-02-05' and date_worked <= '2017-02-11' then wts.hours_worked end)
  , personal_leave2   = sum(case when allocation_code_id = '91209'    and date_worked >= '2017-02-05' and date_worked <= '2017-02-11' then wts.hours_worked end)
  , holiday2          = sum(case when allocation_code_id = '91208'    and date_worked >= '2017-02-05' and date_worked <= '2017-02-11' then wts.hours_worked end)
  , funeral_leave2    = sum(case when allocation_code_id = '30091631' and date_worked >= '2017-02-05' and date_worked <= '2017-02-11' then wts.hours_worked end) 
from dbo.wh_resource as wr
  inner join dbo.wh_department_resource as wdr 
     on wr.resource_id = wdr.resource_id
    and wr.is_active = 1
    and wr.last_name != 'api.user'
    and wdr.is_default_department = 1
  inner join dbo.wh_department as wd 
     on wdr.department_id = wd.department_id
    and wd.department_id = '30091606'
    and department_name not in ('sales', 'marketing', 'operations', '')
  left join wh_time_item as wti 
     on wti.user_id =  wr.resource_id
  left join wh_time_subitem as wts 
     on wts.time_item_id = wti.time_item_id
  left join wh_time_report as wtr 
     on wtr.creator_resource_id = wdr.resource_id
    and wtr.period_end_date in ('2017-02-04', '2017-02-11') 
group by wr.resource_id, last_name, first_name, narrative_full_name, hire_date, department_name
order by last_name