SQL Select rows based on date

Question

I have a two tables (Table1 and Table2):

Table1

Date          Name   Other
2014-02-08    Alex   1
2014-06-15    Bob    1

Table2

Date          Name    Count
2014-02-07    Alex    1
2014-01-31    Alex    2
2014-02-09    Alex    4
2014-02-08    Alex    10
2014-02-10    Alex    0
2014-02-01    Alex    4
2014-01-08    Alex    5
2014-03-08    Alex    4
2014-06-01    Bob     22
2014-06-02    Bob     0
2014-06-10    Bob     9
2014-06-15    Bob     3
2014-06-16    Bob     3
2014-06-20    Bob     5
2014-06-14    Bob     18
2014-07-11    Bob     1
2014-08-15    Bob     2

I am having a difficult time constructing a query that accomplishes the following:

From Table1, run through each "Date" and "Name"
For a given "Date" and "Name" in Table1, go through Table2 and grab all rows that also have the same "Name" and that have dates that are 10 days before the "Date" (in Table1) and 5 days after "Date" (in Table1).

So, for Table1, "Alex" on "2014-02-08", I want to grab all rows in Table2 that also say "Alex" but whose date is between "2014-01-29" (10 days before 2014-02-08) and "2014-02-13" (5 days after 2014-02-08).

For "Bob" on "2014-06-15", I want to grab all rows in Table2 that also say "Bob" but whose date is between "2014-06-05" (10 days before 2014-06-15) and "2014-06-20" (5 days after 2014-06-15).

The expected output is:

Date          Name    Count
2014-02-07    Alex    1
2014-01-31    Alex    2
2014-02-09    Alex    4
2014-02-08    Alex    10
2014-02-10    Alex    0
2014-02-01    Alex    4
2014-06-10    Bob     9
2014-06-15    Bob     3
2014-06-16    Bob     3
2014-06-20    Bob     5
2014-06-14    Bob     18

In my real work, the number of rows in Table1 is much larger and the number of days I'd like to grab before/after the reference date can vary.

@PM 77-1: I've only been through the basic SQL tutorials and have only dealt with single tables. The logic with dealing with two tables eludes me at this time. — slaw, Sep 18 '14 at 02:31

score 0 · Answer 1 · answered Sep 18 '14 at 02:21

0

I think you can do something like this:

select t2.*
from table1 t2
where exists (select 1
              from table1 t1
              where t1.name = t2.name and t1.date >= t2.date - 'interval 10 day' and
                    t1.date <= t2.date + 'interval 10 day'
             );

answered Sep 18 '14 at 02:21

Gordon Linoff

1,242,037
58
646
786

OP operates with two different tables: `Table1` and `Table2`. – PM 77-1 Sep 18 '14 at 02:24
@Gordon Linoff: Sorry, I'm completely new to SQL. I think I get most of the query (assuming that it is valid syntax which I'll have to look up) but what does "select 1" mean? – slaw Sep 18 '14 at 02:30
`SELECT 1` will return `1`. It's used when we need to check just the existence of a matching record and, therefore, do not need to fetch any fields. It usually is used together with `EXISTS` clause. – PM 77-1 Sep 18 '14 at 02:36
@Gordon Linoff: Should the query be "select t2.* from table2 t2" rather than "table1 t2"? Or, really, "Table1" and "Table2"... – slaw Sep 18 '14 at 02:49

score 0 · Answer 2 · answered Sep 18 '14 at 03:01

See:

http://sqlfiddle.com/#!4/82e1e/10

Assuming Oracle, but you get the picture:-). You would need to format the date in the results. That is left for your perusal.

In case, the above link cannot be opened, the sql is:

select t2.*
from
  table2 t2, table1 t1
where
  t1.name = t2.name
  and t2.date1 > t1.date1 -10 
  and t2.date1 <= t1.date1 +5;

The result is:

DATE1                           NAME    COUNT
February, 07 2014 00:00:00+0000 Alex    1
January, 31 2014 00:00:00+0000  Alex    2
February, 09 2014 00:00:00+0000 Alex    4
February, 08 2014 00:00:00+0000 Alex    10
February, 10 2014 00:00:00+0000 Alex    0
February, 01 2014 00:00:00+0000 Alex    4
June, 10 2014 00:00:00+0000     Bob     9
June, 15 2014 00:00:00+0000     Bob     3
June, 16 2014 00:00:00+0000     Bob     3
June, 20 2014 00:00:00+0000     Bob     5
June, 14 2014 00:00:00+0000     Bob     18

SQL Select rows based on date

2 Answers2