Cumulative count to group in SQL

Question

I have a query that returns a list of dates, now what I want to achieve is to group them and identify the grouping with a null.

Example Query:

SELECT  *,
ROW_NUMBER() OVER(PARTITION BY Date1.Date1 ORDER BY Date1.Date1) as rwnumber
FROM Date1
    LEFT JOIN Date2
        ON Date1.Date1= Date2.Date1

OUTPUT:

ID     DATE1         DATE2         rwnumber
1    06-10-2019    06-10-2019         1
1    06-25-2019    06-25-2019         1
1    07-10-2019    NULL               1
1    07-25-2019    07-25-2019         1    
1    08-10-2019    08-10-2019         1

What I want to achieve is this desired OUTPUT:

ID     DATE1         DATE2         rwnumber
1    06-10-2019    06-10-2019         1
1    06-25-2019    06-25-2019         1
1    07-10-2019    NULL               2 or 0
1    07-25-2019    07-25-2019         3    
1    08-10-2019    08-10-2019         3

So I can group this dates to two group.

Other Example Output:

ID     DATE1         DATE2         rwnumber
1    06-10-2019    06-10-2019        group 1
1    06-25-2019    NULL               
1    07-10-2019    07-10-2019        group 2      
1    07-25-2019    07-25-2019        group 2    
1    08-10-2019    08-10-2019        group 2

ID     DATE1         DATE2         rwnumber
1    06-10-2019    06-10-2019        group 1
1    06-25-2019    06-25-2019        group 1               
1    07-10-2019    07-10-2019        group 1      
1    07-25-2019    NULL                  
1    08-10-2019    08-10-2019        group 2

Answer 1

You seem to want a cumulative count of NULL values:

select t.*,
       (case when date2 is null then 0
             else 1 + sum(case when date2 is null then 1 else 0 end) over (order by date1)
        end) as grouping
from t;

This returns grouping values of 1, 2, 3 and so on for the groups. The NULL values all have a value of 0. This seems quite consistent with what you want to do.