An aggregate may not appear in the WHERE clause

Question

 Select cusip
 from mhfspric
 where 
 PriceDate = case (Month(max(PriceDate)))
        when 1 
        Then '12-31-2014'
        when 2
        Then '12-31-2014'
        when 3
        then '12-31-2014'
        ELSE Null
    END

 group by cusip

I need to have a case statement to get the quarter ending dates for different months of pricedate.

select distinct c1 as Cusip,
       c2 as PriceDate,
       c3 as PriceToDate,
       c4 as PriceSource
from 
(select c8 as c1,
       c11 as c2,
       c10 as c3,
       c9 as c4,
       c7 as c5
from 
(select T1."Cusip" as c6,
       MAX(T1."PriceSource") as c7

      from "mhfspric" T1
where T1.PriceDate = (select case (Month(max(T2.PriceDate)))
        when 1 
        Then '12-31-2015'
        when 2
        Then '12-31-2015'
        when 3
        then '12-31-2015'
        when 4
        then '03-31-2015'
        when 5
        then '03-31-2015'
        when 6
        then '03-31-2015'
        when 7
        then '06-30-2015'
        when 8
        then '06-30-2015'
        when 9
        then '06-30-2015'
        when 10
        then '09-30-2015'
        when 11
        then '09-30-2015'
        when 12
        then '09-30-2015'
        ELSE Null
    END
    from mhfspric T2)and T1."PriceSource" <> 91
group by T1."Cusip"

) D3,
(select T1."Cusip" as c8,
       T1."PriceSource" as c9,
       T1."PriceToDate" as c10,
       T1."PriceDate" as c11
from "mhfspric" T1
where T1.PriceDate = (select case (Month(max(T2.PriceDate)))
        when 1 
        Then '12-31-2014'
        when 2
        Then '12-31-2014'
        when 3
        then '12-31-2014'
        when 4
        then '03-31-2015'
        when 5
        then '03-31-2015'
        when 6
        then '03-31-2015'
        when 7
        then '06-30-2015'
        when 8
        then '06-30-2015'
        when 9
        then '06-30-2015'
        when 10
        then '09-30-2015'
        when 11
        then '09-30-2015'
        when 12
        then '09-30-2015'
        ELSE Null
    END
    from mhfspric T2)and T1."PriceSource" <> 91
) D2
where ((c8 = c6) or ((c8 IS NULL) and (c6 IS NULL)))
) D1
where (c4 = c5)
)

This is the entire sql. I need to get the max price date and if it falls in the first 3 months of a year then if has to the previous quarter ending. For ex if max pricedate is 04-13-2015 then the pricedate should be 03-31-2015. How do I make this code generic instead of hard coding for each year.??

Please help. Thank you

I don't understand what you are trying to do here. Are you trying to return a list of PriceDates with the corresponding quarter end dates for each? If so, then you need to get rid of max(PriceDate). It is not used correctly anyway. Look at some examples of how to do case statements [like this](http://stackoverflow.com/questions/8785209/case-statement-within-where-clause-in-sql-server-2008) — jzapata, Apr 20 '15 at 19:48
You have move aggregates from `where` to `having`. But you seem to be comparing `PriceDate` with the aggregate of `PriceDate` which wont work. — Magnus, Apr 20 '15 at 20:01
It's not clear what you want. You can move your case statement out of the WHERE clause into a HAVING clause, but that's assuming that you want your Month(max(PriceDate)) to be evaluated for each cusip group (not across all of your data). — Mark Leiber, Apr 20 '15 at 20:02

score 0 · Answer 1 · answered Apr 22 '15 at 18:07

Here is a SQLFiddle: http://sqlfiddle.com/#!6/57f6f/33

Set up data:

create table mhfspric (
  cusip numeric,
  PriceDate date,
  PriceSource numeric
  );

/* has max value in first quarter */
insert into mhfspric values (1,'2015-01-10',90);
insert into mhfspric values (1,'2015-01-12',90);

/* has max value in second quarter */
insert into mhfspric values (2,'2015-02-13',90);
insert into mhfspric values (2,'2015-04-02',90);

/* has max value in third quarter */
insert into mhfspric values (3,'2015-08-12',90);
insert into mhfspric values (3,'2015-09-05',90);

/* has max value in fourth quarter */
insert into mhfspric values (4,'2015-11-12',90);
insert into mhfspric values (4,'2015-12-23',90);

Query:

select cusip
, max(PriceDate) as maxPriceDate
, convert(date, 
    DATEADD(s,-1,
      DATEADD(mm, 3*(month(max(PriceDate)) / 4), 
        DATEADD(yy, DATEDIFF(yy,0,max(PriceDate)), 0)
      )
    )
  ) as quarterEndDate
from mhfspric
group by cusip;

Here's a breakdown of what the query does.

DATEADD(yy, DATEDIFF(yy,0,max(PriceDate)), 0)

The above part of the query is getting the first day of the year for the given max(PriceDate). So it will return 2015-01-01, for example.

DATEADD(mm, 3*(month(max(PriceDate)) / 4),...

If we take the max(PriceDate) and divide by 4, we'll get a number between 0 and 3. We then add 3 times that number of months to 2015-01-01, so we end up with 2015-01-01 (0 months added), 2015-04-01 (3 months added), 2015-07-01 (6 months added), and 2015-10-01 (9 months added).

DATEADD(s,-1,...

Now we take the above result and subtract 1 second, which changes each value to the previous month (2015-01-01 changes to December 2014, 2015-04-01 changes to March 2015, etc.).

Finally we run convert(date,...) on the above to remove the time portion.

The result looks like:

cusip   maxPriceDate    quarterEndDate
1       2015-01-12      2014-12-31
2       2015-04-02      2015-03-31
3       2015-09-05      2015-06-30
4       2015-12-23      2015-09-30

An aggregate may not appear in the WHERE clause

1 Answers1