SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Case Statement in Where Clause
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Yak Posting Veteran

USA
71 Posts

Posted - 10/31/2013 :  11:40:18  Show Profile  Reply with Quote
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

583 Posts

Posted - 10/31/2013 :  11:53:42  Show Profile  Reply with Quote

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))

Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
317 Posts

Posted - 10/31/2013 :  12:07:33  Show Profile  Reply with Quote
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
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
362 Posts

Posted - 10/31/2013 :  16:38:54  Show Profile  Reply with Quote
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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000