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.

If it's a single value per customer then you probably meant finding the *maximum* number of consecutive days of service. And for customer 2 it should probably be 6 actually. — Andriy M, Oct 15 '14 at 21:57
Could you have a single customer with service records with date ranges that overlap. For sake of discussion, would it be legitimate for the database/table to record that customer A3 has service on 2014-01-01 to 2014-01-05 and on 2014-01-03 to 2014-01-08? — Jonathan Leffler, Oct 15 '14 at 23:14
Amongst other related questions, look at [Date difference between consecutive rows](http://stackoverflow.com/questions/10098570/), [Aggregate adjacent only records with T-SQL](http://stackoverflow.com/questions/235418/), and [Analytical Query](http://stackoverflow.com/questions/487142/). — Jonathan Leffler, Oct 16 '14 at 00:58

score 1 · Answer 1 · answered Oct 15 '14 at 22:08

1

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.

answered Oct 15 '14 at 22:08

sgeddes

62,311
6
61
83

1

Does this handle the plausible but not illustrated case where a single customer has service in 3 (or more) consecutive installments: say, 2014-01-01 to 2014-01-03 and 2014-01-04 to 2014-01-06 and 2014-01-07 to 2014-01-07 (which should produce an answer of 7 days)? – Jonathan Leffler Oct 15 '14 at 22:20
@JonathanLeffler -- I agree, and that would get even messier :) Off the top of my head, perhaps a dates table with a cross join to get the desired result. Recursive CTE maybe -- it wouldn't be trivial by any means though... – sgeddes Oct 16 '14 at 00:31
@sgeddes ya this is good if records are all sorted the way sample is but most likely they are not. So it wont capture what we needed. – Edgar Oct 16 '14 at 22:09

score 0 · Answer 2 · edited Oct 16 '14 at 00:38

0

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.

edited Oct 16 '14 at 00:38

Code Different

90,614
16
144
163

answered Oct 15 '14 at 22:26

Zen Skunkworx

177
5

1

I don't see how this sums consecutive days -- it just sums all date differences for each customer. – sgeddes Oct 16 '14 at 00:33
Each record stores a set of consecutive days from Start date to End date. All you have to do is sum them up by customer. – Zen Skunkworx Oct 16 '14 at 12:08

dav1dsm1th · Answer 3 · 2014-10-17T16:59:26.607

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.

that's good is there a way to tag each record so 1,2,3 so we know which records are connected to each other ... So for Cust A1 which Claim ids are selected and enumerate them. — Edgar, Oct 16 '14 at 22:06
the `serviceenddate` for the claim records in a contiguous date range block would all be the same (in #t) so it should be possible to fashion a `select .... over(partition by ...)` that would create the tag that you are describing. a problem would arise when a customer had two or more blocks of date ranges that had the same number of consecutive days. which block would you want to select? — dav1dsm1th, Oct 16 '14 at 22:47
if cust has same number of consecutive days then whichever is greater — Edgar, Oct 16 '14 at 22:50

score 0 · Answer 4 · answered Oct 17 '14 at 22:53

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)

Find consecutive days of service without a day break in between

4 Answers4