CASE Help

[trembita]

I am trying to put a case statement into my Having Clause or Where Clause. Both come up with the same error "Error near '='" in the THEN statement. I'm not sure why this is not working. The idea of this statement is to look at the date and if it is the first of the month it will return last months data, if not it gives me this months data.
If someone could give me and idea as to why this doesn't work or another solution that would be great. Thanks

SELECT TOP 100 PERCENT database.Point_ time
FROM database
WHERE CASE WHEN datepart(dd, getdate())=1
THEN(MONTH(data base.Point_time ) = MONTH(getdate() )-1) AND
(YEAR(database. Point_time) = YEAR(getdate()) )
ELSE (MONTH(database .Point_time) = MONTH(getdate() )) AND
(YEAR(database. Point_time) = YEAR(getdate()) )
END
GROUP BY database.Point_ time
ORDER BY database.Point_ time.

[scripto]

SELECT TOP 100 PERCENT
CASE
WHEN datepart(dd, getdate()) = 1 THEN MONTH(databasex .Point_time)-1
ELSE MONTH(databasex .Point_time)
END, (YEAR(databasex .Point_time))

FROM databasex

GROUP BY databasex.Point _time
ORDER BY databasex.Point _time

[trembita]

That didn't work. It gave me one column with 1 thru 6 multiple times and a second column of just 2006.

This problem has to do with the statement in the THEN part of the CASE statement. SQL doesn't like the = sign. If there is a way to do it without the = sign I think that would work, but don't know how....

[scripto]

well you can't put the case statment in the WHERE clause - it has to go in the SELECT stmnt, so this way may give you what you want.

if datepart(dd, getdate()) = 1
select TOP 100 PERCENT databasex.Point _time where MONTH(databasex .Point_time) = MONTH(getdate() )-1
and YEAR(databasex. Point_time) = YEAR(getdate())
GROUP BY databasex.Point _time
ORDER BY databasex.Point _time

else
select TOP 100 PERCENT databasex.Point _time where MONTH(databasex .Point_time) = MONTH(getdate() )
and YEAR(databasex. Point_time) = YEAR(getdate())
GROUP BY databasex.Point _time
ORDER BY databasex.Point _time

[Taftheman]

That is infact wrong!

you can put a case statement in the where clause

SELECT TOP 100 PERCENT database.Point_ time
FROM database
WHERE database.Point_ time =

CASE WHEN datepart(dd, getdate())=1
THEN(MONTH(getd ate())-1) AND
(YEAR(getdate() ))

CASE ELSE (MONTH(getdate( ))) AND
(YEAR(getdate() ))
END
GROUP BY database.Point_ time
ORDER BY database.Point_ time

[Taftheman]

Sorry I made a mistake by accident in the previouse reply, always check your work. This is the correct way
SELECT TOP 100 PERCENT database.Point_ time
FROM database
WHERE database.Point_ time =

CASE
WHEN datepart(dd, getdate())=1
THEN(MONTH(getd ate())-1) AND
(YEAR(getdate() ))
WHEN (MONTH(getdate( ))) AND
(YEAR(getdate() ))
END

GROUP BY database.Point_ time
ORDER BY database.Point_ time