3

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.

3

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.

  • Do you mean that the really_bad_idea CTE is the non-SARGable, or your first example? – Jeff.Clark May 23 '17 at 16:50
  • @sp_BlitzErik, regarding the non-SARGable part of your answer; what would suggestion be? Using BETWEEN instead of the 2 OR's? – Jason May 23 '17 at 16:51
  • 1
    @Jeff.Clark both are non-SARGable. Neither is a good idea, performance-wise. – Erik Darling May 23 '17 at 16:52
  • @Jason no, BETWEEN/OR won't make a lick of difference. – Erik Darling May 23 '17 at 16:53
  • Really then, the business requirement to have it all done within one view is creating the performance hit? – Jeff.Clark May 23 '17 at 16:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.