Forum Discussion
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 t...
- 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;
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;
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;