SQL: Multiple Select Statements from Same View Grouped by Year

Question

I hope my description is clear. I have a single view that contains entries based on Level and Status. I wrote a query that counts each Level by its type and Status and then calculates the percent of the total of each Level within a given year using multiple Select statements in a single query. I based my query on this logic: Multiple select statements in a Single query.

My logic:

SELECT Table1.[Level Description]
     , Table1.[Level Status]
     , Table1.[Level Count]
     , Table2.[Level Total]
     , CAST(Table1.[Level Count] / Table2.[Level Total] AS DECIMAL(3, 2)) * 100 AS 'LVL %'
FROM
(
    SELECT LEVEL_DESC AS 'Level Description'
         , LEVEL_STATUS 'Level Status'
         , (CASE
                WHEN LEVEL_DESC IN ('LEVEL 1', 'LEVEL 2', 'LEVEL 3', 'LEVEL 4')
                     AND LEVEL_STATUS IN ('A', 'I', 'R') THEN
                    COUNT(LEVEL_STATUS) * 1.00
                ELSE
                    0
            END
           ) AS 'Level Count'
    FROM dbo.My_Level_View
    WHERE (DUE_DATE BETWEEN '2019-01-01' AND '2019-12-31')
          AND LEVEL_SUB = 'I'
    GROUP BY LEVEL_DESC
           , LEVEL_STATUS
) AS [Table1]
, (
      SELECT LEVEL_DESC AS 'Total Description'
           , (CASE
                  WHEN LEVEL_DESC IN ('LEVEL 1', 'LEVEL 2', 'LEVEL 3', 'LEVEL 4') THEN
                      COUNT(LEVEL_STATUS) * 1.00
                  ELSE
                      0
              END
             ) AS 'Level Total'
      FROM dbo.My_Level_View
      WHERE (DUE_DATE BETWEEN '2019-01-01' AND '2019-12-31')
            AND LEVEL_SUB = 'I'
      GROUP BY LEVEL_DESC
  ) AS [Table2]
WHERE Table1.[Level Description] = Table2.[Total Description];

The query works well and gives me the result I need:

However, I would like to extend it to work with any record within a two year window from today's date and group the results by year.

I have tried using

WHERE DATEDIFF(YEAR, DUE_DATE, GETDATE()) < 2

in place of the hard coded date range

WHERE (DUE_DATE BETWEEN '2019-01-01' AND '2019-12-31')

When I do this it counts each Level by it's Type within the the last two years and then performs the calculations. I would like to separate the results by year, e.g., 2019 and 2020 as shown in the image.

What I have scripted may not be the best solution. How can I make my query separate the results by year?

I wasn't very clear, sorry. I have tried to clarify my question. — RJH, Aug 13 '20 at 21:03
There is no year indicator in image. But why not simply add `Year(DUE_DATE)` in `GROUP BY` clause? — Parfait, Aug 13 '20 at 22:14
Thank you. I hadn't considered it. This gets me closer to where I want to be. I am also studying your answer below. The concept is a little over my head. — RJH, Aug 13 '20 at 22:45

Parfait · Accepted Answer · 2020-08-13T22:16:59.927

Consider using DATEADD(YEAR, -2, GETDATE()) for more precise date range since DATEDIFF(YEAR, DUE_DATE, GETDATE()) < 2 returns dates by year rounding. Plus, you should set an upper range to avoid dates after which DATEDIFF will return as negative. Also, use a single aggregate query with window function for total.

SELECT agg.[Year]
     , agg.[Level Description]
     , agg.[Level Status]
     , agg.[Level Count]
     , SUM(agg.[Level Count])  OVER (PARTITION BY agg.[Year]
                                                  , agg.[Level Description]
                                                  , agg.[Level Status]) AS [Level Total]
     , CAST(agg.[Level Count] / (SUM(agg.[Level Count]) 
                                 OVER (PARTITION BY agg.[Year]
                                                    , agg.[Level Description],
                                                    , agg.[Level Status]))
             AS DECIMAL(3, 2)) * 100 AS 'LVL %'
FROM
(
    SELECT YEAR(DUE_DATE) AS [Year]
         , LEVEL_DESC AS [Level Description]
         , LEVEL_STATUS AS [Level Status]
         , SUM(CASE
                   WHEN LEVEL_DESC IN ('LEVEL 1', 'LEVEL 2', 'LEVEL 3', 'LEVEL 4')
                        AND LEVEL_STATUS IN ('A', 'I', 'R') 
                   THEN 1.00
                   ELSE 0.00
               END) AS [Level Count]
    FROM dbo.My_Level_View
    WHERE DUE_DATE BETWEEN DATEADD(YEAR, -2, GET_DATE()) AND GET_DATE()
      AND LEVEL_SUB = 'I'
    GROUP BY YEAR(DUE_DATE)
           , LEVEL_DESC
           , LEVEL_STATUS
) AS agg

Should you need to supply any dynamic date, declare or pass parameter with a stored procedure:

DECLARE @paramDate datetime = '2020-01-01';
...    
     WHERE DUE_DATE BETWEEN DATEADD(YEAR, -2, @paramDate) AND @paramDate
...

Aside - best practice in SQL is to use explicit joins with multiple subqueries or tables and heed Bad Habits to Kick : Using table aliases like (a, b, c) or (t1, t2, t3).

Thank you, @Parfait. I was able to apply your answer with one tweak and make this work. — RJH, Aug 14 '20 at 00:51

SQL: Multiple Select Statements from Same View Grouped by Year

1 Answers1