title: SQL getting a column to match multiple conditions in CASE
body: <p>Presented with the following snippet of SQL:</p>
  
  <pre><code>CASE
      WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') &lt; DATE_FORMAT(`StartDateTCDR`, '%m%d')) &gt;= 30) THEN '&gt;= 30 (inclusive)'
      WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') &lt; DATE_FORMAT(`StartDateTCDR`, '%m%d')) &gt;= 20) THEN '&gt;= 20 (inclusive)'
      WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') &lt; DATE_FORMAT(`StartDateTCDR`, '%m%d')) &gt;= 10) THEN '&gt;= 10 (inclusive)'
      ELSE '&lt; 10'
  END
  </code></pre>
  
  <p>(The full query is below, the snippet above is just to highlight the problem)</p>
  
  <p>The above current results in exclusive matches:</p>
  
  <pre><code> (30 &lt;= date_diff) = '&gt;= 30'
   (20 &lt;= date_diff &lt;= 30) = '&gt;= 20'
   (10 &lt;= date_diff &lt;= 20) = '&gt;= 10'
   (date_diff &lt; 10) = '&lt; 10'
  </code></pre>
  
  <p>But what I need is inclusive matches:</p>
  
  <pre><code> (30 &lt;= date_diff &lt;= 0) = '&gt;= 30'
   (20 &lt;= date_diff &lt;= 0) = '&gt;= 20'
   (10 &lt;= date_diff &lt;= 0) = '&gt;= 10'
   (date_diff &lt; 10) = '&lt; 10'
  </code></pre>
  
  <p>So -  if a record is older than 30 years, it must be categorized in in <code>&gt;= 10</code>, <code>&gt;= 20</code> as well as <code>&gt;= 30</code>, and so on.</p>
  
  <p><strong>FULL QUERY</strong></p>
  
  <pre><code>    SELECT
          COUNT(*) AS `RecordCount`, `ExistingPlanned`, `ResponsibleOrg`, `StartDateTCDR`, `EndDateTCDR`, `SpecificEndDate`, `ResponsibleOrgOut`,
          (CASE
              WHEN (`EndDateTCDR` &lt; '1001-01-01' AND `SpecificEndDate` = 0) THEN '2018-12-31'
              WHEN (`EndDateTCDR` &lt; '1001-01-01' AND `SpecificEndDate` = 1) THEN '-'
              ELSE `EndDateTCDR`
          END) AS `EndDate`,
          (CASE
              WHEN (`StartDateTCDR` &lt; '1001-01-01') THEN '-'
              ELSE `StartDateTCDR`
          END) AS `StartDate`,
          (CASE
              WHEN (`StartDateTCDR` &lt; '1001-01-01') THEN '-'
              WHEN (`StartDateTCDR` &gt; '1001-01-01' AND `EndDateTCDR` &lt; '1001-01-01' AND `SpecificEndDate` = 1) THEN '-'
              WHEN (`StartDateTCDR` &gt; '1001-01-01' AND `EndDateTCDR` &lt; '1001-01-01' AND `SpecificEndDate` = 0) THEN (
                  CASE
                      WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') &lt; DATE_FORMAT(`StartDateTCDR`, '%m%d')) &gt;= 30) THEN '&gt;= 30 (inclusive)'
                      WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') &lt; DATE_FORMAT(`StartDateTCDR`, '%m%d')) &gt;= 20) THEN '&gt;= 20 (inclusive)'
                      WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') &lt; DATE_FORMAT(`StartDateTCDR`, '%m%d')) &gt;= 10) THEN '&gt;= 10 (inclusive)'
                      ELSE '&lt; 10'
                  END
              )
              WHEN (`StartDateTCDR` &gt; '1001-01-01' AND `EndDateTCDR` &gt; '1001-01-01') THEN (
                  CASE
                      WHEN (YEAR(`EndDateTCDR`) - YEAR(`StartDateTCDR`) - (DATE_FORMAT(`EndDateTCDR`, '%m%d') &lt; DATE_FORMAT(`StartDateTCDR`, '%m%d')) &gt;= 30) THEN '&gt;= 30 (inclusive)'
                      WHEN (YEAR(`EndDateTCDR`) - YEAR(`StartDateTCDR`) - (DATE_FORMAT(`EndDateTCDR`, '%m%d') &lt; DATE_FORMAT(`StartDateTCDR`, '%m%d')) &gt;= 20) THEN '&gt;= 20 (inclusive)'
                      WHEN (YEAR(`EndDateTCDR`) - YEAR(`StartDateTCDR`) - (DATE_FORMAT(`EndDateTCDR`, '%m%d') &lt; DATE_FORMAT(`StartDateTCDR`, '%m%d')) &gt;= 10) THEN '&gt;= 10 (inclusive)'
                      ELSE '&lt; 10'
                  END
              )
          END) AS `TCDRLength`,
          (CASE
              WHEN (`StartDateTCDR` &lt; '1001-01-01') THEN '-'
              WHEN (`StartDateTCDR` &gt; '1001-01-01' AND `EndDateTCDR` &lt; '1001-01-01' AND `SpecificEndDate` = 1) THEN '-'
              WHEN (`StartDateTCDR` &gt; '1001-01-01' AND `EndDateTCDR` &lt; '1001-01-01' AND `SpecificEndDate` = 0) THEN (
                  ROUND((DATEDIFF('2018-12-31', `StartDateTCDR`) / 365), 2)
              )
              WHEN (`StartDateTCDR` &gt; '1001-01-01' AND `EndDateTCDR` &gt; '1001-01-01') THEN (
                  ROUND((DATEDIFF(`EndDateTCDR`, `StartDateTCDR`) / 365), 2)
              )
          END) AS `ActualLength`
      FROM `data_records`
      LEFT JOIN `RespOrgLUT`
          ON `ResponsibleOrgIn` = `ResponsibleOrg`
      GROUP BY `ExistingPlanned`, `ResponsibleOrg`, `StartDateTCDR`, `EndDateTCDR`, `SpecificEndDate`, `ResponsibleOrgOut`, `StartDate`, `EndDate`, `ActualLength`, `TCDRLength`
      ORDER BY `ResponsibleOrg` ASC, `RecordCount` DESC, `ResponsibleOrgOut` ASC, `ActualLength` DESC, `TCDRLength` DESC
      LIMIT 0, 2000
  </code></pre>
  
  <p><strong>EDIT 1</strong></p>
  
  <p>Using the suggestion by @Barmar, my query now looks like this, resulting in an error: <code>#1248 - Every derived table must have its own alias</code>, so I clearly implemented it wrong, so any further assistance will be appreciated.</p>
  
  <pre><code>    SELECT
          COUNT(*) AS `RecordCount`, `ExistingPlanned`, `ResponsibleOrg`, `StartDateTCDR`, `EndDateTCDR`, `SpecificEndDate`, `ResponsibleOrgOut`,
          (CASE
              WHEN (`EndDateTCDR` &lt; '1001-01-01' AND `SpecificEndDate` = 0) THEN '2018-12-31'
              WHEN (`EndDateTCDR` &lt; '1001-01-01' AND `SpecificEndDate` = 1) THEN '-'
              ELSE `EndDateTCDR`
          END) AS `EndDate`,
          (CASE
              WHEN (`StartDateTCDR` &lt; '1001-01-01') THEN '-'
              ELSE `StartDateTCDR`
          END) AS `StartDate`,
          (CASE
              WHEN (`StartDateTCDR` &lt; '1001-01-01') THEN '-'
              WHEN (`StartDateTCDR` &gt; '1001-01-01' AND `EndDateTCDR` &lt; '1001-01-01' AND `SpecificEndDate` = 1) THEN '-'
              WHEN (`StartDateTCDR` &gt; '1001-01-01' AND `EndDateTCDR` &lt; '1001-01-01' AND `SpecificEndDate` = 0) THEN (
                  CASE
                      WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') &lt; DATE_FORMAT(`StartDateTCDR`, '%m%d')) &gt;= 30) THEN '&gt;= 30 (inclusive)'
                      WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') &lt; DATE_FORMAT(`StartDateTCDR`, '%m%d')) &gt;= 20) THEN '&gt;= 20 (inclusive)'
                      WHEN (YEAR('2018-12-31') - YEAR(`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') &lt; DATE_FORMAT(`StartDateTCDR`, '%m%d')) &gt;= 10) THEN '&gt;= 10 (inclusive)'
                      ELSE '&lt; 10'
                  END
              )
              WHEN (`StartDateTCDR` &gt; '1001-01-01' AND `EndDateTCDR` &gt; '1001-01-01') THEN (
                  CASE
                      WHEN (YEAR(`EndDateTCDR`) - YEAR(`StartDateTCDR`) - (DATE_FORMAT(`EndDateTCDR`, '%m%d') &lt; DATE_FORMAT(`StartDateTCDR`, '%m%d')) &gt;= 30) THEN '&gt;= 30 (inclusive)'
                      WHEN (YEAR(`EndDateTCDR`) - YEAR(`StartDateTCDR`) - (DATE_FORMAT(`EndDateTCDR`, '%m%d') &lt; DATE_FORMAT(`StartDateTCDR`, '%m%d')) &gt;= 20) THEN '&gt;= 20 (inclusive)'
                      WHEN (YEAR(`EndDateTCDR`) - YEAR(`StartDateTCDR`) - (DATE_FORMAT(`EndDateTCDR`, '%m%d') &lt; DATE_FORMAT(`StartDateTCDR`, '%m%d')) &gt;= 10) THEN '&gt;= 10 (inclusive)'
                      ELSE '&lt; 10'
                  END
              )
          END) AS `TCDRLength`,
          (CASE
              WHEN (`StartDateTCDR` &lt; '1001-01-01') THEN '-'
              WHEN (`StartDateTCDR` &gt; '1001-01-01' AND `EndDateTCDR` &lt; '1001-01-01' AND `SpecificEndDate` = 1) THEN '-'
              WHEN (`StartDateTCDR` &gt; '1001-01-01' AND `EndDateTCDR` &lt; '1001-01-01' AND `SpecificEndDate` = 0) THEN (
                  ROUND((DATEDIFF('2018-12-31', `StartDateTCDR`) / 365), 2)
              )
              WHEN (`StartDateTCDR` &gt; '1001-01-01' AND `EndDateTCDR` &gt; '1001-01-01') THEN (
                  ROUND((DATEDIFF(`EndDateTCDR`, `StartDateTCDR`) / 365), 2)
              )
          END) AS `ActualLength`
      FROM `data_records`
      LEFT JOIN `RespOrgLUT` ON `ResponsibleOrgIn` = `ResponsibleOrg`
      LEFT JOIN (
          SELECT `category`, COUNT(*)
          FROM `data_records` dr
          JOIN (
              SELECT '&lt; 10' AS `category`
              UNION
              SELECT '&gt;= 10'
              UNION
              SELECT '&gt;= 20'
              UNION
              SELECT '&gt;= 30'
          ) AS c
              ON (`category` = '&gt;= 30' AND (YEAR('2018-12-31') - YEAR(`dr`.`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') &lt; DATE_FORMAT(`dr`.`StartDateTCDR`, '%m%d')) &gt;= 30))
              OR (`category` = '&gt;= 20' AND (YEAR('2018-12-31') - YEAR(`dr`.`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') &lt; DATE_FORMAT(`dr`.`StartDateTCDR`, '%m%d')) &gt;= 20))
              OR (`category` = '&gt;= 10' AND (YEAR('2018-12-31') - YEAR(`dr`.`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') &lt; DATE_FORMAT(`dr`.`StartDateTCDR`, '%m%d')) &gt;= 10))
              OR (`category` = '&lt; 10' AND (YEAR('2018-12-31') - YEAR(`dr`.`StartDateTCDR`) - (DATE_FORMAT('2018-12-31', '%m%d') &lt; DATE_FORMAT(`dr`.`StartDateTCDR`, '%m%d')) &lt; 10))
      )
      GROUP BY `ExistingPlanned`, `ResponsibleOrg`, `StartDateTCDR`, `EndDateTCDR`, `SpecificEndDate`, `ResponsibleOrgOut`, `StartDate`, `EndDate`, `ActualLength`, `TCDRLength`
      ORDER BY `ResponsibleOrg` ASC, `RecordCount` DESC, `ResponsibleOrgOut` ASC, `ActualLength` DESC, `TCDRLength` DESC
      LIMIT 0, 2000
  </code></pre>