How to get average of earliest datetime per day?-CodePudding

I need to get average time based on their earliest check in for each day.

For example, these are my check in datetime.

Person | Checkin
-----------------
Jack    2022-01-06 16:42:34.000
Jack    2022-01-06 17:30:34.000
Jack    2022-01-07 10:22:34.000
Jack    2022-01-07 12:12:54.000
Jack    2022-01-08 11:08:53.000

When I want to calculate my average check in time based on earliest check in, I should only consider these datetime.

2022-01-06 16:42:34.000
2022-01-07 10:22:34.000
2022-01-08 11:08:53.000

This is my current sql to get the average check in time. But this consider all the datetime above and gets the average time.

Select Person,(CAST(DATEADD(SS, AVG(CAST(DATEDIFF(SS, '00:00:00', CAST(Checkin AS TIME)) AS BIGINT)), '00:00:00' ) AS TIME)) AS 'AvgCheckInTime' from #Tab_CheckIn
group by Person

How can I only consider the earliest datetime for each day and get the average time?

CodePudding user response：

This is a little messy.

Firstly you need to group the data to get the earliest checkin my day. That's quite simple, using CONVERT to date and MIN.

Then you need to get the "average" of those times. You can't average a time in SQL Server, however, as time represents a point in time during the day not an interval, so it doesn't make sense to average them like it would a timespan. As a result you need to "convert" the values to a numerical value.

In this case I use DATEDIFF to get the number of seconds since midnight to the checkin converted to a time. Then I can average those values, and finally add those second to midnight:

WITH Earliest AS(
    SELECT Person,
           MIN(Checkin) AS EarliestCheckin
    FROM dbo.YourTable
    GROUP BY Person,
             CONVERT(date,Checkin))
SELECT Person,
       DATEADD(SECOND,AVG(DATEDIFF(SECOND,'00:00',CONVERT(time,EarliestCheckin))),CONVERT(time,'00:00')) AS AverageCheckin
FROM Earliest
GROUP BY Person;

CodePudding user response：

Assuming you get desired result(except filtering the undesired dates)

Select t.Person,(CAST(DATEADD(SS, AVG(CAST(DATEDIFF(SS, '00:00:00', CAST(t.Checkin AS TIME)) AS BIGINT)), '00:00:00' ) AS TIME)) AS 'AvgCheckInTime'
from (SELECT Person, min(Checkin) as Checkin FROM #Tab_CheckIn
      GROUP BY Person, cast(Checkin As Date)) t
group by t.Person

CodePudding user response：

SELECT Person,(CAST(DATEADD(SS, AVG(CAST(DATEDIFF(SS, '00:00:00', CAST(Checkin AS TIME)) AS BIGINT)), '00:00:00' ) AS TIME)) AS 'AvgCheckInTime'
from (
SELECT person, checkin, row_number() over(PARTITION by person, CAST(checkin as DATE) order by checkin asc) as row 
FROM #Tab_CheckIn) as p
where p.row =1
group by p.person