27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT
CREATER,
RTRIM(SUM(minutes)/SUM(Con))+N'分钟' as 单张花费,
RTRIM(SUM(minutes)/SUM(DisCount))+N'分钟' AS 单个TYPENO花费,
DENSE_RANK()OVER(ORDER BY SUM(minutes)/SUM(DisCount) asc) AS 排名
FROM
(SELECT
a.CREATER,
DATEDIFF(n,a.CREATTIME,a.UPDATETIME) AS minutes,
COUNT(DISTINCT b.TYPENO) AS DisCount,
COUNT(1) AS Con
FROM matb AS a
LEFT JOIN mxtb AS b ON a.BILLID=b.BILLID
WHERE a.creattime>='2011-09-11'
GROUP BY a.CREATER,a.BILLID,a.CREATTIME,a.UPDATETIME
)t
GROUP BY CREATER
/*
CREATER 单张花费 单个TYPENO花费 排名
AAA 2分钟 3分钟 1
BBB 29分钟 29分钟 2
ccc 61分钟 92分钟 3
*/
declare @主表 table (BILLID int,CREATTIME datetime,UPDATETIME datetime,CREATER varchar(3))
insert into @主表
select 1001,'2011-09-11 09:00','2011-09-11 09:06','AAA' union all
select 1002,'2011-09-11 09:00','2011-09-11 10:06','BBB' union all
select 1003,'2011-09-11 09:01', '2011-09-11 10:05', 'ccc' union all
select 1004,'2011-09-11 09:03', '2011-09-11 11:03', 'ccc'
declare @明细表 table (BILLID int,PID varchar(5),TYPENO varchar(4))
insert into @明细表
select 1001,'P1001','UUIO' union all
select 1001,'P1002','UUIO' union all
select 1001,'P1003','YYXJ' union all
select 1002,'P1001','X90X' union all
select 1002,'P1002','ME90' union all
select 1003, 'P1001', 'xxxx' union all
select 1003, 'P1001', 'xxxx' union all
select 1004, 'P1001', 'xx11'
select a.CREATER,
单张花费=ltrim(a.c/b.c)+'分钟',
单个TYPENO花费=ltrim(a.c/c.c)+'分钟'
from
(select CREATER,sum(datediff(mi,CREATTIME,UPDATETIME)) as c
from @主表 where creattime>='2011-09-11' --条件加在这个位置即可
group by CREATER) a
left join
(select b.CREATER,count(1) as c from @明细表 a left join @主表
b on a.BILLID=b.BILLID
group by b.CREATER
) b on a.CREATER=b.CREATER
left join
(select b.CREATER,count(distinct TYPENO) as c from @明细表 a left join @主表
b on a.BILLID=b.BILLID
group by b.CREATER
) c on a.CREATER=c.CREATER
declare @主表 table (BILLID int,CREATTIME datetime,UPDATETIME datetime,CREATER varchar(3))
insert into @主表
select 1001,'2011-09-11 09:00','2011-09-11 09:06','AAA' union all
select 1002,'2011-09-11 09:00','2011-09-11 10:06','BBB' union all
select 1003,'2011-09-11 09:01', '2011-09-11 10:05', 'ccc' union all
select 1004,'2011-09-11 09:03', '2011-09-11 11:03', 'ccc'
declare @明细表 table (BILLID int,PID varchar(5),TYPENO varchar(4))
insert into @明细表
select 1001,'P1001','UUIO' union all
select 1001,'P1002','UUIO' union all
select 1001,'P1003','YYXJ' union all
select 1002,'P1001','X90X' union all
select 1002,'P1002','ME90' union all
select 1003, 'P1001', 'xxxx' union all
select 1003, 'P1001', 'xxxx' union all
select 1004, 'P1001', 'xx11'
select a.CREATER,
单张花费=ltrim(a.c/b.c)+'分钟',
单个TYPENO花费=ltrim(a.c/c.c)+'分钟'
from
(select CREATER,sum(datediff(mi,CREATTIME,UPDATETIME)) as c
from @主表 group by CREATER) a
left join
(select b.CREATER,count(1) as c from @明细表 a left join @主表
b on a.BILLID=b.BILLID
group by b.CREATER
) b on a.CREATER=b.CREATER
left join
(select b.CREATER,count(distinct TYPENO) as c from @明细表 a left join @主表
b on a.BILLID=b.BILLID
group by b.CREATER
) c on a.CREATER=c.CREATER
/*
CREATER 单张花费 单个TYPENO花费
------- ---------------- ----------------
AAA 2分钟 3分钟
BBB 33分钟 33分钟
ccc 61分钟 92分钟
*/
--主表
--BILLID CREATTIME UPDATETIME CREATER
--1001 2011-09-11 09:00 2011-09-11 09:06 AAA
--1002 2011-09-11 09:00 2011-09-11 10:06 BBB
--明细表
--BILLID PID TYPENO
--1001 P1001 UUIO
--1001 P1002 UUIO
--1001 P1003 YYXJ
--1002 P1001 X90X
--1002 P1002 ME90
create table matb(BILLID INT, CREATTIME VARCHAR(50), UPDATETIME VARCHAR(50),CREATER VARCHAR(50) )
INSERT INTO matb select 1001 ,'2011-09-11 09:00 ','2011-09-11 09:06',' AAA' union all
select 1002,'2011-09-11 09:00', '2011-09-11 10:06', 'BBB'
create TABLE mxtb ( BILLID int ,PID varchar(50),TYPENO VARCHAR(50) )
INSERT INTO mxtb
select 1001, 'P1001', 'UUIO' union all
select 1001, 'P1002', 'UUIO' union all
select 1001, 'P1003', 'YYXJ' union all
select 1002, 'P1001', 'X90X' union all
select 1002, 'P1002', 'ME90'
--要求计算每个人平均处理子单据的时间:
--CREATER 单张花费 单个TYPENO花费 排名
-- AAA 2分钟 3分钟 1
-- BBB 33分钟 33分钟 2
select a.creater,
ltrim(datediff(mi,a.CREATTIME,a.UPDATETIME)/nullif(count(distinct b.pid),0))+'分钟' as 单张花费,
ltrim(datediff(mi,a.CREATTIME,a.UPDATETIME)/nullif(count(distinct b.typeno),0))+'分钟' as 单个TYPENO花费,
row_number() over (order by datediff(mi,a.CREATTIME,a.UPDATETIME)/nullif(count(distinct b.pid),0)) AS 排名
from matb a join mxtb b on a.BILLID = b.BILLID
group by a.creater,a.CREATTIME,a.UPDATETIME
creater 单张花费 单个TYPENO花费 排名
-------------------------------------------------- ---------------- ---------------- --------------------
AAA 2分钟 3分钟 1
BBB 33分钟 33分钟 2
(2 行受影响)
declare @主表 table (BILLID int,CREATTIME datetime,UPDATETIME datetime,CREATER varchar(3))
insert into @主表
select 1001,'2011-09-11 09:00','2011-09-11 09:06','AAA' union all
select 1002,'2011-09-11 09:00','2011-09-11 10:06','BBB'
declare @明细表 table (BILLID int,PID varchar(5),TYPENO varchar(4))
insert into @明细表
select 1001,'P1001','UUIO' union all
select 1001,'P1002','UUIO' union all
select 1001,'P1003','YYXJ' union all
select 1002,'P1001','X90X' union all
select 1002,'P1002','ME90'
select CREATER,
单张花费=
ltrim(datediff(mi,a.CREATTIME,a.UPDATETIME)/b.c)+'分钟',
单个TYPENO花费=
ltrim(datediff(mi,a.CREATTIME,a.UPDATETIME)/c.c)+'分钟'
from @主表 a
left join
(select BILLID,count(1) as c from @明细表 group by BILLID
) b on a.BILLID=b.BILLID
left join
(select BILLID,count(distinct TYPENO) as c from @明细表 group by BILLID
) c on a.BILLID=c.BILLID
/*
CREATER 单张花费 单个TYPENO花费
------- ---------------- ----------------
AAA 2分钟 3分钟
BBB 33分钟 33分钟
*/
select a.creater,
ltrim(datediff(mi,a.createtime,a.updatetime)/nullif(count(distinct b.pid),0))+'分钟' as [everyAct],
ltrim(datediff(mi,a.createtime,a.updatetime)/nullif(count(distinct b.typeno),0))+'分钟' as [everyTct],
px=row_number() over (order by datediff(mi,a.createtime,a.updatetime)/nullif(count(distinct b.pid),0))
from 主表 a join 明细表 b on a.id = b.billid
group by a.creater,a.createtime,a.updatetime
create table t1(BILLID int,CREATETIME datetime,UPDATETIME datetime,CREATER varchar(10))
insert t1
select 1001, '2011-09-11 09:00', '2011-09-11 09:06', 'AAA' union all
select 1002, '2011-09-11 09:00', '2011-09-11 10:06', 'BBB'
create table t2(BILLID int, PID varchar(10),TYPENO varchar(10))
insert t2
select 1001, 'P1001', 'UUIO' union all
select 1001, 'P1002', 'UUIO' union all
select 1001, 'P1003', 'YYXJ' union all
select 1002, 'P1001', 'X90X' union all
select 1002, 'P1002', 'ME90'
go
select CREATER,
单张花费=datediff(mi,createtime,updatetime)/count(PID ) ,
单个TYPENO花费=datediff(mi,createtime,updatetime)/count(TYPENO ) ,
排名=row_number()over(order by datediff(mi,createtime,updatetime)/count(PID ),datediff(mi,createtime,updatetime)/count(TYPENO ))
from (
select b.CREATER,a.PID,a.TYPENO ,b.CREATETIME,b.UPDATETIME
from t2 a
join t1 b on a.BILLID=b.BILLID
) as c
group by CREATER,CREATETIME,UPDATETIME
/*
(2 行受影响)
creater 单张花费 单个TYPENO花费 排名
----- ------ ---------- ---
AAA 2 2 1
BBB 33 33 2
*/
go
drop table t1,t2
select a.CREATER,
单张花费=ltrim(a.c/b.c)+'分钟',
单个TYPENO花费=ltrim(a.c/c.c)+'分钟'
from
(select CREATER,sum(datediff(mi,CREATTIME,UPDATETIME)) as c
from matb where creattime>='2011-09-11' group by CREATER) a
left join
(select b.CREATER,count(1) as c from mxtb a left join matb
b on a.BILLID=b.BILLID
group by b.CREATER
) b on a.CREATER=b.CREATER
left join
(select b.CREATER,count(distinct TYPENO) as c from mxtb a left join matb
b on a.BILLID=b.BILLID
group by b.CREATER
) c on a.CREATER=c.CREATER