Select query

Transact-SQL
1

Hello

I have a table that contains data as follows:

col1 Date
100 01.02.2015
100 02.02.2015
100 02.03.2015
100 02.04.2015
100 02.05.2015
100 02.06.2015
100 02.07.2015
100 02.15.2015
100 02.16.2015
100 02.20.2015
100 02.21.2015
100 02.22.2015
100 02.25.2015
100 02.26.2015

I need to read the recording in groups of three successive,

the result should be as follows:

100 01.02.2015
100 02.02.2015
100 02.03.2015

100 02.04.2015
100 02.05.2015
100 02.06.2015

100 02.20.2015
100 02.21.2015
100 02.22.2015

only, because the 02.07.2015 has no following days

15 and 16 as well as 26

I used a cursor, but it's complicated

Thank you

2

Why these 3 rows are considered ?

3 
; with 
tbl as    -- sample data
(
    select    col = 100, date = convert (date, '02.01.2015') union all
    select    col = 100, date = convert (date, '02.02.2015') union all
    select    col = 100, date = convert (date, '02.03.2015') union all
    select    col = 100, date = convert (date, '02.04.2015') union all
    select    col = 100, date = convert (date, '02.05.2015') union all
    select    col = 100, date = convert (date, '02.06.2015') union all
    select    col = 100, date = convert (date, '02.07.2015') union all
    select    col = 100, date = convert (date, '02.15.2015') union all
    select    col = 100, date = convert (date, '02.16.2015') union all
    select    col = 100, date = convert (date, '02.20.2015') union all
    select    col = 100, date = convert (date, '02.21.2015') union all
    select    col = 100, date = convert (date, '02.22.2015') union all
    select    col = 100, date = convert (date, '02.25.2015') union all
    select    col = 100, date = convert (date, '02.26.2015') 
),
cte as    -- assign rn for each row
(
    select    *, 
        rn    = row_number() over (partition by col order by date)
    from    tbl
),
rcte as    -- recursive CTE to identify grp and seq by grp
(
    select    col, date, rn, grp = 1, seq = 1
    from    cte
    where    rn    = 1

    union all

    select    c.col, c.date, c.rn, 
        grp    = case when dateadd(day, 1, r.date) = c.date
                then r.grp
                else r.grp + 1
                end,
        seq     = case when dateadd(day, 1, r.date) = c.date
                then r.seq + 1
                else 1
                end
    from    rcte r
        inner join cte c    on    r.col    = c.col
                    and    r.rn    = c.rn - 1
), 
cte2 as    -- max seq no
(
    select  *, m = max(seq) over (partition by grp) / 3 * 3
    from    rcte
)
select  col, date
from    cte2
where   seq    <= m
4

Thank you khtan
your query works perfectly
how can I recover the limits of dates as follows:

My example groups by 2, not 3,
the result is good:

001078 2015-11-02 00: 00: 00.000
001078 2015-11-03 00: 00: 00.000
001078 2015-11-04 00: 00: 00.000
001078 2015-11-05 00: 00: 00.000
001078 2015-11-25 00: 00: 00.000
001078 2015-11-26 00: 00: 00.000

now I need to read like this

from 02.11.2015 to 03.11.2015
from 04.11.2015 to 05.11.2015
from 25.11.2015 to 26.11.2015

I tried grouped by grp, it gives me:
from 02.11.2015 to 05.11.2015 (4)
from 25.11.2015 to 26.11.2015 (2)

5

change to as follows

cte2 as    -- max seq no
(
    select  *, m = max(seq) over (partition by grp) / 2 * 2,
            grp2 = (row_number() over (partition by grp order by date) - 1) / 2 + 1
    from    rcte
)
select  col, min(date), max(date)
from    cte2
where   seq    <= m
group by col, grp, grp2
6

thank you very much
It's awesome
I could never write a query like this alone
now I'll be a little more demanding
In the list of data, I could have holes on weekly rest days or public holidays
my result if for example I have this:
01/02/2015
02/02/2015
02/03/2015
02/04/2015
02/16/2015
02/17/2015
02/18/2015
if 02.03.2015 is a holiday, my counter skip the 3 day 3 and show me
01/02/2015
02/02/2015
02/04/2015
as group 3

Thank you