multiple criteria to case statment

[Ian]

See the SQL statement below. It is based on a table called datanorm. I have
another table called ToUSTART where the DATANORM.ID will join to the
TOUSTART.USERID with one(toustart) to many(datanorm) relationship. Within
TOUSTART there is a datefield called TOUSTART to compare to the fields
MONTH(DATEREAD) and YEAR(DATEREAD) in DATANORM.

I need to create a case statement where when DATANORM.ID = TOUSTART.USERID
and the MONTH and YEAR of TOUSTART.TOUSTART are the same as the DATANORM then
a new column returns the expression "transition". If TOUSTART.TOUSTART <
DATEREAD then "pretou" and when TOUSTART > DATEREAD then "post-TOU".

Any ideas?

SELECT DATANORM.ID, SUM(CONSUMPTION) AS MNTHCONSUMP,
SUM(CASE
WHEN PEAKTYPE = 1 THEN CONSUMPTION*ONRATE
WHEN PEAKTYPE = 2 THEN CONSUMPTION*MIDRATE
WHEN PEAKTYPE = 3 THEN CONSUMPTION*OFFRATE
ELSE 0 END) AS TOUREV, YEAR(dateread) AS YEAR, MONTH(DATEREAD) AS MONTH
FROM DATANORM INNER JOIN HOURIDDATA
ON (HOURIDDATA.POWERHOUR = DATANORM.TOD) AND (DATANORM.DATEREAD =
HOURIDDATA.POWERDATE)
GROUP BY DATANORM.ID, YEAR(DATEREAD), MONTH(DATEREAD)
ORDER BY ID, YEAR, MONTH

[Hugo Kornelis]

On Fri, 5 Mar 2010 16:32:48 -0800, Ian wrote:

>See the SQL statement below. It is based on a table called datanorm. I have
>another table called ToUSTART where the DATANORM.ID will join to the
>TOUSTART.USERID with one(toustart) to many(datanorm) relationship. Within
>TOUSTART there is a datefield called TOUSTART to compare to the fields
>MONTH(DATEREAD) and YEAR(DATEREAD) in DATANORM.
>
>I need to create a case statement where when DATANORM.ID = TOUSTART.USERID
>and the MONTH and YEAR of TOUSTART.TOUSTART are the same as the DATANORM then
>a new column returns the expression "transition". If TOUSTART.TOUSTART <
>DATEREAD then "pretou" and when TOUSTART > DATEREAD then "post-TOU".
>
>Any ideas?

Hi Ian,

Something like this?
CASE
WHEN DATANORM.ID = TOUSTART.USERID
AND MONTH(DATANORM.DATEREAD) = MONTH(TOUSTART.TOUSTART)
AND YEAR(DATANORM.DATEREAD) = YEAR(TOUSTART.TOUSTART))
THEN "transition"
WHEN TOUSTART.TOUSTART < DATANORM.DATEREAD
THEN "pretou"
WHEN TOUSTART.TOUSTART > DATANORM.DATEREAD
THEN "post-TOU"
END

(nitpicking: SQL does not have a CASE statement, but a CASE expression)

General readability tip - do not use ALL UPPERCASE for your table and
column names. That makes the code much harder to read.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis