How can I create a "start" "end" time table from a timestamp list-CodePudding

I am trying to create a view that displays the time of employee stamps.

This is what the table looks like now:

Person	Person_Number	Date	Stamp_number	Time_Stamp
Paul	1	22-10-24	1	8:00
Paul	1	22-10-24	2	10:00
Paul	1	22-10-24	3	10:30
Paul	1	22-10-24	4	12:00
Jimmy	2	22-10-23	1	9:00
Jimmy	2	22-10-23	2	11:00
Jimmy	2	22-10-23	3	12:00

And I would like it to look like this using only a select query

Person	Person_Number	Date	Start	End	Duration
Paul	1	22-10-24	8:00	10:00	2:00
Paul	1	22-10-24	10:30	12:00	1:30
Jimmy	2	22-10-23	9:00	11:00	2:00
Jimmy	1	22-10-23	12:00	null	null

Is it possible ?

CodePudding user response：

We can use conditional aggregation along with a ROW_NUMBER trick:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Person_Number, Date
                                 ORDER BY Stamp_number) - 1 rn
    FROM yourTable
)

SELECT Person, Person_Number, Date,
       MAX(CASE WHEN rn % 2 = 0 THEN Time_Stamp END) AS [Start],
       MAX(CASE WHEN rn % 2 = 1 THEN Time_Stamp END) AS [End],
       DATEDIFF(MINUTE,
                MAX(CASE WHEN rn % 2 = 0 THEN Time_Stamp END),
                MAX(CASE WHEN rn % 2 = 1 THEN Time_Stamp END)) AS Duration
FROM cte
GROUP BY Person, Person_Number, Date, rn / 2
ORDER BY 2, 4;

Here is a working demo.

CodePudding user response：

Try the following:

SELECT Person, Person_Number, Date, [Start], [End],
  CONVERT(TIME(0), CONVERT(DATETIME, [End]) - CONVERT(DATETIME, [Start])) AS Duration
FROM
  (
    SELECT Person, Person_Number, Date, MIN(Time_Stamp) AS [Start], 
     CASE 
       WHEN MAX(Time_Stamp) <> MIN(Time_Stamp) 
       THEN MAX(Time_Stamp) 
     END AS [End] /* To select End as null when there is no End for a Start */
    FROM table_name
    GROUP BY Person, Person_Number, Date, (Stamp_number 1)/2
  ) T
ORDER BY Person_Number, Date, [Start]

See a demo.