SQL查询为用户生成每周和每日日志(使用SQL Server 2016)

Question

我有以下格式的表结构，需要生成用户每天、每周和每月在现场花费的总时间(小时和分钟(，需要sql查询来生成报告。

表名LoginReport

userid                             LoginTime                    LogoutTime
PersonA                          2018-11-19 13:02:33.780      2018-11-19 13:03:44.793
PersonA                           2018-11-19 13:11:06.370    2018-11-19 13:12:17.290
PersonB                          2018-11-19 13:00:03.257     2018-11-19 13:01:23.067
PersonB                          2018-11-19 12:38:35.400     2018-11-19 12:39:04.843

预期结果

userid                                    Daily_Duration      Weekly_duration
PersonA                                  X hrs y mins         X hrs y mins
PersonB                                  X hrs y mins         X hrs y mins

Answer 1

您可以在下面尝试

with cte as 
(
select userid, cast(LoginTime as date) as ldate, DATEPART( wk, cast(LoginTime as date)) as wk,LoginTime,LogoutTime
from tablename
)
select userid,daily_duration,weekly_duration from
(
select userid, ldate,wk,datediff(hh,min(LoginTime),max(LogoutTime)) as daily_duration from cte
group by userid,ldate,wk
)A inner join 
(
select userid,wk,datediff(hh,min(LoginTime),max(LogoutTime)) as weekly_duration from cte
group by userid,wk
)B on A.userid=B.userid and a.wk=b.wk

Answer 2

每日持续时间意味着您需要指定等情况

需要日期和月份等列

否则，您需要输入需要输出的日期和月份的参数查询可以给出所有每日和每月持续时间的平均值

我认为以下功能对你来说是完全使用

CREATE FUNCTION  [DBO].[ConvertMinToHours]
(
@Min int

)
RETURNS varchar(30) 
AS
BEGIN
DECLARE @HOUR INT = (@Min/60)
set @Min=@Min-(@HOUR*60)
declare @InHours varchar(30)=CONCAT(@HOUR,'hrs',Space(1),@Min,'min')
return @InHours
END



SELECT USERID,[DBO].[ConvertMinToHours](sum (DATEDIFF (mi,LOGINTIME,LOGOUTTIME))) 
FROM LOGINREPORT group by userId, cast(LOGINTIME as DATE)

Answer 3

这个对我有用：(

select t2.USERID,Data,Data1 from  
(SELECT USERID,(sum (case when Logouttime is null or Logouttime ='' then 0 else datediff(Minute,LoginTime,Logouttime) end)) as Data
FROM LOGINREPORT  where datepart(dd,loginTime)=datepart(dd,'2018-11-20 13:11:06.370') group by userId, cast(LOGINTIME as DATE)) as t1
right outer join    
(SELECT USERID,(sum (case when Logouttime is null or Logouttime ='' then 0 else datediff(Minute,LoginTime,Logouttime) end)) as Data1 
FROM LOGINREPORT  where datepart(ww,loginTime)=datepart(ww,'2018-11-19 13:11:06.370') group by userId, cast(LOGINTIME as DATE)) as t2 
on t1.UserID=t2.UserID