How to SUM by date range within a view

I have a view in which I need to Group By or SUM OVER Partition By year and quarter, but the year and quarter will be computed within the view. I would like to keep it all within a view instead of needing tables and SPs.

The goal is to get SUMs by of all records within the first quarter of a year, spanning over multiple years. What I have is:

SELECT MIN(PAYROLL)                     AS MinPayroll
      ,MAX(PAYROLL)                     AS MaxPayroll
      ,YEAR(CHECK_DATE)                 AS PayrollYear
      ,MONTH(CHECK_DATE)                AS PayrollMonth
      ,SUM(sit)                         AS TotalStateTax
      ,SUM(FIT)                         AS TotalFedTax
...
...
FROM CHECKS 
WHERE (PayrollMonth = 01 OR PayrollMonth = 02 OR PayrollMonth = 03)
GROUP BY PayrollYear

This gives a good idea of what I want to do, but SSMS doesn't like it, saying 'Invalid column name 'PayrollMonth', and 'Invalid Column name 'PayrollYear'

I'll start working on sample data to help, but I wanted to get this out now in case someone knew what to do off the top of their heads without data.

You can't use columns aliased in the SELECT list directly in the WHERE clause. See blog post here. Full disclosure; I wrote it.

You'd have to do this. Note that leading zeroes are discarded for integers.

SELECT MIN(PAYROLL)                     AS MinPayroll
      ,MAX(PAYROLL)                     AS MaxPayroll
      ,YEAR(CHECK_DATE)                 AS PayrollYear
      ,MONTH(CHECK_DATE)                AS PayrollMonth
      ,SUM(sit)                         AS TotalStateTax
      ,SUM(FIT)                         AS TotalFedTax
...
...
FROM CHECKS 
WHERE MONTH(CHECK_DATE) = 1 OR MONTH(CHECK_DATE) = 2 OR MONTH(CHECK_DATE) = 3
GROUP BY YEAR(CHECK_DATE);

Or use a CTE

WITH really_bad_idea AS (
SELECT MIN(PAYROLL)                     AS MinPayroll
      ,MAX(PAYROLL)                     AS MaxPayroll
      ,YEAR(CHECK_DATE)                 AS PayrollYear
      ,MONTH(CHECK_DATE)                AS PayrollMonth
      ,SUM(sit)                         AS TotalStateTax
      ,SUM(FIT)                         AS TotalFedTax
...
...
FROM CHECKS 
GROUP BY YEAR(CHECK_DATE)
)
SELECT *
FROM really_bad_dea
WHERE (PayrollMonth = 1 OR PayrollMonth = 2 OR PayrollMonth = 3);

Though I would ask very nicely that you not write non-SARGable WHERE clauses like that, because they are performance nightmares.

Also, it's doubltful the query will compile with just that column in the GROUP BY.

StackExchange Database Administrators Q#174350, answer score: 3