Forum Discussion

vatzz8's avatar
vatzz8
Copper Contributor
Oct 18, 2023
Solved

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

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.

  • vatzz8's avatar
    vatzz8
    Oct 25, 2023
    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;

3 Replies

Sort By
  • vatzz8's avatar
    vatzz8
    Copper Contributor
    Oct 25, 2023
    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;
  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Oct 19, 2023
    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.

Resources