sqlsql-serverdimensional-modeling
Use dimension table for data combination not existing in main table
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:
My main table has the following data:
table1
╔════╦═══════╦═════════╦═══════╗
║ 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 ║
╚════╩═══════╩═════════╩═══════╝
and I have the code:
select id,
CASE
when price <= 2 THEN '01-02'
when price <= 3 THEN '03-04'
when price <= 4 THEN '05-05'
when price <= 5 THEN '05-06'
...ETC
END as price,
Item_id,
name
from table1
Now, from the data I have, in this case there is no price "when price <= 2 THEN '01-02'" so that option doesn't appear in the final report, however I would like for it to appear, with a 0.
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.
I'm not sure if it would be a table with all the combinations of what I want to show, like "'01-02'" and then join with it, but that doesn't seem to work.
This is the code from the SP:
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 <> '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 <= 2 THEN '01-02'
WHEN td.BSP <= 3 THEN '02-03'
WHEN td.BSP <= 5 THEN '03-05'
WHEN td.BSP <= 8 THEN '05-08'
WHEN td.BSP <= 13 THEN '08-13'
WHEN td.BSP <= 21 THEN '13-21'
WHEN td.BSP <= 34 THEN '21-34'
WHEN td.BSP <= 55 THEN '34-55'
WHEN td.BSP <= 89 THEN '55-89'
WHEN td.BSP <= 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 <= 7 THEN '01-07'
WHEN td.RaceRunners <= 12 THEN '07-12'
WHEN td.RaceRunners <= 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 > 3 THEN '04-1000'
END AS label
UNION ALL
SELECT 'Distance' AS category,
CASE
WHEN td.distance <= 1200 THEN '0-1200'
WHEN td.distance <= 1600 THEN '1200-1600'
WHEN td.distance <= 10000 THEN '1600-10000'
END AS label
UNION ALL
SELECT 'Race' AS category,
CASE
WHEN td.raceno < 5 THEN '0-4'
WHEN td.raceno < 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 <= 7 THEN '01-07'
WHEN td.RaceRunners <= 12 THEN '07-12'
WHEN td.RaceRunners <= 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 > 3 THEN '04-1000'
END AS label
UNION ALL
SELECT 'Distance' AS category,
CASE
WHEN td.distance <= 1200 THEN '0-1200'
WHEN td.distance <= 1600 THEN '1200-1600'
WHEN td.distance <= 10000 THEN '1600-10000'
END AS label
UNION ALL
SELECT 'Race' AS category,
CASE
WHEN td.raceno < 5 THEN '0-4'
WHEN td.raceno < 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 > 0 AND previous_winner_group_id >= 1 THEN profit_back1winner END AS Profit_Winner_1,
CASE WHEN CountHorses_Winners > 0 AND previous_winner_group_id >= 2 THEN profit_back2winner END AS Profit_Winner_2,
CASE WHEN CountHorses_Winners > 0 AND previous_winner_group_id >= 3 THEN profit_back3winner END AS Profit_Winner_3,
CASE WHEN (previous_winner_group_id >= 3 and CountHorses_Winners <> 0) THEN cast(((profit_back1winner + profit_back2winner +profit_back3winner)/3) as decimal(10,2)) END AS Average_Winner,
-- CASE WHEN CountHorses_Winners > 0 AND previous_winner_group_id >= 5 THEN profit_back5winner END AS Profit_Winner_5,
-- CASE WHEN CountHorses_Winners > 0 AND previous_winner_group_id >= 8 THEN profit_back8winner END AS Profit_Winner_8,
CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id >= 1 AND CountHorses_Losers > 0 THEN profit_back1winner END AS Profit_NotWinner_1,
CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id >= 2 AND CountHorses_Losers > 0 THEN profit_back2winner END AS Profit_NotWinner_2,
CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id >= 3 AND CountHorses_Losers > 0 THEN profit_back3winner END AS Profit_NotWinner_3,
CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id >= 3 AND CountHorses_Losers > 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 >= 5 AND CountHorses_Losers > 0 THEN profit_back5winner END AS Profit_NotWinner_5,
-- CASE WHEN CountHorses_Winners = 0 AND previous_winner_group_id >= 8 AND CountHorses_Losers > 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;
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.
Solution
You would use a left join. I would recommend:
select v.price, t1.Item_id, t1.name
from (values (0, 2, '01-02'),
(2, 4, '03-04'),
. . .
) v(lo, hi, price) left join
table1 t1
on t1.lo > v.lo and t1.hi <= v.hi;
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?