sql - 每小时分组需要与前一小时数据相加并与 SQL Server 中的另一个字段相减

Question

我必须使用 SQL Server 查询显示 1 小时内联络中心座席的登录和注销总数。

我开发了查询以获取一天中每小时的登录和注销计数。但我需要一个字段来显示

SUM(All the Login Count) - SUM(All the Logout Out) 

之前的时间间隔，并且需要在下一个时间间隔中显示为按小时登录，或者我们将以前的按小时登录数据与当前时间间隔的登录计数相加并减去时间间隔的当前注销计数。

预期数据:

截至目前的实际数据:

我也使用了 LAG 函数，但没有得到所需的输出。您能否纠正我在以下查询中出错的地方？

如果需要任何创建语句，请告诉我。

我的查询:

Declare @FromDate datetime
Declare @ToDate datetime
Declare @AgentID varchar(max);


set @FromDate='2020-05-22 00:00:00'; 
set @ToDate='2020-05-22 23:59:59'; 


;with 

LoginCount AS (
SELECT 
CONVERT(Date,A1.DateTime) AS Date,
    case DATEPART(HOUR,A1.DateTime)
        when 0 then '00:00-00:59' when 1 then '01:00-01:59' when 2 then '02:00-02:59' when 3 then '03:00-03:59' when 4 then '04:00-04:59' when 5 then '05:00-05:59' when 6 then '06:00-06:59' when 7 then '07:00-07:59'
        when 8 then '08:00-08:59' when 9 then '09:00-09:59' when 10 then '10:00-10:59' when 11 then '11:00-11:59' when 12 then '12:00-12:59' when 13 then '13:00-13:59' when 14 then '14:00-14:59' when 15 then '15:00-15:59'
        when 16 then '16:00-16:59' when 17 then '17:00-17:59' when 18 then '18:00-18:59' when 19 then '19:00-19:59' when 20 then '20:00-20:59' when 21 then '21:00-21:59' when 22 then '22:00-22:59' when 23 then '23:00-23:59'
    end AS INTERVAL,
    COUNT(Event) LoginCount
    FROM Agent_Event_Detail A1
    WHERE LoginDateTime BETWEEN @FromDate and @ToDate 
    AND A1.Event=1

GROUP BY CONVERT(Date,A1.DateTime), case DATEPART(HOUR,A1.DateTime)
        when 0 then '00:00-00:59' when 1 then '01:00-01:59' when 2 then '02:00-02:59' when 3 then '03:00-03:59' when 4 then '04:00-04:59' when 5 then '05:00-05:59' when 6 then '06:00-06:59' when 7 then '07:00-07:59'
        when 8 then '08:00-08:59' when 9 then '09:00-09:59' when 10 then '10:00-10:59' when 11 then '11:00-11:59' when 12 then '12:00-12:59' when 13 then '13:00-13:59' when 14 then '14:00-14:59' when 15 then '15:00-15:59'
        when 16 then '16:00-16:59' when 17 then '17:00-17:59' when 18 then '18:00-18:59' when 19 then '19:00-19:59' when 20 then '20:00-20:59' when 21 then '21:00-21:59' when 22 then '22:00-22:59' when 23 then '23:00-23:59'
    end 
)
--select * from LoginCount Order by INTERVAL

,LogoutCount AS (
SELECT 
CONVERT(Date,A1.DateTime) AS Date,
    case DATEPART(HOUR,A1.DateTime)
        when 0 then '00:00-00:59' when 1 then '01:00-01:59' when 2 then '02:00-02:59' when 3 then '03:00-03:59' when 4 then '04:00-04:59' when 5 then '05:00-05:59' when 6 then '06:00-06:59' when 7 then '07:00-07:59'
        when 8 then '08:00-08:59' when 9 then '09:00-09:59' when 10 then '10:00-10:59' when 11 then '11:00-11:59' when 12 then '12:00-12:59' when 13 then '13:00-13:59' when 14 then '14:00-14:59' when 15 then '15:00-15:59'
        when 16 then '16:00-16:59' when 17 then '17:00-17:59' when 18 then '18:00-18:59' when 19 then '19:00-19:59' when 20 then '20:00-20:59' when 21 then '21:00-21:59' when 22 then '22:00-22:59' when 23 then '23:00-23:59'
    end AS INTERVAL,
    COUNT(Event) LogoutCount
    FROM Agent_Event_Detail A1
    WHERE LoginDateTime BETWEEN @FromDate and @ToDate 
    AND A1.Event=2

GROUP BY CONVERT(Date,A1.DateTime), case DATEPART(HOUR,A1.DateTime)
        when 0 then '00:00-00:59' when 1 then '01:00-01:59' when 2 then '02:00-02:59' when 3 then '03:00-03:59' when 4 then '04:00-04:59' when 5 then '05:00-05:59' when 6 then '06:00-06:59' when 7 then '07:00-07:59'
        when 8 then '08:00-08:59' when 9 then '09:00-09:59' when 10 then '10:00-10:59' when 11 then '11:00-11:59' when 12 then '12:00-12:59' when 13 then '13:00-13:59' when 14 then '14:00-14:59' when 15 then '15:00-15:59'
        when 16 then '16:00-16:59' when 17 then '17:00-17:59' when 18 then '18:00-18:59' when 19 then '19:00-19:59' when 20 then '20:00-20:59' when 21 then '21:00-21:59' when 22 then '22:00-22:59' when 23 then '23:00-23:59'
    end 
)
,

--select * from LogoutCount Order by INTERVAL

cteRanked AS
(
   SELECT LoginCount, Date,INTERVAL, ROW_NUMBER() OVER(ORDER BY Date,INTERVAL) rownum
   FROM LoginCount
),
cteRanked1 AS
(
   SELECT LogoutCount, Date,INTERVAL, ROW_NUMBER() OVER(ORDER BY Date,INTERVAL) rownum
   FROM LogoutCount
),

HourlyLoginCount AS
(
SELECT ISNULL(c1.Date,c2.Date) LoginDate,ISNULL(c1.INTERVAL,c2.INTERVAL) LoginInterval,c1.LoginCount,
ISNULL(c2.Date,c1.Date)LogoutDate,ISNULL(c2.INTERVAL,c1.INTERVAL)LogoutInterval,c2.LogoutCount,
ISNULL(c1.LoginCount -c2.LogoutCount,0) [LoginDifference]
 from cteRanked c1 FULL JOIN cteRanked1 c2
ON c1.Date=c2.Date and c1.INTERVAL=c2.INTERVAL
)

select LoginDate,LoginInterval,ISNULL(LoginCount,0)LoginCount,ISNULL(LogoutCount,0)LogoutCount,LoginDifference,
(LoginCount)+(LAG(LoginDifference,1,0) OVER (PARTITION BY LoginDate, LoginInterval ORDER BY LoginDate,LoginInterval))-(LogoutCount)[Hourly Login Count] 
from HourlyLoginCount
order by LoginDate,LoginInterval

Answer 1

您可以使用 sum() over()

示例

Declare @YourTable Table ([LoginDate] date,LoginInterval varchar(50),[Login] int,[Logout] int)  Insert Into @YourTable Values 
 ('2020-05-22','00:00-00:59',6,5)
,('2020-05-22','01:00-01:59',1,0)
,('2020-05-22','04:00-04:59',3,0)
,('2020-05-22','05:00-05:59',71,1)
,('2020-05-22','06:00-06:59',112,23)

Select *
      ,Totals  = sum(Login) over (partition by LoginDate order by LoginInterval) 
                -sum(Logout) over (partition by LoginDate order by LoginInterval)
 from @YourTable

返回

LoginDate   LoginInterval   Login   Logout  Totals
2020-05-22  00:00-00:59     6       5       1
2020-05-22  01:00-01:59     1       0       2
2020-05-22  04:00-04:59     3       0       5
2020-05-22  05:00-05:59     71      1       75
2020-05-22  06:00-06:59     112     23      164

注意事项示例数据，因为文本比图像更有用