Select records between values of two columns

Question

I have following table which saves sales data for specific week of a year.

Sales

week | year | value
50   | 2011 | 150
51   | 2011 | 151
52   | 2011 | 152
1    | 2012 | 170
2    | 2012 | 190
3    | 2012 | 180
4    | 2012 | 160
5    | 2012 | 170
6    | 2012 | 290

I want to find records which lies between week1 of year1 and week2 of year2.

for example

between week 50 of 2011 to week 5 of 2012
between week 1 of 2012 to week 6 of 2012

Having little experience in sQL, I tried following query which works for case 2 but not for case 1.

SELECT * from sales WHERE (week >= 1 AND year >= 2012) AND (week <= 6 AND year <= 2012 )

Looks like grouping of condition does not work as I understand. It takes as if there are no grouping of conditions.

score 4 · Accepted Answer · answered Aug 29 '12 at 08:34

4

select *
from
(
   Select *, year*100 + week as yearweek
   from table
) v
where yearweek between 201150 and 201205

answered Aug 29 '12 at 08:34

podiluska

50,950
7
98
104

2

+1 Same solution I had. The upside is that the statement works for both scenarios without having to resort to error prone `or` clauses. The downside is that the statement is not sargable. – Lieven Keersmaekers Aug 29 '12 at 09:16
This worked perfectly with SQL. The challenge is how to map this query to Rails ActiveRecord query mechanism. – Amit Patel Aug 29 '12 at 09:26
@AmitPatel `find_by_sql`, perhaps? – podiluska Aug 29 '12 at 09:28
@podiluska, is this query is compatible with major DBs as I don't know which DB environment will be used for the application. – Amit Patel Aug 29 '12 at 09:30
@AmitPatel That's standard SQL. Mysql may like quotes around the names. – podiluska Aug 29 '12 at 09:35
@Lieven, you had used `SELECT * FROM sales WHERE year * 100 + week BETWEEN 201150 AND 201206` which also worked. I am curious to know why you prefer podiluska's solution over yours? – Amit Patel Aug 29 '12 at 09:50
@AmitPatel It's the same query, just rearranged for purposes of clarity and readability. – podiluska Aug 29 '12 at 09:52
1

@AmitPatel - I didn't but the community did . In all faireness, podiluska was first to answer. – Lieven Keersmaekers Aug 29 '12 at 10:07
I used `Sales.where("year * 100 + week BETWEEN ? AND ?", 201150, 201206)` in Rails. Thanks Lieven and podiluska for invaluable inputs. – Amit Patel Aug 29 '12 at 10:08
2

@Lieven There's no accounting for taste :) – podiluska Aug 29 '12 at 10:12

score 1 · Answer 2 · edited May 23 '17 at 11:49

1

Simpler, faster and more elegant with comparing ad-hoc records:

SELECT *
FROM   tbl
WHERE (year, week) BETWEEN (2011, 50) AND (2012, 5);

A lot more details at this closely related answer, including a benchmark, and some more possible solutions.

edited May 23 '17 at 11:49

Community

1
1

answered Aug 29 '12 at 23:52

Erwin Brandstetter

605,456
145
1,078
1,228

Tom Chantler · Answer 3 · 2012-08-29T08:38:48.080

This query works for case 1:

SELECT * from sales WHERE (week >= 50 AND year = 2011) OR (week <= 5 AND year = 2012 )

Whereas this should work for most cases with adjacent years (e.g. 2011 and 2012 or whatever, but not 2001 to 2010). That case is a bit more complicated, but still fairly straightforward.

DECLARE @week1 INT
DECLARE @week2 INT
DECLARE @year1 INT
DECLARE @year2 INT

SELECT * from sales WHERE (week >= @week1 AND year = @year1) OR (week <= @week2 AND year = @year2 )

Okay, here is the example for a bigger range.

DECLARE @week1 INT
DECLARE @week2 INT
DECLARE @year1 INT
DECLARE @year2 INT

SELECT * from sales WHERE (week >= @week1 AND year = @year1) OR (week <= @week2 AND year = @year2 ) OR (year > @year1 AND year < @year2)
-- This last one gives ALL weeks for the full years between year1 and year2 (e.g. all weeks for 2002-2009 if we choose dates between 2001 and 2010)

Just to explain, I did it this way so it could take advantage of any indices that might exist. — Tom Chantler, Aug 29 '12 at 09:57

hgulyan · Answer 4 · 2012-08-29T08:38:55.797

0

Try this for 1st example

    SELECT * 
    FROM sales 
    WHERE (year = 2011 AND week > 50) 
       OR (year = 2012 AND week < 5)

In General

    SELECT * 
    FROM sales 
    WHERE (year = 'year1' AND week > 'week1') 
       OR  (year > 'year1' AND year < 'year2' ) 
       OR (year = 'year2' AND week < 'week2' )

edited Aug 29 '12 at 08:38

answered Aug 29 '12 at 08:33

hgulyan

8,099
8
50
75

This `(year > 2011 AND year < 2012) ` is never true – Germann Arlington Aug 29 '12 at 08:35
Check general query. It's just an example for 1st case. Sure it's doesn't make sense if there's one year difference – hgulyan Aug 29 '12 at 08:38

Select records between values of two columns

4 Answers4