0

I'm trying to use the MAX() function as a subquery to filter results of my greater query by the last couple of whole years. I realized this was a little bit more complicated than what I initially thought it would need so I created a much simpler query to test out what I would need to do with the HAVING clause in order to get it to work.

I've checked these two posts but it didn't really help (SQL, HAVING clause explained, SQL: HAVING clause)

I don't think my understanding of the HAVING clause is correct because I'm not sure why it's not working. Would someone be able to help and explain?

Note: [Fiscal Year] is NVARCHAR so I figured converting it to INT might work.

SELECT DISTINCT

D.[FISCAL YEAR]

FROM [Dates] AS D

GROUP BY D.[Fiscal Year]

HAVING CONVERT(INT,D.[Fiscal Year]) >= MAX(CONVERT(INT,D.[FISCAL YEAR])) -2

These are my results:

(No column name)
2015
2014
2013
2012
2016

These are the results I should get:

(No column name)
2015
2014
2016
Community
  • 1
  • 1
plankton
  • 369
  • 5
  • 21

2 Answers2

2

I think this could be done better in the WHERE clause. This solution isn't optimal, but here's what you can do.

SELECT 
      DISTINCT D.[FISCAL YEAR]
FROM [Dates] AS D
      WHERE CONVERT(INT,d.[Fiscal Year]) 
             >= (SELECT MAX(CONVERT(INT,[Fiscal Year])) -2 from Dates)
Arthur D
  • 592
  • 4
  • 10
1

The problem you are having is you are grouping by the year, thus the max will always be the same as the year for that group.

Here's one option using window functions:

select distinct fiscalyear 
from (
    select fiscalyear, max(fiscalyear) over () maxfiscalyear
    from dates 
) t 
where fiscalyear >= maxfiscalyear - 2

I don't see the need to use the convert function here either, but if you need to, you can add it back.

sgeddes
  • 62,311
  • 6
  • 61
  • 83