2

Assuming I have the following table:

date         version  
2015-02-01   v1
2015-02-02   v1
2015-02-03   v1
2015-02-04   v1
2015-02-02   v2
2015-02-03   v2
2015-02-08   v1
2015-02-09   v1

My query shall group dates in such way I get this result:

datefrom      dateto      version
2015-02-01    2015-02-04  v1
2015-02-08    2015-02-09  v1
2015-02-02    2015-02-03  v2

I tried something with 

LEAD(date) OVER(PARTITION BY version ORDER BY date)

but it did not really pay off since I cannot group the "bins" of dates. I want to group each subsequent date into on bin allowing no date gap.

ekad
  • 14,436
  • 26
  • 44
  • 46
tuxmania
  • 906
  • 2
  • 9
  • 28

1 Answers1

6

You can do this with comparing row number with datediff to a fixed date, and if the difference of those changes, then there must be at least a gap of one day. So, something like this:

select
  version,
  min (date) as datefrom,
  max (date) as dateto
from
  (
    select
      version,
      date,
      datediff(day, '20100101', date) 
      - row_number() over (partition by version order by date) as bin
    from
      version
  ) T
group by
  version,
  bin
order by
  version,
  datefrom
James Z
  • 12,209
  • 10
  • 24
  • 44
  • i do use sql server 2012, can you explain how this query can be simplified with lag/lead? – tuxmania Mar 01 '15 at 19:54
  • 1
    Sorry, of course lag / lead are good for finding the gaps, not the islands themselves. I think this is the best way in 2012 too. – James Z Mar 01 '15 at 20:22
  • You sir are a genious :) i really tried some time to even get your idea of binning. I got it now and i thought i already saw everything ;). Thank you for that challenging moment. – tuxmania Mar 01 '15 at 20:24