Case Statement in Where Clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Case Statement in Where Clause

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-10-31 : 11:40:18

Good afternoon, I have the following CASE statement in a query that I can't seem to get correct.

If the current month = 1 then I want to have the query use the month 12 and the current year - 1
otherwise use the current month - 1 and the current year.

Here is the query

select blah,blah2 from table
where
[wa_mattvalue].[fee_bills_ytd] > 0
AND
CASE WHEN MONTH(GetDate()) = 1 THEN
[wa_mattvalue].[mo] = '12'
and [wa_mattvalue].[yr] = YEAR(GetDate())-1
ELSE
[wa_mattvalue].[mo] = MONTH(GetDate())-1
and [wa_mattvalue].[yr] = YEAR(GetDate())
END

Thanks in advance for your help.

Bryan Holmstrom

Ifor
Aged Yak Warrior

700 Posts

Posted - 2013-10-31 : 11:53:42

[code]
WHERE wa_mattvalue.fee_bills_ytd > 0
AND wa_mattvalue.mo = MONTH(DATEADD(month, -1, CURRENT_TIMESTAMP))
AND wa_mattvalue.yr = YEAR(DATEADD(month, -1, CURRENT_TIMESTAMP))
[/code]

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-10-31 : 12:07:33

Maybe something like this:

select blah,blah2 from table
where [wa_mattvalue].[fee_bills_ytd] > 0
AND [wa_mattvalue].[mo] = CASE WHEN MONTH(GetDate()) = 1 THEN '12' ELSE MONTH(GetDate())-1 END
AND [wa_mattvalue].[yr] = CASE WHEN MONTH(GetDate()) = 1 THEN YEAR(GetDate())-1 ELSE YEAR(GetDate()) END

The problem is a limitation within T-SQL that you cannot have multiple statements

djj

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-10-31 : 16:38:54

CASE statements in a WHERE can make it impossible for the query optimizer to generate the best plan. Thus, I suggest doing something like this instead:


DECLARE @yr smallint --chg to match data type of [wa_mattvalue].[yr]
DECLARE @mo tinyint --chg to match data type of [wa_mattvalue].[mo]
SET @yr = YEAR(GETDATE())
SET @mo = MONTH(GETDATE()) - 1
IF @mo = 0
BEGIN
    SET @yr = @yr - 1
    SET @mo = 12
END --IF

SELECT whatever, whatever2, ...
FROM dbo.tablename
WHERE
  [wa_mattvalue].[fee_bills_ytd] > 0
  AND
  [wa_mattvalue].[yr] = @yr
  AND
  [wa_mattvalue].[mo] = @mo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources