1

I would like to build a query that returns entries that have been entered in two consecutive weeks.

For example:

Name  |  Country  |  Date
Name1 | Country1  | 2014-07-29 
Name2 | Country2  | 2014-08-08 
Name1 | Country2  | 2014-08-07

I want to be able to select the entries that are entered on two consecutive weeks. In this case, my query would return only Name1.

I recently asked a similar question about querying records entered on two separate dates and this is what I have for that:

SELECT Name
FROM Table
GROUP by Name
COUNT(DISTINCT Date) > 1

But this checks that the record was inserted on more than one date, but not that it has been entered at least once in two consecutive weeks.

mo alaz
  • 4,529
  • 8
  • 30
  • 36

3 Answers3

1

If your definition of week is "7 days later", then you can do something like:

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

Note that different databases handle dates differently, so t.date + 7 may not work in all databases. There is some similar construct that does.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This is almost exactly what I need. However, I don't need to compare dates in two different tables. I only need to compare two entries within the same table based on the date. So to take my example, I would get all the Names from the 'Name' column and check to see if they have been entered in two consecutive weeks. – mo alaz Jul 30 '14 at 12:45
  • @moalaz . . . Then just use the same table in the outer query and the subquery. – Gordon Linoff Jul 30 '14 at 13:15
  • `t2.date + interval '1' week` would be the ANSI SQL expression for "a week later" –  Jul 30 '14 at 13:27
1

You haven't said what DBMS you are using, and "week" is handled differently in them. The Stack Overflow question Getting week number of date in SQL is a good starting point, or the SQL2003 standard at http://users.atw.hu/sqlnut/sqlnut2-chp-4-sect-4.html and search within it for EXTRACT.

But working with SQL-Server, a working example would be

--Input your sample data
DECLARE @T TABLE (N varchar(50),   C varchar(50),  D  Date)
INSERT INTO @T VALUES('Name1', 'Country1', '2014-07-29'), ('Name2', 'Country2', '2014-08-08'), ('Name1', 'Country2', '2014-08-07')

--Code to search it for entries in consecutive weeks
SELECT DISTINCT T1.N FROM @T as T1 INNER JOIN @T as T2 ON T1.N = T2.N
WHERE (DATEPART(year, T1.D) * 52 + DATEPART(week, T1.D)) - (DATEPART(year, T2.D) * 52 + DATEPART(week, T2.D)) = 1

Note that you really need to use the week function, because an entry on Monday of one week and Friday of the same week are farther apart than an order on Friday of one week and Monday of the next week.

Also note that you can't just use the week, otherwise an entry in the first week of January of 2005 would "match" with an entry in the second week of January of 2006, or would miss a match in the last week of December of 2004.

Community
  • 1
  • 1
Robert Sheahan
  • 2,100
  • 1
  • 10
  • 12
  • +1 - regarding the weeks and multi-year dates. I assumed in my answer we were looking at dates within one year, but that is a very, very good point. – DCaugs Jul 30 '14 at 18:40
0

Without more information I'm making some assumptions with this solution, but here is an off the cuff/rough idea using t-SQL...at least a starting point: 

select a.name
,a.date 
FROM table a
INNER JOIN table a2 ON a.name = a2.name
WHERE a2.date >= DATEADD(dd,(7 - (DATEPART(dw,a.date)%7)) +1, a.date)
AND a2.date < DATEADD(dd, 7, (DATEADD(dd,(7 - (DATEPART(dw,a.date)%7)) +1, a.date)))

This assumes that you literally want entries that appear in week one and then again in week two where week two is any day from the following Sunday through Saturday (or whichever day you decide to define as the start of the week). I believe this meets your requirement and is a little more specific than defining the following week as simply '>= +7 and <= +14 days'. I'm not saying there is anything wrong with that, just that it might not meet your needs if you want an entry that falls on a Friday and an entry that falls on the following Monday to be considered as a consecutive pair.

This example takes your date, finds the integer representing the day of week it falls on, then mods and subtracts from 7 to find the number of days until the end of that day's week. Then, using that end-of-week date, we can add one day to find the start of the following week as well as use the DATEADD function to add 7 days to find the upper limit (the start of the second week out), giving us a date range to work within. This works well because it is always relevant to the date being passed and you don't have to worry about the position (in the week) of the date being passed in. On the down side, it's probably not going to be a performance champ...so depending on the use and volume, it could be pretty slow.

DCaugs
  • 484
  • 5
  • 16