How to make a specific group by (window like function)

Question

Bellow you can see the Table and context. I want to get 3 groups from the context in the table, so i want to group by ABDC_IDENT but when the DATE_RANK order breaks as you can see in the data after DATE_RANK 11 comes 1,2 (because it is the group B) then it continues to rank up for the group A (the order by is by VARIOUS_DATES).

What i want to get is 3 groups, the first is group A rank 1 to 11, the second group is B rank 1,2 and the third group should be the group A but ranks from 12 to 21. I hope this is clear for everyone.

Im currently experimenting with rows between UNBOUNDED PRECEDING and current row, any idea is welcomed, maybe it can be done in some other way also. Cheers and thx

Here is my fiddle, so you can build it easy for yourself:

CREATE TABLE Table1
    (ABDC_IDENT varchar(5), VARIOUS_DATES date, DATE_RANKS int)
;

INSERT INTO Table1
    (ABDC_IDENT, VARIOUS_DATES, DATE_RANKS)
VALUES
    ('A', '31.12.2010', 1),
    ('A', '31.01.2011', 2),
    ('A', '28.02.2011', 3),
    ('A', '31.03.2011', 4),
    ('A', '29.04.2011', 5),
    ('A', '31.05.2011', 6),
    ('A', '30.06.2011', 7),
    ('A', '29.07.2011', 8),
    ('A', '31.08.2011', 9),
    ('A', '30.09.2011', 10),
    ('A', '31.10.2011', 11),
    ('B', '30.11.2011', 1),
    ('B', '30.12.2011', 2),
    ('A', '31.01.2012', 12),
    ('A', '29.02.2012', 13),
    ('A', '30.03.2012', 14),
    ('A', '30.04.2012', 15),
    ('A', '31.05.2012', 16),
    ('A', '29.06.2012', 17),
    ('A', '31.07.2012', 18),
    ('A', '31.08.2012', 19),
    ('A', '28.09.2012', 20),
    ('A', '31.10.2012', 21)
;

The desired result would be then inserted into another table

Table2

  GROUP_ABC |  MIN_DATE |  MAX_DATE  |
       A    |31.12.2010 | 31.10.2011 |
       B    |30.11.2011 | 30.12.2011 |
       C    |31.01.2012 | 31.10.2012 |

Answer 1

I think you can use convert format 104 to handle the date strings.

Does this work?

select
    substring('ABCDEF', row_number() over (order by min(VARIOUS_DATES)), 1) as GROUP_ABC,
    min(VARIOUS_DATES) as MIN_DATE,
    max(VARIOUS_DATES) as MAX_DATE
from (
    select
        ABDC_IDENT,
        convert(date, VARIOUS_DATES, 104) as VARIOUS_DATES
        row_number() over (order by convert(date, VARIOUS_DATES, 104)) - DATE_RANKS as grp
    from Table1
) data
group by ABDC_IDENT, grp

or:

select
    substring('ABCDEF', row_number() over (order by MIN_DATE), 1) as GROUP_ABC,
    MIN_DATE, MAX_DATE
from (
    select
        ABDC_IDENT as GROUP_ABC,
        min(VARIOUS_DATES) as MIN_DATE,
        max(VARIOUS_DATES) as MAX_DATE
    from (
        select
            ABDC_IDENT,
            convert(date, VARIOUS_DATES, 104) as VARIOUS_DATES
            row_number()
                over (order by convert(date, VARIOUS_DATES, 104)) - DATE_RANKS as grp
    from Table1
    ) data
    group by ABDC_IDENT, grp
) t