title: Grouping sets with month, quarter, half and year not working properly
body: <p>I am using SQL Server 2012. And I am not able to solve a little issue. </p>
  
  <p>When it shows the tag "Half Year Total" the "[HalfYear]" Field is null, and I would like to label it with the right half of year. </p>
  
  <p>Any help please!
  Thanks</p>
  
  <p>This is the code:</p>
  
  <pre><code>CREATE TABLE #tempDates (OrderDate DATETIME, SubTotal  MONEY)
  
  INSERT INTO #tempDates VALUES('2016-01-01', 10)
  INSERT INTO #tempDates VALUES('2016-01-02', 10)
  INSERT INTO #tempDates VALUES('2016-02-01', 15)
  INSERT INTO #tempDates VALUES('2016-02-02', 15)
  INSERT INTO #tempDates VALUES('2016-03-01', 20)
  INSERT INTO #tempDates VALUES('2016-03-02', 20)
  INSERT INTO #tempDates VALUES('2016-04-01', 10)
  INSERT INTO #tempDates VALUES('2016-04-02', 10)
  INSERT INTO #tempDates VALUES('2016-05-01', 15)
  INSERT INTO #tempDates VALUES('2016-05-02', 15)
  INSERT INTO #tempDates VALUES('2016-06-01', 20)
  INSERT INTO #tempDates VALUES('2016-06-02', 20)
  INSERT INTO #tempDates VALUES('2016-07-01', 10)
  INSERT INTO #tempDates VALUES('2016-07-02', 10)
  INSERT INTO #tempDates VALUES('2016-08-01', 15)
  INSERT INTO #tempDates VALUES('2016-08-02', 15)
  INSERT INTO #tempDates VALUES('2016-09-01', 20)
  INSERT INTO #tempDates VALUES('2016-09-02', 20)
  INSERT INTO #tempDates VALUES('2016-10-01', 10)
  INSERT INTO #tempDates VALUES('2016-10-02', 10)
  INSERT INTO #tempDates VALUES('2016-11-01', 15)
  INSERT INTO #tempDates VALUES('2016-11-02', 15)
  INSERT INTO #tempDates VALUES('2016-12-01', 20)
  INSERT INTO #tempDates VALUES('2016-12-02', 20)
  
  SELECT   
      CASE
          --WHEN GROUPING(YEAR(OrderDate)) = 1  THEN 'Year Total'
          WHEN GROUPING(
                          CASE 
                                  WHEN MONTH(OrderDate) &lt;= 6 THEN 'H1'  
                                  WHEN MONTH(OrderDate) &gt;  6 THEN 'H2'  
                                  WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &lt;= 2 THEN 'H1'  
                                  WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &gt;  2 THEN 'H2'  
                          END
                      ) = 1
              THEN 'Year Total'
          WHEN GROUPING(DATENAME(QUARTER, OrderDate)) = 1  THEN 'Half Year Total'
          WHEN GROUPING(MONTH(OrderDate)) = 1 THEN 'Quarter Total'
          ELSE ''
      END AS TotalType,
      MONTH(OrderDate) AS [Month],
      DATENAME(QUARTER, OrderDate) AS [Quarter],
      CASE 
           WHEN MONTH(OrderDate) &lt;= 6 THEN 'H1'  
           WHEN MONTH(OrderDate) &gt;  6 THEN 'H2'  
           WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &lt;= 2 THEN 'H1'  
           WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &gt;  2 THEN 'H2'  
           --WHEN DATENAME(QUARTER, OrderDate) IS NULL THEN 'Half Total' 
      END  AS [HalfYear],
      YEAR(OrderDate) AS [Year],
      SUM(SubTotal) as Purchases 
  FROM     #tempDates
  GROUP BY GROUPING SETS
  (
      (
      MONTH(OrderDate),  
      DATENAME(QUARTER, OrderDate),
       CASE 
           WHEN MONTH(OrderDate) &lt;= 6 THEN 'H1'  
           WHEN MONTH(OrderDate) &gt;  6 THEN 'H2'  
           WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &lt;= 2 THEN 'H1'  
           WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &gt;  2 THEN 'H2' 
       END, 
       YEAR(OrderDate)
   ),
  ( 
       DATENAME(QUARTER, OrderDate),
       CASE 
           WHEN MONTH(OrderDate) &lt;= 6 THEN 'H1'  
           WHEN MONTH(OrderDate) &gt;  6 THEN 'H2'  
           WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &lt;= 2 THEN 'H1'  
           WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &gt;  2 THEN 'H2'  
       END,  
       YEAR(OrderDate)
   ),
   ( 
       CASE 
           WHEN MONTH(OrderDate) &lt;= 6 THEN 'H1'  
           WHEN MONTH(OrderDate) &gt;  6 THEN 'H2'  
           WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &lt;= 2 THEN 'H1'  
           WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &gt;  2 THEN 'H2' 
       END,  
       YEAR(OrderDate)
   ),
  (Year(OrderDate))
  )
  ORDER BY 
         CASE 
         -- Month
         WHEN 
          GROUPING(MONTH(OrderDate)) = 1 AND 
          GROUPING(DATENAME(QUARTER, OrderDate)) = 1 AND  
          GROUPING( CASE 
                      WHEN MONTH(OrderDate) &lt;= 6 THEN 'H1'  
                      WHEN MONTH(OrderDate) &gt;  6 THEN 'H2'  
                      WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &lt;= 2 THEN 'H1'  
                      WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &gt;  2 THEN 'H2'  
                   END) = 1 AND 
          GROUPING(YEAR(OrderDate)) = 1 THEN '4'
         -- Quarter   
         WHEN 
          GROUPING(MONTH(OrderDate)) = 0 AND 
          GROUPING(DATENAME(QUARTER, OrderDate)) = 1 AND  
          GROUPING( CASE 
                       WHEN MONTH(OrderDate) &lt;= 6 THEN 'H1'  
                       WHEN MONTH(OrderDate) &gt;  6 THEN 'H2'  
                       WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &lt;= 2 THEN 'H1'  
                       WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &gt;  2 THEN 'H2'  
                   END) = 1 AND 
          GROUPING(YEAR(OrderDate)) = 1 THEN '3'
         -- Half a year
         WHEN 
          GROUPING(MONTH(OrderDate)) = 0 AND 
          GROUPING(DATENAME(QUARTER, OrderDate)) = 0 AND  
          GROUPING( CASE 
                       WHEN MONTH(OrderDate) &lt;= 6 THEN 'H1'  
                       WHEN MONTH(OrderDate) &gt;  6 THEN 'H2'  
                       WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &lt;= 2 THEN 'H1'  
                       WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &gt;  2 THEN 'H2'
                   END) = 1 AND 
          GROUPING(YEAR(OrderDate)) = 1 THEN '3'
        -- Year
        WHEN 
          GROUPING(MONTH(OrderDate)) = 0 AND 
          GROUPING(DATENAME(QUARTER, OrderDate)) = 0 AND  
          GROUPING( CASE 
                       WHEN MONTH(OrderDate) &lt;= 6 THEN 'H1'  
                       WHEN MONTH(OrderDate) &gt;  6 THEN 'H2'  
                       WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &lt;= 2 THEN 'H1'  
                       WHEN MONTH(OrderDate) IS NULL AND  DATENAME(QUARTER, OrderDate) &gt;  2 THEN 'H2' 
                   END) = 0 AND 
          GROUPING(YEAR(OrderDate)) = 1 THEN '1'
  
        ELSE
         '0'
      END
  </code></pre>