SQL Server: serial line issue

Question

I'm having difficulty sequentially querying strings and you need help.

I have a table that is part of a telephony switch. It tracks every employee who enters the phone (and the extension of the phone). I have another table that tells me where this phone is located (via several sites).

The logic that we are going to do is if someone logs into a specific phone / site within two consecutive days, we will trigger an event to update the record of our employee to make this site the main place.

The problem with my separation is that even if the data is not truly sequential, my score increases.

If you are considering the following table:

row_date    logid   extn    duration    sitecode    daysconsecutive
----------------------------------------------------------------------------    
2014-05-22  500001  414128  9.82    W   1
2014-05-27  500001  414120  10.74   W   2
2014-05-28  500001  414149  5.47    W   3
2014-05-28  500001  414126  6.18    W   4
2014-05-29  500001  414128  11.80   W   5
2014-05-30  500001  414128  2.21    W   6
2014-05-30  500001  414150  5.47    N   1
2014-05-31  500001  414128  4.57    W   7
2014-06-02  500001  414150  4.94    N   2

5/30, W N. 5/31 W - 1, 7, 6 W 5/22 5/30.

, where daysconsecutive>=2 .

, , .

SQL Server 2008 R2.

.

Fiddle .

create table haglog (row_date datetime, logid int, extn int, duration decimal(10,2), sitecode varchar(10))

insert into haglog 
select '2014-05-22',500001,414128,9.82,'W' union all
select '2014-05-27',500001,414120,10.74,'W' union all
select '2014-05-28',500001,414149,5.47,'W' union all
select '2014-05-28',500001,414126,6.18,'W' union all
select '2014-05-29',500001,414128,11.80,'W' union all
select '2014-05-30',500001,414128,2.21,'W' union all
select '2014-05-30',500001,414150,5.47,'N' union all
select '2014-05-31',500001,414128,4.57,'W' union all
select '2014-06-02',500001,414150,4.94,'N'

;with consecutivecte
AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY logid, sitecode ORDER BY row_date) AS daysconsecutive
FROM haglog
)

select * 
from consecutivecte
where logid=500001
order by row_date

Answer 1

gaps-and-islands . , sitecode , sitecode. .

WITH
  t1 AS (
    SELECT *,
      ROW_NUMBER() OVER(PARTITION BY logid, sitecode ORDER BY row_date)
      - ROW_NUMBER() OVER(PARTITION BY logid ORDER BY row_date) consecutive_group_id
    FROM @haglog
  ),
  t2 AS (
    SELECT *,
      ROW_NUMBER() OVER(PARTITION BY logid, sitecode, consecutive_group_id ORDER BY row_date) daysconsecutive
    FROM t1
  )
SELECT row_date, logid, extn, duration, sitecode, daysconsecutive
FROM t2
ORDER BY row_date

Answer 2

cross apply (select top(1) ...) CTE . .

, .

select h.* , t.* 
from haglog h
    cross apply ( select top(1) * from haglog t where ...) as t
where logid=500001 and date = @yesterday