CODEWITHSUNDEEP
mysqlsql
Zali
Zali

Reputation: 3

SQL query that checks by week

I need an SQL query that checks for whether a person is active for two consecutive weeks in the year.

For example, 

Table1:
Name | Activity | Date
Name1|Basketball| 08-08-2014
Name2|Volleyball| 08-09-2014
Name3|None      | 08-10-2014
Name1|Tennis    | 08-14-2014

I want to retrieve Name1 because that person has been active for two consecutive weeks in the year.

This is my query so far:

SELECT DISTINCT Name 
FROM Table1 
Where YEAR(Date) = 2014 AND 
      Activity NOT 'None' AND

This is where I would need the logic that checked for an activity in two consecutive weeks. A week can be described as 7 to 14 days later. I am working with MYSQL.

Upvotes: 0

Views: 145

Answers (4)

VACN
VACN

Reputation: 75

From the top of my head, I suggest this query:

SELECT DISTINCT t1.Name
FROM Table1 AS t1, Table1 AS t2
WHERE t1.Name = t2.Name
AND t2.Date BETWEEN t1.Date-7 AND t1.Date+7;

The idea is basically: you call your table twice, select the rows whose names match, and then keep only those whose second date are up to 7 days away from the first date.

Upvotes: 0

Christof R
Christof R

Reputation: 883

I don't know if it is performance relevant, but I like concise queries:

SELECT t1.Name
FROM Table1 t1, Table1 t2
Where t1.Name=t2.Name AND
  t1.Date >= '2014-01-01' AND t1.Date < '2015-01-01' AND
  t1.Activity <> 'None' AND
  t1.Date < t2.Date AND
  datediff(t2.Date, t1.Date) <= 14

I liked the hint of @user2067753 about the YEAR(date).

I used the sqlfiddle of the answer above to check the performance using the explain syntax. It seems that avoiding sub queries as in VACN's answer or mine is beneficial (see join vs sub query)

Upvotes: 1

Paul Maxwell
Paul Maxwell

Reputation: 35563

I have avoided using YEAR(Date) in the where clause deliberately, and recommend you do too. Using functions on multiple rows of data to suit a single criteria (2014) never makes sense to me, plus it destroys the effectiveness of indexes (see "sargable" at wikipedia). Way easier to just define a filter by a date range IMHO.

I've used a correlated subquery to derive nxt_date which might not scale very well, but overall the performance will depend on your indexes most probably.

select distinct
      name
from (
      select
              t.name
            , t.Activity
            , t.`Date`
            , (
               select min(table1.`Date`) from table1 
               where t.name = table1.name 
               and table1.Activity <> 'None'
               and table1.`Date` > t.`Date`
               ) as nxt_date
      from table1 as t
      where ( t.`Date` >= '2014-01-01' and t.`Date` < '2015-01-01' )
      and t.Activity <> 'None'
      ) as sq
where datediff(sq.nxt_date, sq.`Date`) <= 14
;

see: http://sqlfiddle.com/#!9/cbbb3/9

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269553

You can do the logic using an exists subquery:

select t.*
from table1 t
where exists (select 1
              from table1 t2
              where t2.name = t.name and
                    t2.date between t.date + 7 and t.date + 14
             );

Upvotes: 1

Related Questions