SQL - Find end date and start date according to timestamp record

Question

Currently I have the following table, and I want to aggregate it to some kind of a log of the status changes for each asset_id, that will include a start and end date.

ie, If this is my data:

Asset_Id	Status_Id	Update_Date
1	1	2021-06-29 01:00:00
1	1	2021-06-29 04:00:00
1	3	2021-06-29 05:00:00
1	3	2021-06-29 05:00:31
1	1	2021-06-29 05:01:00
1	1	2021-06-29 05:08:00
1	2	2021-06-30 12:12:12
1	2	2021-06-30 12:15:12
1	2	2021-07-30 04:12:12

I would like to aggregate it to:

Asset_Id	Status_Id	Start_date	End_Date
1	1	2021-06-29 01:00:00	2021-06-29 04:59:59
1	3	2021-06-29 05:00:00	2021-06-29 05:00:59
1	1	2021-06-29 05:01:00	2021-06-30 12:12:11
1	2	2021-06-30 12:12:12	2099-12-31 00:00:0

I've found similar proposed solutions, but none of them included the actual status_id and the end_date didn't included the time until the next row start time minus a second or minute, ie Get list with start and end values from table of datetimes

This is still a gaps and island problem; you just need to the use `LEAD` to get the "max". — Thom A, Jun 27 '22 at 17:11

Yitzhak Khabinsky · Answer 1 · 2022-06-27T23:04:23.967

Please try the following solution.

SQL

DECLARE @tbl TABLE (Asset_Id INT, Status_Id INT, Update_Date DATETIME);
INSERT @tbl (Asset_Id, Status_Id, Update_Date) VALUES
(1, 1, '2021-06-29 01:00:00'),
(1, 1, '2021-06-29 04:00:00'),
(1, 3, '2021-06-29 05:00:00'),
(1, 3, '2021-06-29 05:00:31'),
(1, 1, '2021-06-29 05:01:00'),
(1, 1, '2021-06-29 05:08:00'),
(1, 2, '2021-06-30 12:12:12'),
(1, 2, '2021-06-30 12:15:12'),
(1, 2, '2021-07-30 04:12:12');

;WITH rs AS
(
    SELECT * 
        , series = status_id + ROW_NUMBER() OVER (ORDER BY Update_Date ASC) - 
            ROW_NUMBER() OVER (PARTITION BY Asset_ID, Status_Id ORDER BY Update_Date ASC)
    FROM @tbl
)
SELECT Asset_Id, Status_Id, MIN(Update_Date) AS Start_date, MAX(rs.Update_Date) AS End_Date
FROM rs
GROUP BY Asset_Id, Status_Id, series
ORDER BY MIN(Update_Date) ASC;

SQL #2

To satisfy the following

the end_date didn't included the time until the next row start time minus a second

;WITH rs AS
(
    SELECT * 
        , series = status_id + ROW_NUMBER() OVER (ORDER BY Update_Date ASC) - 
            ROW_NUMBER() OVER (PARTITION BY Asset_Id, Status_Id ORDER BY Update_Date ASC)
        , DATEADD(SECOND,-1, LEAD(Update_Date,1) OVER (ORDER BY Update_Date ASC)) AS end_date
    FROM @tbl
)
SELECT Asset_Id, Status_Id, MIN(rs.Update_Date) AS Start_date, MAX(COALESCE(rs.end_date,'2099-12-31')) AS End_Date
FROM rs
GROUP BY Asset_Id, Status_Id, series
ORDER BY MIN(rs.Update_Date) ASC;

Output

+----------+-----------+-------------------------+-------------------------+
| Asset_Id | Status_Id |       Start_date        |        End_Date         |
+----------+-----------+-------------------------+-------------------------+
|        1 |         1 | 2021-06-29 01:00:00.000 | 2021-06-29 04:00:00.000 |
|        1 |         3 | 2021-06-29 05:00:00.000 | 2021-06-29 05:00:31.000 |
|        1 |         1 | 2021-06-29 05:01:00.000 | 2021-06-29 05:08:00.000 |
|        1 |         2 | 2021-06-30 12:12:12.000 | 2021-07-30 04:12:12.000 |
+----------+-----------+-------------------------+-------------------------+

Output #2

+----------+-----------+-------------------------+-------------------------+
| Asset_Id | Status_Id |       Start_date        |        End_Date         |
+----------+-----------+-------------------------+-------------------------+
|        1 |         1 | 2021-06-29 01:00:00.000 | 2021-06-29 04:59:59.000 |
|        1 |         3 | 2021-06-29 05:00:00.000 | 2021-06-29 05:00:59.000 |
|        1 |         1 | 2021-06-29 05:01:00.000 | 2021-06-30 12:12:11.000 |
|        1 |         2 | 2021-06-30 12:12:12.000 | 2099-12-31 00:00:00.000 |
+----------+-----------+-------------------------+-------------------------+

Sample data may only have one Asset_ID, I suspect the real-world data has more. Perhaps add Asset_ID to the Partition By — John Cappelletti, Jun 27 '22 at 19:02
Your solution is clean, though the "*End_Date*" values do not match with the required output: this attempt probably needs some additional tweaking. — lemon, Jun 27 '22 at 21:50
IMHO, the desired output's *End_Date* column values are not matching the initial data in the question by OP's mistake. — Yitzhak Khabinsky, Jun 27 '22 at 21:56
"*I've found similar proposed solutions, but none of them included the actual status_id and the end_date didn't included the time until the next row start time minus a second or minute*" - I guess OP was clear about that, but never say never, maybe you're right in some ways. — lemon, Jun 27 '22 at 21:59
Output #2 indeed provide the solution I've asked for. Partition for Asset_Id is needed as well as other ids can be included in the dataset other than '1' — Nimrod Shenhav, Jun 28 '22 at 12:17

score 0 · Answer 2 · answered Jun 28 '22 at 12:21

I've followed Larnu recommendation on the op comment and adjusted the SQL script to the below, this is provided me what I need

WITH EndsMarked
AS
    (SELECT F.Asset_Id
          , F.Status_Id
          , F.Creation_Date
          , CASE
                 WHEN LAG(F.Status_Id, 1) OVER (PARTITION BY F.Asset_Id
                                                ORDER BY F.Asset_Id
                                                       , F.Creation_Date
                                               ) IS NULL
                      AND ROW_NUMBER() OVER (PARTITION BY F.Asset_Id
                                             ORDER BY F.Creation_Date
                                            ) = 1 THEN 1
                 WHEN LAG(F.Status_Id, 1) OVER (PARTITION BY F.Asset_Id
                                                ORDER BY F.Asset_Id
                                                       , F.Creation_Date
                                               ) <> LAG(F.Status_Id, 0) OVER (PARTITION BY F.Asset_Id
                                                                              ORDER BY F.Asset_Id
                                                                                     , F.Creation_Date
                                                                             ) THEN 1
                 ELSE 0
            END AS IS_START
          , CASE
                 WHEN LEAD(F.Status_Id, 1) OVER (PARTITION BY F.Asset_Id
                                                 ORDER BY F.Asset_Id
                                                        , F.Creation_Date
                                                ) IS NULL
                      AND ROW_NUMBER() OVER (PARTITION BY F.Asset_Id
                                             ORDER BY F.Creation_Date DESC
                                            ) = 1 THEN 1
                 WHEN LEAD(F.Status_Id, 0) OVER (PARTITION BY F.Asset_Id
                                                 ORDER BY F.Asset_Id
                                                        , F.Creation_Date
                                                ) <> LEAD(F.Status_Id, 1) OVER (PARTITION BY F.Asset_Id
                                                                                ORDER BY F.Asset_Id
                                                                                       , F.Creation_Date
                                                                               ) THEN 1
                 ELSE 0
            END AS IS_END
     FROM
            (
            SELECT mrsabda.Assets_AssetId        AS Asset_Id
                 , mrsabda.CreationDate          AS Creation_Date
                 , mrsabda.Assets_Asset_StatusId AS Status_Id
            --,[Aantal Facturen]
            FROM   MRR.MRR_Round_Status_Audit_Buildup_Dim_Asset AS mrsabda
            ) AS F )
   , GroupsNumbered
AS
    (SELECT EndsMarked.Asset_Id
          , EndsMarked.Status_Id
          , EndsMarked.Creation_Date
          , EndsMarked.IS_START
          , EndsMarked.IS_END
          , COUNT(   CASE
                          WHEN EndsMarked.IS_START = 1 THEN 1
                     END
                 ) OVER (ORDER BY EndsMarked.Asset_Id
                                , EndsMarked.Creation_Date
                        ) AS GroupNum
     FROM   EndsMarked
     WHERE  EndsMarked.IS_START = 1
            OR EndsMarked.IS_END = 1)
SELECT   a.Asset_Id
       , a.Status_Id
       , a.GROUP_START                                                                                   AS Start_Date
       , DATEADD(SECOND, -1, LEAD(a.GROUP_START, 1, '2099-12-31 00:00:01') OVER (ORDER BY a.GROUP_START)) AS End_Date
FROM
         (
         SELECT   GroupsNumbered.Asset_Id
                , GroupsNumbered.Status_Id
                , MIN(GroupsNumbered.Creation_Date) AS GROUP_START
                , MAX(GroupsNumbered.Creation_Date) AS GROUP_END
         FROM     GroupsNumbered
         GROUP BY GroupsNumbered.Asset_Id
                , GroupsNumbered.Status_Id
                , GroupsNumbered.GroupNum
         ) AS a
GROUP BY a.Asset_Id
       , a.Status_Id
       , a.GROUP_START

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Jun 29 '22 at 20:05

SQL - Find end date and start date according to timestamp record

2 Answers2