SQL filtering by different conditions based on values in column

Question

I have a classifier column that can have value of X or Y. My select query has to filter by date taking into account if the column has x or y.

date BETWEEN i.valid_fromX AND i.valid_toX 
date BETWEEN i.valid_fromY AND i.valid_toY

Essentially I need the equivalent of the following in SQL:

if classifier == X {
  check if date is between valid_fromX to valid_toX
}   else if classifier == Y {
  check if date is between valid_fromY to valid_toY
}

Andomar · Accepted Answer · 2012-03-16 11:43:45Z

5

You can do that with regular and/or logic. This example searches for January if classifier equals X, and for February if classifier equals Y:

where  classifier = 'X' and date between '2012-01-01' and '2012-02-01'
       or
       classifier = 'Y' and date between '2012-02-01' and '2012-03-01'

answered Mar 16, 2012 at 11:43

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Over a year ago

Why no brackets just in case?

Bridge · Accepted Answer · 2012-03-16 11:44:04Z

2

WHERE  ( classifier = 'X' AND [date] BETWEEN valid_fromX AND valid_toX )
        OR ( classifier = 'Y' AND [date] BETWEEN valid_fromY AND valid_toY )

answered Mar 16, 2012 at 11:44

Bridge

30.9k10 gold badges64 silver badges85 bronze badges

Comments

user359040 · Accepted Answer · 2012-03-16 11:51:00Z

0

A minor variant:

where date between 
           case classifier when 'X' then valid_fromX when 'Y' then valid_fromY end 
           and case classifier when 'X' then valid_toX when 'Y' then valid_toY end

answered Mar 16, 2012 at 11:51

user359040

Collectives™ on Stack Overflow

SQL filtering by different conditions based on values in column

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related