title: Use dimension table for data combination not existing in main table
body: <p>I have a stored procedure that generates a report. The actual report is a bit complex so I will try to explain myself as simple as possible with table examples:</p>
  
  <p>My main table has the following data:</p>
  
  <p>table1</p>
  
  <pre><code>╔════╦═══════╦═════════╦═══════╗
  ║ id ║ price ║ Item_id ║ name  ║
  ╠════╬═══════╬═════════╬═══════╣
  ║ 1  ║ 3     ║ 11      ║ item1 ║
  ╠════╬═══════╬═════════╬═══════╣
  ║ 2  ║ 5     ║ 22      ║ item2 ║
  ╠════╬═══════╬═════════╬═══════╣
  ║ 3  ║ 6     ║ 33      ║ item3 ║
  ╠════╬═══════╬═════════╬═══════╣
  ║ 4  ║ 7     ║ 44      ║ item4 ║
  ╠════╬═══════╬═════════╬═══════╣
  ║ 5  ║ 8     ║ 55      ║ item5 ║
  ╚════╩═══════╩═════════╩═══════╝
  </code></pre>
  
  <p>and I have the code:</p>
  
  <pre><code>select id, 
           CASE 
               when price &lt;= 2    THEN '01-02'
               when price &lt;= 3    THEN '03-04'
               when price &lt;= 4    THEN '05-05'
               when price &lt;= 5    THEN '05-06'
               ...ETC
           END as price,
  Item_id,
  name
  from table1
  </code></pre>
  
  <p>Now, from the data I have, in this case there is no price "<code>when price &lt;= 2    THEN '01-02'</code>"  so that option doesn't appear in the final report, however I would like for it to appear, with a 0.</p>
  
  <p>I understand that for this I need to do a LEFT JOIN with a "dimension" table to use a template, but I don't know how to implement this.</p>
  
  <p>I'm not sure if it would be a table with all the combinations of what I want to show, like <code>"'01-02'"</code> and then join with it, but that doesn't seem to work.</p>
  
  <p>This is the code from the SP:</p>
  
  <pre><code>DECLARE @RaceTrack CHAR(5)= 'Bala';
  DECLARE @Category VARCHAR(4000)= 'All, Class, Days, Distance, Race, Runners, Total Bracket Count, Runners2';
  DECLARE @TrotPace VARCHAR(10)= 'Excluded';
  WITH CTE_TblData
       AS (SELECT td.EVENT_ID, 
                  td.MENU_HINT, 
                  SUBSTRING(td.menu_hint, 7, 4) AS [RaceTrack],
                  td.EVENT_NAME, 
                  td.BSP, 
                  td.EVENT_DT AS Expr1003, 
                  td.WIN_LOSE, 
                  COUNT(*) OVER(PARTITION BY event_id) AS [RaceRunners], 
                  td.[Race No] AS RaceNo, 
                  replace(LEFT(SUBSTRING(td.EVENT_NAME, CHARINDEX(' ', td.EVENT_NAME) + 1, LEN(td.EVENT_NAME)), CHARINDEX(' ', SUBSTRING(td.EVENT_NAME, CHARINDEX(' ', td.EVENT_NAME) + 2, LEN(td.EVENT_NAME)))), 'm', '') AS Distance, 
                  REVERSE(LEFT(REVERSE(td.EVENT_NAME), CHARINDEX(' ', REVERSE(td.EVENT_NAME)) - 1)) AS Class, 
                  FORMAT(CONVERT(DATETIME, td.EVENT_DT, 103), 'dddd') AS [Day], 
                  DATEPART(WEEKDAY, CONVERT(DATETIME, td.EVENT_DT, 103)) AS [DayNumber]
           FROM tblData td
           WHERE(@TrotPace &lt;&gt; 'Excluded' OR td.event_name NOT LIKE '%trot%' AND td.event_name NOT LIKE '%pace%') AND SUBSTRING(td.menu_hint, 7, 4) = @RaceTrack),
       NewCTE1_PriceLabel
       AS (SELECT td.*, price.label AS [PriceBracketLabel]
           FROM CTE_TblData AS td
                CROSS APPLY
           (
               SELECT CASE    WHEN td.BSP &lt;= 2    THEN '01-02'
                              WHEN td.BSP &lt;= 3    THEN '02-03'
                              WHEN td.BSP &lt;= 5    THEN '03-05'
                              WHEN td.BSP &lt;= 8    THEN '05-08'
                              WHEN td.BSP &lt;= 13   THEN '08-13'
                              WHEN td.BSP &lt;= 21   THEN '13-21'
                              WHEN td.BSP &lt;= 34   THEN '21-34'
                              WHEN td.BSP &lt;= 55   THEN '34-55'
                              WHEN td.BSP &lt;= 89   THEN '55-89'
                              WHEN td.BSP &lt;= 1000 THEN '89-1000'
                      END AS label
           ) AS price),
       NewCTE2_WindowedCount
       AS (SELECT *, 
                  COUNT(*) OVER(PARTITION BY event_id, 
                                             PriceBracketLabel) AS [PriceBracketRunners]
           FROM NewCTE1_PriceLabel),
       DataCube
       AS (SELECT td.event_id, 
                  td.MENU_HINT, 
                  td.EVENT_NAME, 
                  td.Expr1003, 
                  td.RaceNo, 
                  td.Distance, 
                  td.Class, 
                  td.[Day], 
                  CASE WHEN brackets.category = brackets2.category then  brackets.category ELSE  brackets.category  + ' : ' + brackets2.category END  AS bracket_category, 
                  CASE WHEN brackets.label = brackets2.label THEN td.PriceBracketlabel + ' : ' + brackets.label ELSE td.PriceBracketlabel + ' : ' + brackets.label + ' : ' + brackets2.label END AS bracket_label, 
                  SUM(CASE WHEN td.WIN_LOSE = 1 THEN 1      ELSE 0 END) AS CountHorses_Winners, 
                  SUM(CASE WHEN td.WIN_LOSE = 1 THEN td.BSP ELSE 0 END) AS SumPrices_Winners, 
                  SUM(CASE WHEN td.WIN_LOSE = 0 THEN 1      ELSE 0 END) AS CountHorses_Losers, 
                  SUM(CASE WHEN td.WIN_LOSE = 0 THEN td.BSP ELSE 0 END) AS SumPrices_Losers
           FROM NewCTE2_WindowedCount AS td
                CROSS APPLY
           (
               SELECT 'All' AS category, 
                      'All' AS label
               UNION ALL
               SELECT 'Runners' AS category,
                      CASE
                          WHEN td.RaceRunners &lt;= 7    THEN '01-07'
                          WHEN td.RaceRunners &lt;= 12   THEN '07-12'
                          WHEN td.RaceRunners &lt;= 1000 THEN '12-1000'
                      END AS label
               UNION ALL
               SELECT 'Total Bracket Count' AS category,
                      CASE
                          WHEN td.PriceBracketRunners = 1      THEN '01'
                          WHEN td.PriceBracketRunners IN(2, 3) THEN '02-03'
                          WHEN td.PriceBracketRunners &gt; 3      THEN '04-1000'
                      END AS label
               UNION ALL
               SELECT 'Distance' AS category,
                      CASE
                          WHEN td.distance &lt;= 1200  THEN '0-1200'
                          WHEN td.distance &lt;= 1600  THEN '1200-1600'
                          WHEN td.distance &lt;= 10000 THEN '1600-10000'
                      END AS label
               UNION ALL
               SELECT 'Race' AS category,
                      CASE
                          WHEN td.raceno &lt; 5     THEN '0-4'
                          WHEN td.raceno &lt; 1000  THEN '04-1000'
                      END AS label
               UNION ALL
               --the stuff after the "then" is the title for the column, the stuff inside the brackets of "in" is what it filters
               SELECT 'Class' AS category,
                      CASE
                          WHEN td.Class IN('Mdn')                                            THEN 'Mdn'
                          WHEN td.Class IN('Cl1', 'Cl2', 'Cl3')                              THEN 'Cl1,Cl2,Cl3'
                          WHEN td.Class IN('Cl4', 'Cl5', 'Cl6')                              THEN 'Cl4,Cl5,Cl6'
                          WHEN td.Class IN('2yo')                                            THEN '2yo'
                          WHEN td.Class IN('3yo')                                            THEN '3yo'
                          WHEN td.Class IN('Hcp', 'Hcap')                                    THEN 'Hcp'
                          WHEN td.Class IN('Qlty', 'Listed', 'Grp3', 'Grp2', 'Grp1', 'WFA')  THEN 'Qlty,Listed,Grp3,Grp2,Grp1,WFA'
                      END AS label
               UNION ALL
               SELECT 'Days' AS category,
                      CASE
                          WHEN td.DayNumber IN(1, 2, 3) THEN 'Sun-Mon-Tues'
                          WHEN td.DayNumber IN(4, 5)    THEN 'Wed-Sat'
                          WHEN td.DayNumber IN(6, 7)    THEN 'Thurs-Fri'
                      END AS label
           ) AS brackets 
           cross apply
  
            (
               SELECT 'All' AS category, 
                      'All' AS label
               UNION ALL
               SELECT 'Runners' AS category,
                      CASE
                          WHEN td.RaceRunners &lt;= 7    THEN '01-07'
                          WHEN td.RaceRunners &lt;= 12   THEN '07-12'
                          WHEN td.RaceRunners &lt;= 1000 THEN '12-1000'
                      END AS label
               UNION ALL
               SELECT 'Total Bracket Count' AS category,
                      CASE
                          WHEN td.PriceBracketRunners = 1      THEN '01'
                          WHEN td.PriceBracketRunners IN(2, 3) THEN '02-03'
                          WHEN td.PriceBracketRunners &gt; 3      THEN '04-1000'
                      END AS label
               UNION ALL
               SELECT 'Distance' AS category,
                      CASE
                          WHEN td.distance &lt;= 1200  THEN '0-1200'
                          WHEN td.distance &lt;= 1600  THEN '1200-1600'
                          WHEN td.distance &lt;= 10000 THEN '1600-10000'
                      END AS label
               UNION ALL
               SELECT 'Race' AS category,
                      CASE
                          WHEN td.raceno &lt; 5     THEN '0-4'
                          WHEN td.raceno &lt; 1000  THEN '04-1000'
                      END AS label
               UNION ALL
               --the stuff after the "then" is the title for the column, the stuff inside the brackets of "in" is what it filters
               SELECT 'Class' AS category,
                      CASE
                          WHEN td.Class IN('Mdn')                                            THEN 'Mdn'
                          WHEN td.Class IN('Cl1', 'Cl2', 'Cl3')                              THEN 'Cl1,Cl2,Cl3'
                          WHEN td.Class IN('Cl4', 'Cl5', 'Cl6')                              THEN 'Cl4,Cl5,Cl6'
                          WHEN td.Class IN('2yo')                                            THEN '2yo'
                          WHEN td.Class IN('3yo')                                            THEN '3yo'
                          WHEN td.Class IN('Hcp', 'Hcap')                                    THEN 'Hcp'
                          WHEN td.Class IN('Qlty', 'Listed', 'Grp3', 'Grp2', 'Grp1', 'WFA')  THEN 'Qlty,Listed,Grp3,Grp2,Grp1,WFA'
                      END AS label
               UNION ALL
               SELECT 'Days' AS category,
                      CASE
                          WHEN td.DayNumber IN(1, 2, 3) THEN 'Sun-Mon-Tues'
                          WHEN td.DayNumber IN(4, 5)    THEN 'Wed-Sat'
                          WHEN td.DayNumber IN(6, 7)    THEN 'Thurs-Fri'
                      END AS label
           ) AS brackets2
           GROUP BY td.event_id, 
                    td.MENU_HINT, 
                    td.EVENT_NAME, 
                    td.Expr1003, 
                    td.RaceNo, 
                    td.Distance, 
                    td.Class, 
                    td.[Day], 
                    brackets.category, 
                    brackets2.category,
                    td.PriceBracketlabel, 
                    brackets.label,
                    brackets2.label),
       WinnerGroups
       AS (SELECT *, 
                  SUM(CountHorses_Winners) OVER(PARTITION BY bracket_category, 
                                                             bracket_label
                  ORDER BY Expr1003, 
                           RaceNo, 
                           event_id) AS winner_group_id, 
                  SumPrices_Winners - CountHorses_Winners - CountHorses_Losers AS line_profit
           FROM DataCube),
       GroupTotals
       AS (SELECT bracket_category, 
                  bracket_label, 
                  winner_group_id, 
                  SUM(line_profit) AS Group_Total_Profit
           FROM WinnerGroups
           GROUP BY bracket_category, 
                    bracket_label, 
                    winner_group_id),
       GroupSteppedTotals
       AS (SELECT bracket_category, 
                  bracket_label, 
                  winner_group_id, 
                  Group_Total_Profit, 
                  SUM(Group_Total_Profit) OVER(PARTITION BY bracket_category, 
                                                            bracket_label
                  ORDER BY winner_group_id ROWS BETWEEN 0 PRECEDING AND CURRENT ROW) AS Group_Total1_Profit, 
                  SUM(Group_Total_Profit) OVER(PARTITION BY bracket_category, 
                                                            bracket_label
                  ORDER BY winner_group_id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS Group_Total2_Profit, 
                  SUM(Group_Total_Profit) OVER(PARTITION BY bracket_category, 
                                                            bracket_label
                  ORDER BY winner_group_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Group_Total3_Profit, 
                  SUM(Group_Total_Profit) OVER(PARTITION BY bracket_category, 
                                                            bracket_label
                  ORDER BY winner_group_id ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS Group_Total5_Profit, 
                  SUM(Group_Total_Profit) OVER(PARTITION BY bracket_category, 
                                                            bracket_label
                  ORDER BY winner_group_id ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS Group_Total8_Profit
           FROM GroupTotals),
       RunningTotals
       AS (SELECT *, 
                  SUM(line_profit) OVER(PARTITION BY bracket_category, 
                                                     bracket_label, 
                                                     winner_group_id
                  ORDER BY Expr1003 DESC, 
                           RaceNo DESC, 
                           event_id DESC) - line_profit AS Group_Partial_Profit
           FROM WinnerGroups),
       LaggedProfits
       AS (SELECT RunningTotals.*, 
                  GroupSteppedTotals.Group_Total_Profit, 
                  LAG(RunningTotals.winner_group_id) OVER(PARTITION BY RunningTotals.bracket_category, 
                                                                       RunningTotals.bracket_label
                  ORDER BY RunningTotals.Expr1003, 
                           RunningTotals.RaceNo, 
                           RunningTotals.event_id) AS previous_winner_group_id, 
                  (GroupSteppedTotals.Group_Total1_Profit - RunningTotals.Group_Partial_Profit) AS profit_back1winner, 
                  (GroupSteppedTotals.Group_Total2_Profit - RunningTotals.Group_Partial_Profit) AS profit_back2winner, 
                  (GroupSteppedTotals.Group_Total3_Profit - RunningTotals.Group_Partial_Profit) AS profit_back3winner, 
                  (GroupSteppedTotals.Group_Total5_Profit - RunningTotals.Group_Partial_Profit) AS profit_back5winner, 
                  (GroupSteppedTotals.Group_Total8_Profit - RunningTotals.Group_Partial_Profit) AS profit_back8winner
           FROM RunningTotals
                INNER JOIN GroupSteppedTotals ON GroupSteppedTotals.bracket_category = RunningTotals.bracket_category
                                                 AND GroupSteppedTotals.bracket_label = RunningTotals.bracket_label
                                                 AND GroupSteppedTotals.winner_group_id = RunningTotals.winner_group_id)
       SELECT
       event_id, 
       MENU_HINT, 
       EVENT_NAME, 
       Expr1003, 
       Class, 
       RaceNo, 
       lp.bracket_category, 
       bracket_label, 
       --  winner_group_id, -- Debugging Column
  
       CountHorses_Winners, 
       SumPrices_Winners, 
       CountHorses_Losers, 
       SumPrices_Losers, 
       --  line_profit, -- Debugging Column
       --  Group_Partial_Profit, -- Debugging Column
       --  Group_Total_Profit, -- Debugging Column
  
       CASE WHEN CountHorses_Winners &gt; 0 AND previous_winner_group_id &gt;= 1 THEN profit_back1winner END AS Profit_Winner_1,
       CASE WHEN CountHorses_Winners &gt; 0 AND previous_winner_group_id &gt;= 2 THEN profit_back2winner END AS Profit_Winner_2,
       CASE WHEN CountHorses_Winners &gt; 0 AND previous_winner_group_id &gt;= 3 THEN profit_back3winner END AS Profit_Winner_3,
       CASE WHEN (previous_winner_group_id &gt;= 3 and CountHorses_Winners &lt;&gt; 0) THEN  cast(((profit_back1winner +  profit_back2winner +profit_back3winner)/3) as decimal(10,2)) END AS Average_Winner, 
       -- CASE WHEN CountHorses_Winners &gt; 0 AND previous_winner_group_id &gt;= 5 THEN profit_back5winner END   AS Profit_Winner_5,
       -- CASE WHEN CountHorses_Winners &gt; 0 AND previous_winner_group_id &gt;= 8 THEN profit_back8winner END   AS Profit_Winner_8,
  
       CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id &gt;= 1 AND CountHorses_Losers &gt; 0 THEN profit_back1winner END AS Profit_NotWinner_1,
       CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id &gt;= 2 AND CountHorses_Losers &gt; 0 THEN profit_back2winner END AS Profit_NotWinner_2,
       CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id &gt;= 3 AND CountHorses_Losers &gt; 0 THEN profit_back3winner END AS Profit_NotWinner_3,
       CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id &gt;= 3 AND CountHorses_Losers &gt; 0 THEN cast(((profit_back1winner +  profit_back2winner +profit_back3winner)/3) as decimal(10,2)) END AS Average_NotWinner
       -- CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id &gt;= 5 AND CountHorses_Losers &gt; 0 THEN profit_back5winner END   AS Profit_NotWinner_5,
       -- CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id &gt;= 8 AND CountHorses_Losers &gt; 0 THEN profit_back8winner END   AS Profit_NotWinner_8
       FROM LaggedProfits lp
  
  
       WHERE lp.bracket_category in ('All', 'Class', 'Days', 'Distance', 'Race', 'Runners', 'Total Bracket Count', 'Class : Days','Class : Distance','Class : Race', 'Class : Runners', 'Class : Total Bracket Count', 'Days : Distance', 'Days : Race', 'Days : Runners', 
                                  'Days : Total Bracket Count', 'Distance : Race', 'Distance : Runners', 'Distance : Total Bracket Count', 'Race : Runners', 'Race : Total Bracket Count', 'Runners : Total Bracket Count')
                                  --and bracket_label = '03-05 : All'
          -- WHERE --@Category IS NULL OR bracket_category IN (SELECT Value FROM dbo.FnSplit(@Category, ',')) and
      --event_id = 145688261
       --AND LaggedProfits.MENU_HINT  LIKE '%MVal%'
       --bracket_category = 'Runners2'    AND 
    --  and  bracket_label = '03-05 : Hcp : Wed-Sat'
       --LaggedProfits.MENU_HINT LIKE '%Flem%'
       ORDER BY Expr1003 DESC, 
                RaceNo DESC, 
                event_id DESC, 
                bracket_category, 
                bracket_label, 
                Class;
  </code></pre>
  
  <p>and this is an example of the table result, if you see, the "Bracket_Label"  for "01-02 : All" and "02-03 : All" does not appear.</p>
  
  <p><a href="https://i.stack.imgur.com/GXgij.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GXgij.png" alt="enter image description here"></a></p>