时间戳

首页 > 解决方案 > 每小时分组需要与前一小时数据相加并与 SQL Server 中的另一个字段相减

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

问题描述

我必须使用 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

标签： sqlsql-servertsqldatewindow-functions

解决方案

您可以使用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

只是一个说明：作为文本的示例数据比图像更有用

推荐阅读