SQL Server Group Date column and display it as a date range based on grouping another column and only group if the next row has same value of grouping

sbdsn5lh 于 11个月前发布在其他

关注(0)|答案(1)|浏览(110)

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. Below are the input and required output tables. Please help me out with this.

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|

Required Output

Input and Output

sql-server

来源：https://stackoverflow.com/questions/77312847/group-date-column-and-display-it-as-a-date-range-based-on-grouping-another-colum

1条答案

按热度按时间

zf9nrax11#

I finally found a solution to this. Below is the code for same. The code 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;

赞(0）回复(0）举报 11个月前

我来回答

SQL Server Group Date column and display it as a date range based on grouping another column and only group if the next row has same value of grouping

1条答案

相关问题

热门标签

最新问答