单张销售排名解决方法

单张销售排名
主表
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


要求计算每个人平均处理子单据的时间:
CREATER 单张花费 单个TYPENO花费 排名
 AAA 2分钟 3分钟 1
 BBB 33分钟 33分钟 2

备注:3分钟=6/2(UUIO,YYXJ共两个)

[解决办法]

SQL code

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.billidgroup by a.creater,a.createtime,a.updatetime
[解决办法]
SQL code
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 allselect 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 allselect 1001,'P1002','UUIO' union allselect 1001,'P1003','YYXJ' union allselect 1002,'P1001','X90X' union allselect 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.BILLIDleft 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分钟*/
[解决办法]
SQL code
--主表--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 ME90create 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 allselect 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 allselect 1001, 'P1002', 'UUIO' union allselect 1001, 'P1003', 'YYXJ' union allselect 1002, 'P1001', 'X90X' union allselect 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.BILLIDgroup by a.creater,a.CREATTIME,a.UPDATETIMEcreater                                            单张花费             单个TYPENO花费       排名-------------------------------------------------- ---------------- ---------------- -------------------- AAA                                               2分钟              3分钟              1BBB                                                33分钟             33分钟             2(2 行受影响)
------解决方案-------------------- 
 
SQL code
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 allselect 1002,'2011-09-11 09:00','2011-09-11 10:06','BBB' union allselect 1003,'2011-09-11 09:01', '2011-09-11 10:05', 'ccc' union allselect 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 allselect 1001,'P1002','UUIO' union allselect 1001,'P1003','YYXJ' union allselect 1002,'P1001','X90X' union allselect 1002,'P1002','ME90' union allselect 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 cfrom @主表 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.CREATERleft 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分钟*/
[解决办法]
探讨

加上时间范围:@主表.creattime>='2011-09-11'
好象又不行了.