34,576
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE #t_history(
[h_person] [nvarchar](50) NULL,--员工
[h_opt] [int] NULL,--借出状态,0刷卡未操作,1拿走设备,2归还设备,3逾期归还设备
[h_time] [datetime] NULL,--刷卡时间
--[padinfo_id] [nvarchar](50) NULL,--设备编号
[Department_ID] [nvarchar](50) NULL--部门编号
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[#t_history] ADD CONSTRAINT [DF__#t_history__h_per__5070F446] DEFAULT (NULL) FOR [h_person]
GO
ALTER TABLE [dbo].[#t_history] ADD CONSTRAINT [DF__#t_history__h_opt__5165187F] DEFAULT (NULL) FOR [h_opt]
GO
ALTER TABLE [dbo].[#t_history] ADD CONSTRAINT [DF__#t_history__h_tim__52593CB8] DEFAULT (NULL) FOR [h_time]
GO
insert into #t_history values('tsmc0001',1,'2021-06-26 09:39:00.000','A01')
insert into #t_history values('tsmc0003',1,'2021-06-26 10:39:00.000','A02')
insert into #t_history values('tsmc0001',0,'2021-06-26 11:39:00.000','A01')
insert into #t_history values('tsmc0003',2,'2021-06-26 12:39:00.000','A02')
insert into #t_history values('tsmc0001',2,'2021-06-26 13:39:00.000','A01')
insert into #t_history values('tsmc0001',1,'2021-06-26 14:39:00.000','A01')
insert into #t_history values('tsmc0004',1,'2021-06-26 15:39:00.000','A03')
insert into #t_history values('tsmc0001',0,'2021-06-26 16:39:00.000','A02')
insert into #t_history values('tsmc0004',2,'2021-06-26 17:39:00.000','A03')
insert into #t_history values('tsmc0001',0,'2021-06-26 18:39:00.000','A02')
insert into #t_history values('tsmc0001',2,'2021-06-26 19:39:00.000','A02')
insert into #t_history values('tsmc0001',1,'2021-06-26 20:39:00.000','A01')
insert into #t_history values('tsmc0001',3,'2021-06-28 20:39:00.000','A01')
insert into #t_history values('tsmc0001',0,'2021-06-28 21:39:00.000','A02')
insert into #t_history values('tsmc0001',2,'2021-06-28 22:39:00.000','A02')
insert into #t_history values('tsmc0001',1,'2021-06-28 23:39:00.000','A01')
--要求:
--1,按部门查询平均借用时长,逾期记录数量,正常归还记录数量
--2,按员工查询平均借用时长,逾期记录数量,正常归还记录数量
--3,按员工查询每次正常借用记录,包括员工号,借用时间,归还时间,使用时长
--4,按员工查询每次逾期借用记录,包括员工号,借用时间,归还时间,使用时长
--问题:
--1,查询的时候得注意,因为要按时间区间去查,第一条记录未必是借用记录,有可能这一天第一条记录是归还记录,所以opt这个值第一条不保证是1
--2,如果第一条记录是2或3,那第记录这一条记录到正常归还数量或逾期数量里面去,并且要获取这条归还记录之前的借用时间记录
--坛子的各位大神有没有方案?之前的贴子提供的方案都推翻了,只要按时间区间查,第一条记录不是借用记录就查询的结果集不对了,所以再次请贴
数据源是有点问题,写了个简单的demo,仅供参考:
declare @TableData table (
h_person varchar(20),
h_opt int,
h_time datetime,
Department_Id varchar(30)
)
insert into @TableData(h_person,h_opt,h_time,Department_Id)
select 'tsmc0001',1,'2021-06-26 09:39:00.000','A01' union all
select 'tsmc0003',1,'2021-06-26 10:39:00.000','A02' union all
select 'tsmc0001',0,'2021-06-26 11:39:00.000','A01' union all
select 'tsmc0003',2,'2021-06-26 12:39:00.000','A02' union all
select 'tsmc0001',2,'2021-06-26 13:39:00.000','A01' union all
select 'tsmc0001',1,'2021-06-26 14:39:00.000','A01' union all
select 'tsmc0004',1,'2021-06-26 15:39:00.000','A03' union all
select 'tsmc0001',0,'2021-06-26 16:39:00.000','A02' union all
select 'tsmc0004',2,'2021-06-26 17:39:00.000','A03' union all
select 'tsmc0001',0,'2021-06-26 18:39:00.000','A02' union all
select 'tsmc0001',2,'2021-06-26 19:39:00.000','A02' union all
select 'tsmc0001',1,'2021-06-26 20:39:00.000','A01' union all
select 'tsmc0001',3,'2021-06-28 20:39:00.000','A01' union all
select 'tsmc0001',0,'2021-06-28 21:39:00.000','A02' union all
select 'tsmc0001',2,'2021-06-28 22:39:00.000','A02' union all
select 'tsmc0001',1,'2021-06-28 23:39:00.000','A01'
-- select * from @TableData where Department_Id ='A01' and h_opt in (1,2)
---------------------------------------------------------------------------------------------------------------------------
--结果表
declare @result table(
Id int identity(1,1) not null,
-- Person varchar(20),
BeginTime datetime,
EndTime datetime,
TimeEqua as datediff(minute,BeginTime,EndTime), --时差
DeptId varchar(30)
)
declare @PendingTab table(
Id int identity(1,1) not null,
Person varchar(20),
Opt int,
[Time] datetime,
DeptId varchar(30)
)
declare @deptId nvarchar(50) ='A01'
--写入待循环表体
insert into @PendingTab(Person,Opt,[Time],DeptId)
select h_person,h_opt,h_time,Department_Id from @TableData where Department_Id =@deptId and h_opt in (1,2)
-- select * from @PendingTab as pt
--求部门平均借用时长
declare @minId int,
@maxId int,
@Opt int
declare @Opt2Time datetime,
@Opt1Time datetime
select @minId =min(Id),@maxId = max(Id) from @PendingTab
-- select @minId,@maxId,@maxTime
while (@minId is not null and @minId <= @maxId)
begin
select @Opt = Opt,@Opt2Time =(case when Opt =2 then [Time] else '' end) from @PendingTab where Id = @minId
-- select @Opt2Time
--写入Opt=2的记录
if @Opt2Time !='1900-01-01 00:00:00'
begin
insert into @result(EndTime) select @Opt2Time
--写入@Opt2Time上一条Opt =1的记录
update a set a.BeginTime =( select x.[Time] from @PendingTab x where x.[Time] < @Opt2Time and x.Id < @minId and x.Opt =1)
,a.DeptId =@deptId
from @result a
end
set @minId = @minId +1
end
select * from @result
输出结果:
我试算了一下数据,数据视乎存在一些问题。
tsmc0001这个人,可以同时存在A01和A02部门?
比方说,问题1。部门平均借用时长,那就要分三步走:
1、先要计算出每个人,每一天的,借出和归还之间的时长。
2、以此数据,再进行按部门分组进行求平均。注意是按部门平均,可以忽略按人头平均。
3、最后count for h_opt =3,以及count for h_opt =2的记录数
而第二个需求,也需要先计算出借用时长,所以这个可以写成一个函数方法,直接调用就可以了。
以此类推……
至于,每个人每天的动作状态是在哪个为准,肯定是先到推最后时间的状态,往前推出上一次的。
昨天后来不就是改成时间区间来查的吗,是1的往后查,2、3的往前查。