I have the following two tables
dimDate dd
----------------------------------------------------------------------
| dimDate dimYear dimMonth dimQuarter dimWeekDayNumber |
----------------------------------------------------------------------
| 2019-12-01 2019 12 4 5 |
| . . . . . |
| . . . . . |
| . . . . . |
| 2021-12-10 2021 12 4 5 |
| 2021-12-11 2021 12 4 6 |
| 2021-12-12 2021 12 4 7 |
| 2021-12-13 2021 12 4 1 |
----------------------------------------------------------------------
Goal Table pg
-------------------------------------------------
| location goal startdate enddate ptype |
-------------------------------------------------
| A 600000 2019-01-01 2019-12-31 CONN |
| B 400000 2019-01-01 2020-10-01 CONN |
| C 600000 2019-01-01 NULL CONN |
| D 450000 2019-01-01 NULL CONN |
| A 500000 2020-01-01 NULL CONN |
| B 500000 2020-10-02 2021-03-15 CONN |
| B 600000 2021-03-16 NULL CONN |
-------------------------------------------------
Desired output:
-----------------------------------------------------------------------------------------------------------
| dimDate dimYear dimMonth dimQuarter dimWeekDayNumber A B C D |
-----------------------------------------------------------------------------------------------------------
| 2019-12-01 2019 12 4 7 600000 400000 600000 450000 |
| . . . . . . . . . |
| . . . . . . . . . |
| . . . . . . . . . |
| 2021-12-10 2021 12 4 5 500000 600000 600000 450000 |
| 2021-12-11 2021 12 4 6 500000 600000 600000 450000 |
| 2021-12-12 2021 12 4 7 500000 600000 600000 450000 |
| 2021-12-13 2021 12 4 1 500000 600000 600000 450000 |
-----------------------------------------------------------------------------------------------------------
I've been trying the SQL pivot operator, but it wants to aggregate, and I've tried using cases but it pulls back four of the same date since I have four locations, with only one location with the goal per row. Any help would be appreciated.
CodePudding user response：
This is the best I've come up with so far. I don't like it because it isn't dynamic. I must type in each location explicitly. Suggestions always welcome.
SELECT dd.*,
(SELECT goal from mdp.ProductionGoals where dd.dimDate between startdate and isnull(enddate, getdate()) and [location] = 'A') AS 'A' ,
(SELECT goal from mdp.ProductionGoals where dd.dimDate between startdate and isnull(enddate, getdate()) and [location] = 'B') AS 'B' ,
(SELECT goal from mdp.ProductionGoals where dd.dimDate between startdate and isnull(enddate, getdate()) and [location] = 'C') AS 'C' ,
(SELECT goal from mdp.ProductionGoals where dd.dimDate between startdate and isnull(enddate, getdate()) and [location] = 'D') AS 'D'
FROM mdp.dimDate dd
left outer join mdp.ProductionGoals pg
on dd.dimdate between startdate and isnull(enddate, getdate())
where dd.dimDate between '2019-01-01' and getdate()
------------------------------------------------------------------------------------------------
|dimDate dimYear dimMonth dimQuarter dimWeekDayNumber A B C D |
------------------------------------------------------------------------------------------------
|2019-12-01 2019 12 4 7 600000 400000 600000 450000 |
|. . . . . . . . . |
|. . . . . . . . . |
|. . . . . . . . . |
|2021-12-10 2021 12 4 5 500000 600000 600000 450000 |
|2021-12-11 2021 12 4 6 500000 600000 600000 450000 |
|2021-12-12 2021 12 4 7 500000 600000 600000 450000 |
|2021-12-13 2021 12 4 1 500000 600000 600000 450000 |
------------------------------------------------------------------------------------------------
CodePudding user response：
You don't need subqueries, you can do this with conditional aggregation. PIVOT is a type of conditional aggregation, but you can also use SUM or MAX with a CASE expression, which is far more flexible
SELECT
dd.dimDate,
dd.dimYear,
dd.dimMonth,
dd.dimQuarter,
dd.dimWeekDayNumber,
SUM(CASE WHEN pg.location = 'A' THEN pg.goal END) AS [A],
SUM(CASE WHEN pg.location = 'B' THEN pg.goal END) AS [B],
SUM(CASE WHEN pg.location = 'C' THEN pg.goal END) AS [C],
SUM(CASE WHEN pg.location = 'D' THEN pg.goal END) AS [D]
FROM mdp.dimDate dd
left outer join mdp.ProductionGoals pg
on dd.dimdate between pg.startdate and isnull(pg.enddate, getdate())
where dd.dimDate between '20190101' and getdate()
GROUP BY
dd.dimDate,
dd.dimYear,
dd.dimMonth,
dd.dimQuarter,
dd.dimWeekDayNumber;
If you want a dynamic solution, I would recommend not going down the full dynamic SQL route. Instead, you can select the top four results using ROW_NUMBER, DENSE_RANK, or other windowing function. I don't know what criteria you would use, but for example the top four locations by total goal:
SELECT
dd.dimDate,
dd.dimYear,
dd.dimMonth,
dd.dimQuarter,
dd.dimWeekDayNumber,
SUM(CASE WHEN pg.rn = 1 THEN pg.goal END) AS [1],
SUM(CASE WHEN pg.rn = 2 THEN pg.goal END) AS [2],
SUM(CASE WHEN pg.rn = 3 THEN pg.goal END) AS [3],
SUM(CASE WHEN pg.rn = 4 THEN pg.goal END) AS [4]
FROM mdp.dimDate dd
left outer join (
SELECT *,
rn = DENSE_RANK() OVER (ORDER BY TotalGoal)
FROM (
SELECT *,
SUM(goal) OVER (PARTITION BY pg.location) AS TotalGoal
FROM mdp.ProductionGoals pg
) pg
WHERE rn <= 4
) pg on dd.dimdate between pg.startdate and isnull(pg.enddate, getdate())
where dd.dimDate between '20190101' and getdate()
GROUP BY
dd.dimDate,
dd.dimYear,
dd.dimMonth,
dd.dimQuarter,
dd.dimWeekDayNumber;