CODEWITHSUNDEEP
sql-serversql-server-2005
windowsgm
windowsgm

Reputation: 1616

Consecutive Day Query

I have a table of contactIDs and datetimes, the time being when a letter was generated for the contact. Each contact can only have one letter generated a day. I want to write a query to select any contact that has had letters generated on more than one consecutive day.

I guess I'd need to increment the datetime as records are found but how would I do this separately for each contact?

Upvotes: 0

Views: 104

Answers (2)

gvee
gvee

Reputation: 17161

I decided to utilise a calendar table. Use your favourite search engine to find a script to create a calendar table.

Alternatively, here's one I made earlier

So here's the query I have rolled with in full, I will explain the detail of it afterwards

DECLARE @your_table table (
   contact_id int
 , created_on datetime
);

INSERT INTO @your_table (contact_id, created_on)
          SELECT 9, '2014-01-02 06:00'
UNION ALL SELECT 9, '2014-01-02 18:00'
UNION ALL SELECT 9, '2014-01-05 08:00'
UNION ALL SELECT 9, '2014-01-07 01:00'
UNION ALL SELECT 3, '2014-01-02 00:01'
UNION ALL SELECT 3, '2014-01-03 23:59' -- Over 24 hours but a "day" different
UNION ALL SELECT 7, '2014-01-04 01:00'
UNION ALL SELECT 7, '2014-01-06 01:00'
UNION ALL SELECT 7, '2014-01-08 01:00'
UNION ALL SELECT 7, '2014-01-09 01:00'
UNION ALL SELECT 7, '2014-01-10 01:00'
UNION ALL SELECT 7, '2014-01-11 01:00'
;

; WITH x AS (
  SELECT your_table.contact_id
       , your_table.created_on
       , calendar.the_date
       , Row_Number() OVER (PARTITION BY your_table.contact_id ORDER BY calendar.the_date) As sequence
  FROM   @your_table As your_table
   INNER
    JOIN dbo.calendar
      ON your_table.created_on >= calendar.the_date
     AND your_table.created_on <  DateAdd(dd, 1, calendar.the_date)
)
, y AS (
  SELECT curr.contact_id
       , curr.created_on
       , curr.the_date As the_date
       , prev.the_date As previous_date
       , DateDiff(dd, prev.the_date, curr.the_date) As difference_in_days
  FROM   x As curr
   LEFT
    JOIN x As prev
      ON curr.contact_id = prev.contact_id
     AND curr.sequence   = prev.sequence + 1
)
SELECT contact_id
     , created_on
     , the_date
     , previous_date
     , difference_in_days
FROM   y
WHERE  difference_in_days = 1

Because you didn't provide any sample data that's where I had to start, so the query is self-contained using a table variable (@your_table) as its source.

Once populated we start out with a couple of Common-Table Expressions (CTE for short). Read up here if you're not familiar with the concept: http://msdn.microsoft.com/en-us/library/ms175972.aspx . There's not a lot of difference between these and subqueries.

Our first CTE (x) joins @your_table to the calendar table. It does this by returning the single row from the calendar on which the created_on date lies, by checking that it is greater than (or equal to) the calendar date and less than the next calendar date (DateAdd()).

Once complete we use the windowed function - Row_Number() to provide some sequencing.

We partition (i.e. reset the sequence) for each contact_id and sort the sequence by the created_on date.

Moving on to the second CTE (y): we perform a self-join on CTE x joining each contact record with its "previous" based on the sequencing.

This allows us to work out the difference in days (DateDiff()) between the current and the previous records.

Finally we reduce our resultset to only those records where the difference in days is 1 i.e. contacts on consecutive days

Upvotes: 0

KumarHarsh
KumarHarsh

Reputation: 5094

select contactid from ContactTable a inner join Contacttable B on a.contctid=b.contactid and datediff(day,a.date,b.date)=1

Upvotes: 1

Related Questions