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(database.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(getdate())-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(getdate())-1) AND
(YEAR(getdate()))
WHEN (MONTH(getdate())) AND
(YEAR(getdate()))
END

GROUP BY database.Point_time
ORDER BY database.Point_time