SQL Server how to loop over a filed data?

sqxo8psd 于 2023-03-11 发布在其他

关注(0)|答案(1)|浏览(118)

I have a table called OrderCheckinoutLog stores check-in and checkout actions on orders by some experts (In sql-server). The table is:

CREATE TABLE dbo.OrderCheckInOutLogs
(
 Id int NOT NULL CONSTRAINT PK_OrderCheckInoutLog PRIMARY KEY IDENTITY(1, 1),
 OrderId int NOT NULL,
 ExpertId int NOT NULL,
 OnCheckinoutStatus bit NOT NULL, /*New field to detect check-in by '0' and check-out status by '1'*/
 CheckinoutTime datetime2 (3) NOT NULL, /*New field to record check-in and check-out time according to field CheckinoutStatus*/
)
Go

Any checkout or check-in action is inserted as new record and this is specified by OnCheckinoutStatus. A service time is difference between checkout and check-in time. I want to calculate the average service time of each expert using a query. Is there any way (without cursor)?

A query that lists expert Id and average of service time.

sql-server

来源：https://stackoverflow.com/questions/75685307/how-to-loop-over-a-filed-data

1条答案

按热度按时间

kupeojn61#

You could do something like this:

select expertId, AVG(processedTime)
FROM (
  select expertId, SUM(DATEDIFF(minute, chkin.CheckinoutTime, 
  outAtime.chkoutTime)) processedTime, OrderID
  from OrderCheckInOutLogs chkin
  cross apply (
    select top 1 CheckinoutTime as chkoutTime
    from OrderCheckInOutLogs chkout
    where chkout.expertId = chkin.expertid
    and chkout.orderId = chkin.OrderId
    and chkout.OnCheckinoutStatus = 1 --checkout
    and chkout.CheckinoutTime >= chkin.CheckinoutTime
    order by CheckinoutTime
  ) outaTime
  where chkin.OnCheckinoutStatus = 0 -- checkin
  group by expertId, OrderId
) x
GROUP BY expertId

We gather all checkins, and then for each one, get the first checkout that happens after the checkin. I do this per OrderId, this allows multiple checkins to be counted in. I also firstly summarize per Order, so that multiple order checkin doesn't get skewered when doing average.

Finally i average the summarized processedTime per order to get total average time.

You probably do well to create an index on OnCheckinoutStatus, ExpertId, OrderId on your table.

赞(0）回复(0）举报 2023-03-11

我来回答

SQL Server how to loop over a filed data?

1条答案

相关问题

热门标签

最新问答