Find consecutive days of service without a day break in between

Question

service table:

claimid, customerid, serv-start-date, service-end-date, charge
1, A1, 1-1-14 , 1-5-14 , $200
2, A1, 1-6-14 , 1-8-14 , $300
3, A1, 2-1-14 , 2-1-14 , $100
4, A2, 2-1-14 , 2-1-14 , $100
5, A2, 2-3-14 , 2-5-14 , $100
6, A2, 2-6-14 , 2-8-14 , $100

Problem: Basically to see the maximum total consecutive days Service start date and end date.
for customer A1 it would be 8 days (1-5 plus 6-8) and customer A2 it would be 5 6 days (3-5 plus 6-8) ... (claimid is unique PK).

Dates are in m-d-yy notation.

Answer 1

WITH chain_builder AS
(
SELECT ROW_NUMBER() OVER(ORDER BY s.customerid, s.CLAIMID) as chain_ID,
  s.customerid,
  s.serv-start-date, s.service-end-date, s.CLAIMID, 1 as chain_count
FROM services s
WHERE s.serv-start-date <> ALL 
  (
  SELECT DATEADD(d, 1, s2.service-end-date)
  FROM services s2
  )
UNION ALL
SELECT chain_ID, s.customerid, s.serv-start-date, s.service-end-date,
  s.CLAIMID, chain_count + 1
  FROM services s
JOIN chain_builder as c
  ON s.customerid = c.customerid AND
  s.serv-start-date = DATEADD(d, 1, c.service-end-date)
),
chains AS
(
SELECT chain_ID, customerid, serv-start-date, service-end-date,
  CLAIMID, chain_count
FROM chain_builder
),
diff AS
(
SELECT c.chain_ID, c.customerid, c.serv-start-date, c.service-end-date,
  c.CLAIMID, c.chain_count,
  datediff(day,c.serv-start-date,c.service-end-date)+1 daysdiff
FROM chains c
),
diff_sum AS
(
SELECT chain_ID, customerid, serv-start-date, service-end-date,
  CLAIMID, chain_count,
  SUM(daysdiff) OVER (PARTITION BY chain_ID) as total_diff
FROM diff
),
diff_comp AS
(
SELECT chain_ID, customerid,
  MAX(total_diff) OVER (PARTITION BY customerid) as total_diff
FROM diff_sum
)
SELECT DISTINCT ds.CLAIMID, ds.customerid, ds.serv-start-date,
  ds.service-end-date, ds.total_diff as total_days, ds.chain_count
FROM diff_sum ds
JOIN diff_comp dc
ON ds.chain_ID = dc.chain_ID AND ds.customerid = dc.customerid
  AND ds.total_diff = dc.total_diff
ORDER BY customerid, chain_count
OPTION (maxrecursion 0)

Answer 2

The only way I could think to solve the issue described by Jonathan Leffler (in a comment on another answer) was to use a temp table to merge contiguous date ranges. This would be best accomplished in an SP - but failing that the following batch may produce the output you are looking for:-

select *, datediff(day,servicestartdate,serviceenddate)+1 as numberofdays
into #t
from service

while @@rowcount>0 begin

  update t1 set 
      t1.serviceenddate=t2.serviceenddate,
      t1.numberofdays=datediff(day,t1.servicestartdate,t2.serviceenddate)+1
  from #t t1
  join #t t2 on t2.customerid=t1.customerid
    and t2.servicestartdate=dateadd(day,1,t1.serviceenddate)

end

select 
  customerid,
  max(numberofdays) as maxconsecutivedays
from #t
group by customerid

The update to the temp table needs to be in a loop because the date range could (I assume) be spread over any number of records (1->n). Interesting problem.

---

I've made updates to the code so that the temp table ends up with an extra column that holds the number of days in the date range on each record. This allows the following:-

select x.customerid, x.maxconsecutivedays, max(x.serviceenddate) as serviceenddate
from (
    select t1.customerid, t1.maxconsecutivedays, t2.serviceenddate
    from (
        select 
          customerid,
          max(numberofdays) as maxconsecutivedays
        from #t
        group by customerid
    ) t1
    join #t t2 on t2.customerid=t1.customerid and t2.numberofdays=t1.maxconsecutivedays
) x
group by x.customerid, x.maxconsecutivedays

To identify the longest block of consecutive days (or the latest/longest if there is a tie) for each customer. This would allow you to subsequently dive back into the temp table to pull out the rows related to that block - by searching on the customerid and the serviceenddate (not maxconsecutivedays). Not sure this fits with your use case - but it may help.

Answer 3

Here ya go, I think it's the simplest way:

SELECT customerid, sum(datediff([serv-end-date],[serv-start-date]))
  FROM [service]
 GROUP BY customerid

You will have to decide if same day start/end records count as 1. If they do, then add one to the datediff function, e.g. sum(datediff([serv-end-date],[serv-start-date]) + 1)

If you don't want to count same day services but DO want to count start/end dates inclusively when you sum them up, you will need to add a function that does the +1 only when start and end dates are different. Let me know if you want ideas on how to do that.

Answer 4

This gets a little messy since you could possibly have customers without multiple records. This uses a common-table-expressions, along with the max aggregate and union all to determine your results:

with cte as (
  select s.customerid, 
    s.servicestartdate,
    s2.serviceenddate,
    datediff(day,s.servicestartdate,s2.serviceenddate)+1 daysdiff
  from service s
    join service s2 on s.customerid = s2.customerid
      and s2.servicestartdate in (s.serviceenddate, dateadd(day,1,s.serviceenddate))
)
select customerid, max(daysdiff) daysdiff
from cte
group by customerid
union all
select customerid, max(datediff(day, servicestartdate, serviceenddate))
from service s
where not exists (
  select 1
  from cte
  where s.customerid = cte.customerid
  )
group by customerid

• SQL Fiddle Demo

The second query in the union statement is what determines those service records without multiple records with consecutive days.