Group Date column and display it as a date range based on grouping another column

[vatzz8]

I am trying to group date column and display it as a date range. This grouping , however is based on 2 different non-date grouping columns. I am trying to do this in SQL Server Management Studio. I tried a lot of methods but either the method is not working or grouping everything together by either columns. Below is an attached image which shows the input and the required output.

I tried Recursive CTE, LAG, Lead methods, Partition by methods. I am either doing it in a wrong way of its is just not giving me the output.

Below are the required tables.

Input Table-

| ID       | Grouping Col 2 | Census date |

| -------- | -------------- | ----------- |

| 1        | Block-1        |   7/2/2022  |

| 1        | Block-1        |   7/3/2022  |

| 1        | Block-1        |   7/4/2022  |

| 1        | Block-2        |   7/5/2022  |

| 1        | Block-2        |   7/6/2022  |

| 1        | Block-2        |   7/7/2022  |

| 1        | Block-2        |   7/8/2022  |

| 1        | Block-1        |   7/9/2022  |

| 1        | Block-1        |   7/10/2022 |

| 1        | Block-1        |   7/11/2022 |

| 1        | Block-1        |   7/12/2022 |

| 1        | Block-1        |   7/13/2022 |

| 2        | Block-2        |   9/14/2022 |

| 2        | Block-2        |   9/15/2022 |

| 2        | Block-3        |   9/16/2022 |

| 2        | Block-3        |   9/17/2022 |

| 2        | Block-3        |   9/18/2022 |

| 2        | Block-3        |   9/19/2022 |

| 2        | Block-3        |   9/20/2022 |

| 3        | Block-2        |   10/21/2022 |

| 3        | Block-2        |   10/22/2022 |

| 3        | Block-1        |   10/23/2022 |

| 3        | Block-1        |   10/24/2022 |

| 3        | Block-2        |   10/25/2022 |

| 3        | Block-2        |   10/26/2022 |

Output Table-

| ID       | Grouping Col 2 | Start Date  | End Date  |

| -------- | -------------- | ----------- | --------- |

| 1        | Block-1        |   7/2/2022  | 7/4/2022  |

| 1        | Block-2        |   7/5/2022  | 7/8/2022  |

| 1        | Block-1        |   7/9/2022  | 7/13/2022 |

| 2        | Block-2        |   9/14/2022 | 9/15/2022 |

| 2        | Block-3        |   9/16/2022 | 9/20/2022 |

| 3        | Block-2        |   10/21/2022| 10/22/2022|

| 3        | Block-1        |   10/23/2022| 10/24/2022|

| 3        | Block-2        |   10/25/2022| 10/26/2022|

Below is the input and required output.

[#2]

I can't see anything in that micro-small screenshot and even if; I can't query screenshots.

Please post table design as DDL, some sample data as DML statement and the expected result.

[#3]

@olafhelper hi I have posted a text version of the tables for your reference

[#4 · Accepted solution]

I found a solution for this . The solution is in a Create View Statement.
Answer-
CREATE VIEW YourViewName AS
SELECT
ID,
[Grouping Col 2],
MIN([Census date]) AS [Start Date],
MAX([Census date]) AS [End Date]
FROM (
SELECT
ID,
[Grouping Col 2],
[Census date],
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [Census date]) -
ROW_NUMBER() OVER (PARTITION BY ID, [Grouping Col 2] ORDER BY
[Census date]) AS grp
FROM YourInputTable ) AS subquery
GROUP BY ID, [Grouping Col 2], grp;