I have following dataset.
| Product | Timestamp |
|---|---|
| A | 2023-12-27 22:37:44.717 |
| A | 2023-12-27 22:38:39.403 |
| A | 2023-12-27 22:39:34.447 |
| B | 2023-12-27 22:40:28.733 |
| B | 2023-12-27 22:41:21.460 |
| A | 2023-12-27 22:43:09.917 |
| A | 2023-12-27 22:44:06.500 |
| C | 2023-12-27 22:44:59.263 |
| C | 2023-12-27 22:45:53.230 |
| C | 2023-12-27 22:46:46.597 |
| B | 2023-12-27 22:46:52.164 |
| B | 2023-12-27 22:47:54.531 |
I want to group by Product and display the min Timestamp and max Timestamp as StartDate/EndDate. But it shouldn´t ingore that the products changes. The desired output should look like this one.
| Product | StartDate | EndDate |
|---|---|---|
| A | 2023-12-27 22:37:44.717 | 2023-12-27 22:39:34.447 |
| B | 2023-12-27 22:40:28.733 | 2023-12-27 22:41:21.460 |
| A | 2023-12-27 22:43:09.917 | 2023-12-27 22:44:06.500 |
| C | 2023-12-27 22:44:59.263 | 2023-12-27 22:46:46.597 |
| B | 2023-12-27 22:46:52.164 | 2023-12-27 22:47:54.531 |
All my attempts failed miserably
You can use ROW_NUMBER and PARTITION to do that. Here is sample code
WITH GroupedData AS (
SELECT
Product,
[Timestamp],
ROW_NUMBER() OVER (ORDER BY [Timestamp]) AS rn,
ROW_NUMBER() OVER (PARTITION BY Product ORDER BY [Timestamp]) AS prn
FROM
TempDataset
)
SELECT
Product,
MIN([Timestamp]) AS StartDate,
MAX([Timestamp]) AS EndDate
FROM
GroupedData
GROUP BY
Product,
(rn - prn)
ORDER BY
MIN([Timestamp]);
Here is sample fiddle link