CODEWITHSUNDEEP
sql-servert-sqlcase
Tiago Maia
Tiago Maia

Reputation: 21

Apply Different WHERE clause depending on value of one field

i'm trying to build a query in which I need to apply 2 different where clauses, depending on the value of Current Month. In this case, I need to show data from the last 2 years, only of the months before the current month:

Example 1:

Current Date is: 01-01-2017

Need to show data from:

01/2015; 02/2015; 03/2015; 04/2015; 05/2015; 06/2015;
07/2015; 08/2015; 09/2015; 10/2015; 11/2015; 12/2015;
01/2016; 02/2016; 03/2016; 04/2016; 05/2016; 06/2016;
07/2016; 08/2016; 09/2016; 10/2016; 11/2016; 12/2016.

Example 2:

Current Date is: 01-03-2017

Need to show data from: 01/2016; 02/2016; 01/2017; 02/2017.

So I built the following query:

SELECT *
FROM TABLE1 
WHERE
    CASE MONTH(GETDATE())
        WHEN 1
        THEN YEAR(Data)>=YEAR(GETDATE())-2 and YEAR(data)<YEAR(GETDATE())
        ELSE YEAR(Data)>=YEAR(GETDATE())-1 and YEAR(data)<=YEAR(data) and MONTH(data)<MONTH(GETDATE())
    END

I'm getting an error. Can you please help me? Thank you.

Upvotes: 2

Views: 83

Answers (3)

iamdave
iamdave

Reputation: 12243

You can't swap in additional statements to your where clause using case statements. Instead, you need to resolve the case to an equality:

select *
from Table1
where case month(getdate())    -- You want to avoid using functions on fields in your WHERE claises, as this can reduce performance.
        when 1 then case when Data >= dateadd(year,datediff(year,0,getdate())-2,0)
                           and Data < dateadd(year,datediff(year,0,getdate()),0)
                         then 1    -- Data rows the meet the criteria will return 1.
                         else 0    -- Data rows that do not will return 0.
                         end
        else case when (Data >= dateadd(year,datediff(year,0,getdate())-1,0)
                        and Data < dateadd(m,datediff(m,0,getdate())-12,0)
                       )
                       or (Data >= dateadd(year,datediff(year,0,getdate()),0)
                           and Data < dateadd(m,datediff(m,0,getdate()),0)
                          )
               then 1
               else 0
               end
        end = 1             -- Then limit the results to only those rows that returned a 1.

In your specific instance however, this can be simplified to a standard or:

select *
from Table1
where (month(getdate()) = 1
         and Data >= dateadd(year,datediff(year,0,getdate())-2,0)
         and Data < dateadd(year,datediff(year,0,getdate()),0)
      )
   or (month(getdate()) <> 1
         and (Data >= dateadd(year,datediff(year,0,getdate())-1,0)
              and Data < dateadd(m,datediff(m,0,getdate())-12,0)
             )
          or (Data >= dateadd(year,datediff(year,0,getdate()),0)
              and Data < dateadd(m,datediff(m,0,getdate()),0)
             )
      )

Note the use of brackets above to separate out the logical tests. Where a Data row meets either one of those criteria it will be returned in your query.

Upvotes: 0

IVNSTN
IVNSTN

Reputation: 9299

Your syntax is incorrect for sure. THEN is not a logical expression - it is supposed to return value. So you can't write logical expression in THEN/ELSE blocks as you have attempted to. Instead you might try something like:

WHERE 
  @date >= CASE WHEN a=b THEN '20150101' ELSE '20160202' END

Another thing is: conversions and functions in predicate are very bad for performance. When working with dates you might want to prepare filter predicate before the query when possible, e.g.:

declare
  @date_begin date,
  @date_end date

set @date_end = DATEADD(..., @arg_date)
set @date_begin = DATEADD(YEAR, -2, @date_end)

select ...
where date between @date_begin and @date_end

in your case it could be something like:

declare
    @arg_date   DATE = GETDATE(),
    @date_begin DATE,
    @date_end   DATE,
    @max_month  INT

set @max_month = MONTH(@date)

if @max_month = 1
begin
    set @date_end = DATEADD(dd, 1-DATEPART(dy, @arg_date), @arg_date) /* first day of year */
    set @date_begin = dateadd(YY, -2, @date_end)
end
else
begin
    set @date_end = @arg_date
    set @date_begin = dateadd(YY, -1, DATEADD(dd, 1-DATEPART(dy, @date_end), @date_end)) /* first day of year_begin */
end

SELECT *
FROM TABLE1 t
WHERE t.date >= @date_begin and t.date < @date_end
    AND (@max_month = 1 OR MONTH(t.date) < @max_month)

another (a better) way is to prepare @periods table variable, put each (date_begin, date_end) pair you need into it and join with TABLE1 - you'll get rid of all function calls from within WHERE clause.

You should realize: you know exactly which periods of each year you need in the result set. There is nothing to compute from stored TABLE1->date column. Just filter it with precomputed date intervals. Don't convert or modify date column - it is already ready to use. Merely apply appropriate filters. MONTH(date) <= 3 is date <= 20170331. Don't torture left part - prepare appropriate right part of such predicates.

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15140

The easiest way would be something like:

SELECT *
FROM TABLE1 
WHERE
    (YEAR(Data)>=YEAR(GETDATE())-2 and YEAR(data)<YEAR(GETDATE()) AND MONTH(GETDATE()) = 1)
OR  (YEAR(Data)>=YEAR(GETDATE())-1 and MONTH(data)<MONTH(GETDATE()) and MONTH(GETDATE()) <> 1)

(Note I removed the superfluous and YEAR(data)<=YEAR(data).).

Personally I prefer (and I think it's generally advised) AND/OR logic to a CASE in a WHERE clause.

The error with your CASE statement is caused by the fact that CASE returns an atomic value. It cannot be used in the same way as if in procedural languages.

Upvotes: 0

Related Questions