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
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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')
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