How compare work hour with the avg of current week?

Lora 200 Reputation points
2023-10-31T02:51:12.11+00:00

Need to calculate the average work hour of each week, and then compare with work hour of each workday.

Here is the demo table:

create table demo (employeeid int,work_start datetime,work_end datetime)
insert into demo values
(10011,'2023-10-26 08:20:45.000','2023-10-26 17:34:13.000'),
(10011,'2023-10-27 08:23:34.000','2023-10-27 17:33:25.000'),
(10011,'2023-10-30 08:25:18.000','2023-10-30 17:31:42.000'),
(10011,'2023-10-31 08:22:56.000','2023-10-31 17:32:12.000')
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,948 questions
{count} votes

Accepted answer
  1. LiHongMSFT-4306 27,961 Reputation points
    2023-10-31T06:19:05.8866667+00:00

    Hi @Lora

    Try this query:

    ;WITH CTE AS
    (
    SELECT employeeid
          ,CONVERT(date, work_start) AS WorkDate
    	  ,CAST(work_end - work_start AS time(0)) AS WorkTime 
    FROM demo
    )
    SELECT *,CONVERT(VARCHAR(255), CAST(AVG(CAST(CAST(WorkTime AS DATETIME) AS DECIMAL(10,5)))OVER(PARTITION BY DATEPART(WEEK,WorkDate)) AS DATETIME), 108)AS Avg_WorkTime
    FROM CTE
    

    Best regards,

    Cosmog Hong

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.