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.