I have a query that displays a count of wells for the current year. I'd like to show the count/year for the past 5 years. How could I do this in a single query?

For the current year, I am using YEAR(GETDATE()). For the past five years, I was thinking I could use: YEAR(GETDATE())-1, YEAR(GETDATE())-2, YEAR(GETDATE())-23, etc. I also thought a CASE WHEN clause could work but wasn't sure how to use it under the SELECT statement.

Here's my current query:

SELECT
    COALESCE(w.WellType, 'Totals') AS 'WellTypes',
    COUNT(DISTINCT(w.WellID)) AS '2021'
FROM Well w
    LEFT JOIN Construct c ON c.WellKey = w.PKey
    LEFT JOIN ConstructDate cd ON c.PKey = cd.ConstructKey
WHERE
    YEAR(cd.EventDate) = YEAR(GETDATE())
    AND cd.Event = 'LATERALSTATUS'
    AND cd.Comment = 'PA'
GROUP BY ROLLUP(w.WellType)
ORDER BY
    CASE w.WellType
        WHEN 'OW' THEN 1
        WHEN 'GW' THEN 2
        WHEN 'D' THEN 3
        WHEN 'OWI' THEN 4
        WHEN 'WI' THEN 5
    END DESC

Here are my current results:

This is what I'd like to get:

I'm working on getting this in db-fiddle but I've never used it before. I know this is super long but for now, here's the plain data in a table. The "EventDate", "Comment", and "Event" columns are from a table called, "ConstructDate". The "WellType" column is from a table called, "Well".

CodePudding user response：

It's tricky when you need different constraints to aggregate what you want. I wouldn't use count, I would sum the instances instead with CASE statements. Here is an example you can run in your session on SQL Server:

IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP(
    WellType            NVARCHAR(10)
    ,EventDate          DATE
)
INSERT INTO #TEMP (WellType, EventDate)
VALUES ('OW','2021-11-03')
    ,('GW','2020-11-03')
    ,('D','2019-11-03')
    ,('OWI','2018-11-03')
    ,('WI','2017-11-03')
    ,('WI','2021-11-03')
    ,('D','2020-11-03')
    ,('D','2019-11-03')
    ,('GW','2018-11-03')
    ,('OW','2017-11-03')
    ,('OW','2021-11-03')
    ,('GW','2020-11-03')
    ,('D','2019-11-03')
    ,('OWI','2018-11-03')
    ,('WI','2017-11-03')
    ,('WI','2021-11-03')
    ,('D','2020-11-03')
    ,('D','2019-11-03')
    ,('GW','2018-11-03')
    ,('OW','2017-11-03')

SELECT 
    WellType
    ,SUM(CASE WHEN YEAR(EventDate) = YEAR(GETDATE()) THEN 1 ELSE 0 END) [THIS YEAR]
    ,SUM(CASE WHEN YEAR(EventDate) = YEAR(DATEADD(YEAR,-1,GETDATE())) THEN 1 ELSE 0 END) [LAST YEAR]
    ,SUM(CASE WHEN YEAR(EventDate) = YEAR(DATEADD(YEAR,-2,GETDATE())) THEN 1 ELSE 0 END) [2 YEARS AGO]
    ,SUM(CASE WHEN YEAR(EventDate) = YEAR(DATEADD(YEAR,-3,GETDATE())) THEN 1 ELSE 0 END) [3 YEARS AGO]
FROM #TEMP
GROUP BY WellType

I also use the GETDATE() function to establish what year to sum. This should do what you want.

OUTPUT BEFORE AND AFTER SELECT STATEMENT:

CodePudding user response：

I can't reproduce your own query without the tables involved and relevant data, however there are a number of ways of pivoting the data you require into columns.

One such method is to use a conditional case expression, another is to use window analytic functions:

select distinct welltype, 
    Count(case when Year(Eventdate)=Year(GetDate())-0 then 1 end) over(partition by welltype ) [2021],
    Count(case when Year(Eventdate)=Year(GetDate())-1 then 1 end) over(partition by welltype ) [2020],
    Count(case when Year(Eventdate)=Year(GetDate())-2 then 1 end) over(partition by welltype ) [2019],
    Count(case when Year(Eventdate)=Year(GetDate())-3 then 1 end) over(partition by welltype ) [2018],
    Count(case when Year(Eventdate)=Year(GetDate())-4 then 1 end) over(partition by welltype ) [2017]
from t 

Demo Fiddle

CodePudding user response：

Unfortunately, I can't verify the request without data. I would try subquery with "Grouping Set" operator in combination with "Case When" structure and then use "SUM" aggregate function.

With Cte As (Select YEAR(GetDate()) As [YEAR], YEAR(GetDate())-4 As [first]             
             Union All 
             Select [YEAR]-1 As [YEAR], [first]
             From cte
             Where [YEAR] > [first])
Select WellTypes, SUM([2021]), SUM([2020]), SUM([2019]), SUM([2018]), SUM([2017])
From
(Select
    COALESCE(w.WellType, 'Totals') As WellTypes,
    Case When cte.[YEAR] = YEAR(GetDate()) Then COUNT(DISTINCT(w.WellID)) End As [2021],
    Case When cte.[YEAR] = Year(GetDate())-1 Then COUNT(DISTINCT(w.WellID)) End As [2020],
    Case When cte.[YEAR] = Year(GetDate())-2 Then COUNT(DISTINCT(w.WellID)) End As [2019],
    Case When cte.[YEAR] = Year(GetDate())-3 Then COUNT(DISTINCT(w.WellID)) End As [2018],
    Case When cte.[YEAR] = Year(GetDate())-4 Then COUNT(DISTINCT(w.WellID)) End As [2017]
From Well w
    Inner Join Construct c ON c.WellKey = w.PKey
    Inner Join ConstructDate cd ON c.PKey = cd.ConstructKey
    Inner Join cte On (YEAR(cd.EventDate)=cte.[YEAR])
Where
    And cd.Event = 'LATERALSTATUS'
    And cd.Comment = 'PA'
Group by Grouping Sets ((cte.[YEAR], w.WellType), (cte.[YEAR]))) As T
Group by WellTypes
Order by
    Case WellTypes
        When 'OW' Then 1
        When 'GW' Then 2
        When 'D' Then 3
        When 'OWI' Then 4
        When 'WI' Then 5
    End Desc