I put together a query that displays the number of wells/year and sums their totals in a column called, "Totals". Please see the db<>fiddle link at the bottom of the post for the working query.
SELECT
YEAR(wd.eventdate) AS [Year],
ISNULL(COUNT(DISTINCT w_oil.WellID), 0) AS [Oil Wells],
ISNULL(COUNT(DISTINCT w_gas.WellID), 0) AS [Gas Wells],
ISNULL(COUNT(DISTINCT w_service.WellID), 0) AS [Service Wells],
ISNULL(COUNT(DISTINCT w_dry.WellID), 0) AS [Dry Holes],
ISNULL(COUNT(DISTINCT w_tot.WellID), 0) AS [Totals]
FROM HWellDate wd
LEFT JOIN HWell w_oil ON (w_oil.PKey = wd.WellKey and w_oil.WellType = 'OW')
LEFT JOIN HWell w_gas ON (w_gas.PKey = wd.WellKey and w_gas.WellType = 'GW')
LEFT JOIN HWell w_service ON
(
w_service.PKey = wd.WellKey
AND w_service.WellType IN ('WI','GI','GS','WD','WS','TW')
)
LEFT JOIN HWell w_dry ON (w_dry.PKey = wd.WellKey and w_dry.WellType = 'D')
LEFT JOIN HWell w_tot ON w_tot.PKey = wd.WellKey
WHERE wd.comment = 'PA'
AND wd.event = 'WELLSTATUS'
AND (YEAR(wd.eventdate) >= (YEAR(GETDATE()) - 4) AND YEAR(wd.eventdate) <= YEAR(GETDATE()))
GROUP BY YEAR(wd.eventdate)
The query works but I'd like to transpose the table so the years are listed at the top as column names and the totals column is a row at the bottom.
Here is the table that is produced from the query above:
Year | Oil Wells | Gas Wells | Service Wells | Dry Holes | Totals |
---|---|---|---|---|---|
2017 | 6 | 7 | 1 | 1 | 15 |
2018 | 15 | 23 | 5 | 6 | 49 |
2019 | 7 | 6 | 4 | 5 | 22 |
2020 | 10 | 16 | 4 | 0 | 30 |
2021 | 24 | 23 | 3 | 3 | 53 |
Here is what I'd like to have:
Well Types | 2021 | 2020 | 2019 | 2018 | 2017 |
---|---|---|---|---|---|
Oil Wells | 24 | 10 | 7 | 15 | 6 |
Gas Wells | 23 | 16 | 6 | 23 | 7 |
Service Wells | 3 | 4 | 4 | 5 | 1 |
Dry Holes | 3 | 0 | 5 | 6 | 1 |
Totals | 53 | 30 | 22 | 49 | 15 |
I think I need to use PIVOT
to rotate the table but suspect I may need to use UNPIVOT
as well to get the results I'm looking for. I was thinking I could insert the data from the first table into a temp table called, "#wellsPluggedTempTbl". After that, maybe I could use dynamic sql to generate the results.
Here's what I have so far:
DECLARE @colsPivot AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
INSERT INTO #wellsPluggedTempTbl([Year], [Oil Wells], [Gas Wells], [Service Wells], [Dry Holes], Totals)
SELECT
YEAR(wd.eventdate) AS [Year],
ISNULL(COUNT(DISTINCT w_oil.WellID), 0) AS [Oil Wells],
ISNULL(COUNT(DISTINCT w_gas.WellID), 0) AS [Gas Wells],
ISNULL(COUNT(DISTINCT w_service.WellID), 0) AS [Service Wells],
ISNULL(COUNT(DISTINCT w_dry.WellID), 0) AS [Dry Holes],
ISNULL(COUNT(DISTINCT w_tot.WellID), 0) AS [Totals]
FROM HWellDate wd
LEFT JOIN HWell w_oil ON (w_oil.PKey = wd.WellKey and w_oil.WellType = 'OW')
LEFT JOIN HWell w_gas ON (w_gas.PKey = wd.WellKey and w_gas.WellType = 'GW')
LEFT JOIN HWell w_service ON
(
w_service.PKey = wd.WellKey
AND w_service.WellType IN ('WI','GI','GS','WD','WS','TW')
)
LEFT JOIN HWell w_dry ON (w_dry.PKey = wd.WellKey and w_dry.WellType = 'D')
LEFT JOIN HWell w_tot ON w_tot.PKey = wd.WellKey
WHERE wd.comment = 'PA'
AND wd.event = 'WELLSTATUS'
AND (YEAR(wd.eventdate) >= (YEAR(GETDATE()) - 4) AND YEAR(wd.eventdate) <= YEAR(GETDATE()))
GROUP BY YEAR(wd.eventdate)
However, I get this error after running the above: "Invalid object name '#wellsPluggedTempTbl'.
For the well type ordering at the end, I know I need to use a CASE WHEN
statement like this:
ORDER BY
CASE WellType
WHEN 'Totals' THEN 5
WHEN 'Dry Holes' THEN 4
WHEN 'Service Wells' THEN 3
WHEN 'Gas Wells' THEN 2
WHEN 'Oil Wells' THEN 1
END
Here is a link to db<>fiddle where I have a sampling of the data that produces the results in this post. Any help would be appreciated! Thanks.
CodePudding user response:
You are approaching this wrong. Instead of pivoting your existing query you should be just using conditional aggregation.
Note other efficiencies
- No need to join multiple times. Just join once and use
CASE
expressions - Note the use of
CROSS APPLY (VALUES
to create and re-use the grouping expression - Use
GROUPING SETS
orROLLUP
to get the totals row, use theGROUPING()
function to identify that row - Do not use functions on columns in the
WHERE
, instead create the date range you need and filter on that. - If you need dynamic years, instead of using dynamic SQL, just call the columns
ThisYear
LastYear
etc
SELECT
WellType = CASE WHEN GROUPING(v.WellType) = 0 THEN v.WellType ELSE 'Totals' END,
[2021] = COUNT(CASE WHEN [Year] = 2021 THEN 1 END),
[2020] = COUNT(CASE WHEN [Year] = 2020 THEN 1 END),
[2019] = COUNT(CASE WHEN [Year] = 2019 THEN 1 END),
[2018] = COUNT(CASE WHEN [Year] = 2018 THEN 1 END),
[2017] = COUNT(CASE WHEN [Year] = 2017 THEN 1 END)
FROM HWellDate wd
JOIN HWell w ON w.PKey = wd.WellKey
CROSS APPLY (VALUES(
CASE WHEN w.WellType = 'OW' THEN 'Oil Wells'
WHEN w.WellType = 'GW' THEN 'Gas Wells'
WHEN w.WellType IN ('WI','GI','GS','WD','WS','TW') THEN 'Service Wells'
WHEN w.WellType = 'D' THEN 'Dry Holes'
END,
YEAR(wd.eventdate)
)) v(WellType, Year)
WHERE wd.comment = 'PA'
AND wd.event = 'WELLSTATUS'
AND wd.eventdate >= DATEFROMPARTS(YEAR(GETDATE()) - 4, 1, 1)
AND wd.eventdate < DATEFROMPARTS(YEAR(GETDATE()) 1, 1, 1)
AND w.WellType IN ('OW','GW','WI','GI','GS','WD','WS','TW','D')
GROUP BY GROUPING SETS (
(v.WellType),
()
)
ORDER BY GROUPING(v.WellType) DESC,
CASE v.WellType
WHEN 'Dry Holes' THEN 4
WHEN 'Service Wells' THEN 3
WHEN 'Gas Wells' THEN 2
WHEN 'Oil Wells' THEN 1
END
;
CodePudding user response:
If you first group by the well type and the year, then it becomes easier to pivot in the outer query.
Since the Hole Category can already be calculated in the sub-query.
And by grouping with a rollup and conditional sums, it'll also gets the totals.
SELECT ISNULL(q.HoleCategory, 'Total') AS WellType
, ISNULL(SUM(CASE WHEN q.eventYear=2021 THEN q.Total END),0) AS [2021]
, ISNULL(SUM(CASE WHEN q.eventYear=2020 THEN q.Total END),0) AS [2020]
, ISNULL(SUM(CASE WHEN q.eventYear=2019 THEN q.Total END),0) AS [2019]
, ISNULL(SUM(CASE WHEN q.eventYear=2018 THEN q.Total END),0) AS [2018]
, ISNULL(SUM(CASE WHEN q.eventYear=2017 THEN q.Total END),0) AS [2017]
FROM
(
SELECT w.WellType
, YEAR(wd.eventDate) AS eventYear
,CASE
WHEN w.WellType = 'OW' THEN 'Oil Wells'
WHEN w.WellType = 'GW' THEN 'Gas Wells'
WHEN w.WellType IN ('WI','GI','GS','WD','WS','TW') THEN 'Service Wells'
WHEN w.WellType = 'D' THEN 'Dry Holes'
END AS HoleCategory
, COUNT(DISTINCT w.WellID) AS Total
FROM HWellDate wd
LEFT JOIN HWell w ON w.PKey = wd.WellKey
WHERE wd.comment = 'PA'
AND wd.event = 'WELLSTATUS'
AND w.WellType IS NOT NULL
AND YEAR(wd.eventdate) BETWEEN 2017 AND 2021
GROUP BY w.WellType, YEAR(wd.eventDate)
) q
GROUP BY ROLLUP(q.HoleCategory)
ORDER BY
CASE q.HoleCategory
WHEN 'Oil Wells' THEN 1
WHEN 'Gas Wells' THEN 2
WHEN 'Service Wells' THEN 3
WHEN 'Dry Holes' THEN 4
ELSE 9
END