title: Do I need a CASE expression?
body: <p>I have the following query which returns an organizations prior year (from the current year, so 2018) total wages. </p>
  
  <pre><code>SELECT 
      organization_id, 
      CASE 
         WHEN organization_id IN (SELECT text_1 
                                  FROM combo_table_detail 
                                  WHERE combo_table_id = 'wageAdjustment') 
            THEN SUM(ISNULL(component_value, 0)) + ISNULL(ctd.number_2, 0) 
            ELSE SUM(ISNULL(component_value, 0)) 
      END AS "total_annual_wage", 
      MAX((begin_date)) AS "total_annual_wage_eff_date" 
  FROM   
      actual_pay_hours aph 
  LEFT JOIN 
      combo_table_detail ctd ON aph.organization_id = ctd.text_1 
                             AND combo_table_id = 'wageAdjustment' 
  WHERE  
      organization_id = 'Org1' 
      AND component_name IN ('earnDef', 'earnings') 
      AND begin_date &gt;= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) 
      AND begin_date &lt; DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
  GROUP BY 
      organization_id, ctd.number_2 
  </code></pre>
  
  <p>However, I've run across an issue where some organizations either don't have a prior year (some only have 2019 wages), or their latest wages are from 2014. In both cases, the query returns blank values. This is due to the line </p>
  
  <pre><code>AND begin_date &gt;= DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) 
  AND begin_date &lt; DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)
  </code></pre>
  
  <p>The expected result should look something like this:</p>
  
  <pre><code>+-----------------+-------------------+----------------------------+
  | organization_id | total_annual_wage | total_annual_wage_eff_date |
  +-----------------+-------------------+----------------------------+
  | Org1            |             50000 | 12/1/2018                  |
  +-----------------+-------------------+----------------------------+
  </code></pre>
  
  <p>But instead, it looks like this:</p>
  
  <pre><code>+-----------------+-------------------+----------------------------+
  | organization_id | total_annual_wage | total_annual_wage_eff_date |
  +-----------------+-------------------+----------------------------+
  </code></pre>
  
  <p>This issue seems to be the fact that in the aph table, some units have wages for the 2018 year, while others don't. Example:</p>
  
  <pre><code>SELECT DISTINCT
      YEAR(BEGIN_DATE) AS [Begin Date for Org1]
  FROM    
      ACTUAL_PAY_HOURS aph
  WHERE
      ORGANIZATION_ID = 'Org1'
  </code></pre>
  
  <p>Results:</p>
  
  <pre><code>+---------------------+
  | Begin Date for Org1 |
  +---------------------+
  |                1988 |
  |                1989 |
  |                1990 |
  |                1991 |
  |                1992 |
  |                1993 |
  |                1994 |
  |                2004 |
  |                2005 |
  |                2006 |
  |                2007 |
  |                2008 |
  |                2009 |
  |                2010 |
  |                2011 |
  |                2012 |
  |                2013 |
  |                2014 |
  +---------------------+
  </code></pre>
  
  <p>Additionally, the total wages for the prior year are then being adjusted by a value in the CTD (combo_table_detail) table. The issue is that when there are no values for an exisitng year, nothing is returned. What I need is for the wage total to then be 0 - since there isn't any data for that year, but then the value from the CTD table is added.</p>
  
  <p>So, if Org1 has no wages for 2018, it should come out like this:</p>
  
  <pre><code>+-----------------+-------------+-------------------+-------+
  | organization_id | total_wages | combo_table_value | total |
  +-----------------+-------------+-------------------+-------+
  | Org1            |           0 |             25000 | 25000 |
  +-----------------+-------------+-------------------+-------+
  </code></pre>
  
  <p>So my question is, what logic can I add to this query that will return a result when the Organization doesn't have any prior year wages, but will still be added to the CTD table resulting in a value being returned?</p>