01拣货数据处理
declare @姓名 nvarchar(255)
declare @当前日期 date
set @姓名='我叫什么'
set @当前日期=convert(varchar(100),getdate(),23)
--分拣记录数据初始化,补充开始时间和计算真实用时
update A set A.开始时间 = b.操作时间,完整时长 = DATEDIFF (S,A.操作时间,b.操作时间),a.配送日期 =@当前日期,a.作业环节 = '拣货', a.站点 = (case
when a.容器编码 = b.容器编码 then '小件'
when a.容器编码 <> b.容器编码 and a.变更数量 > 300 then '大件'
else '小件'
end),时长 =
(case
when a.容器编码 <> b.容器编码 and a.变更数量 > 300 and DATEDIFF (S,A.操作时间,b.操作时间) >600 then 600
when a.容器编码 <> b.容器编码 and a.变更数量 > 300 and DATEDIFF (S,A.操作时间,b.操作时间) <=600 then DATEDIFF (S,A.操作时间,b.操作时间)
when a.容器编码 = b.容器编码 and DATEDIFF (S,A.操作时间,b.操作时间) >300 then 300
when a.容器编码 = b.容器编码 and DATEDIFF (S,A.操作时间,b.操作时间) <300 then DATEDIFF (S,A.操作时间,b.操作时间)
when a.容器编码 <> b.容器编码 and a.变更数量 < 300 and DATEDIFF (S,A.操作时间,b.操作时间) <=300 then DATEDIFF (S,A.操作时间,b.操作时间)
when a.容器编码 <> b.容器编码 and a.变更数量 < 300 and DATEDIFF (S,A.操作时间,b.操作时间) >300 then 300
end)
from 分拣明细表 A,分拣明细表 B
where A.操作人 = b.操作人 and A.操作时间 < b.操作时间 and A.配送日期 is null and a.作业环节 is null and b.配送日期 is null and b.作业环节 is null
----收集作业时长数据,但不包括异常数据
insert into SKU效率表(SKU,时长,中心网格,变化量,配送日期,完整时长) select SKu,时长,站点,sum(变更数量) as 变更数量,配送日期,完整时长 from 分拣明细表 where 时长 >0 and 时长 <> 600 and 时长 <> 300 and 配送日期 =@当前日期 and 作业环节 = '拣货'
GROUP BY 操作时间,操作人,SKu,商品名称,时长,站点,配送日期,完整时长
--删除无时长的数据
DELETE FROM 分拣明细表 where 完整时长<=0 and 配送日期 =@当前日期 and 作业环节 = '拣货' or 配送日期 is null
02分拣数据处理
declare @姓名 nvarchar(255)
declare @当前日期 date
set @姓名='我叫什么'
set @当前日期=convert(varchar(100),getdate(),23)
--分拣记录数据初始化,补充开始时间和计算真实用时
update A set A.开始时间 = b.操作时间,完整时长 = DATEDIFF (S,A.操作时间,b.操作时间),时长 =
(case
when A.生产波次 = '福州中心仓直配' and DATEDIFF (S,A.操作时间,b.操作时间) >120 then 30
when A.生产波次 = '福州中心仓直配' and DATEDIFF (S,A.操作时间,b.操作时间) <=120 then DATEDIFF (S,A.操作时间,b.操作时间)
when A.生产波次 = '福州网格站' and DATEDIFF (S,A.操作时间,b.操作时间) >600 then 300
else DATEDIFF (S,A.操作时间,b.操作时间)
end),A.站点=
(case
when A.站点 is null then '中心'
else '网格'
end)
from 分拣明细表 A,分拣明细表 B
where A.操作人 = b.操作人 and A.操作时间 < b.操作时间 and A.配送日期 =@当前日期 and a.作业环节 ='分拣' and b.配送日期 =@当前日期 and b.作业环节 ='分拣'
--收集作业时长数据,但不包括异常数据
insert into SKU效率表(SKU,商品名称,品类,时长,中心网格,变化量,配送日期,完整时长) select SKu,商品名称,分拣区,时长,站点,sum(变化量) as 变化量,配送日期,完整时长 from 分拣明细表 where 时长 >0 and 时长 <> 30 and 时长 <> 300 and 配送日期 =@当前日期 and 作业环节 ='分拣'GROUP BY 操作时间,操作人,SKu,商品名称,分拣区,时长,站点,配送日期,完整时长
--删除无时长的数据
DELETE FROM 分拣明细表 where 完整时长<=0 and 配送日期 =@当前日期 and 作业环节 <>'拣货' and 作业环节<>'分拣' or 完整时长<=0 and 作业环节<>'分拣' and 配送日期 =@当前日期 and 作业环节 <>'拣货'
--收集产出数据存放历史表
insert into 分拣明细表_历史(操作时间,配送批次,配送日期,生产波次,站点,分拣区,集货位,SKU,商品名称,时长,变化量,操作人,容器编号,作业环节,开始时间,完整时长,容器编码,变更数量,得分) select top 100000 操作时间,配送批次,配送日期,生产波次,站点,分拣区,集货位,SKU,商品名称,时长,变化量,操作人,容器编号,作业环节,开始时间,完整时长,容器编码,变更数量,得分 from (
SELECT top 100000 操作时间,配送批次,配送日期,生产波次,站点,分拣区,集货位,SKU,商品名称,时长,sum(变化量) as 变化量,操作人,容器编号,作业环节,开始时间,完整时长,容器编码,变更数量,得分 FROM(
SELECT top 100000 操作时间,配送批次,配送日期,生产波次,站点,分拣区,集货位,分拣明细表.SKU,商品名称,时长,操作人,容器编号,作业环节,开始时间,完整时长,容器编码,变更数量,
(CASE WHEN dbo.分拣明细表.作业环节 = '拣货' THEN dbo.平均时效.时间 * 0.00555555555555555 WHEN dbo.分拣明细表.作业环节 = '分拣' THEN (CASE WHEN 分拣区 = 'DP' AND SUM(变化量)
> 10 AND SUM(变化量) / 10 < 10 THEN SUM(变化量) / 10 WHEN 分拣区 = 'DP' AND SUM(变化量) > 10 AND SUM(变化量) / 10 > 10 THEN 10 WHEN 分拣区 <> 'DP' AND SUM(变化量) > 20 AND
SUM(变化量) / 20 < 10 THEN SUM(变化量) / 20 WHEN 分拣区 <> 'DP' AND SUM(变化量) > 20 AND SUM(变化量) / 20 > 10 THEN 10 ELSE 1 END) * 平均时效.时间 * 0.00555555555555555 END) AS 得分, SUM(dbo.分拣明细表.变化量) AS 变化量
FROM dbo.分拣明细表 INNER JOIN
dbo.平均时效 ON dbo.分拣明细表.站点 = dbo.平均时效.中心网格 AND dbo.分拣明细表.SKU = dbo.平均时效.SKU
WHERE (dbo.分拣明细表.配送日期 =@当前日期) AND (dbo.分拣明细表.作业环节 = '拣货') OR
(dbo.分拣明细表.配送日期 =@当前日期) AND (dbo.分拣明细表.作业环节 = '分拣')
GROUP BY 操作时间,配送批次,配送日期,生产波次,站点,分拣区,集货位,分拣明细表.SKU,商品名称,时长,操作人,容器编号,作业环节,开始时间,完整时长,容器编码,变更数量,时间) aa
GROUP BY 操作时间,配送批次,配送日期,生产波次,站点,分拣区,集货位,SKU,商品名称,时长,操作人,容器编号,作业环节,开始时间,完整时长,容器编码,变更数量,得分 ORDER BY 作业环节,操作时间 DESC) bb
--查询当日总产出得分
SELECT 配送日期,操作人,sum(总得分) as 分数 FROM(
SELECT dbo.分拣明细表.配送日期, dbo.分拣明细表.操作时间, dbo.分拣明细表.时长, dbo.分拣明细表.操作人, dbo.平均时效.时间,
(CASE WHEN dbo.分拣明细表.作业环节 = '拣货' THEN dbo.平均时效.时间 * 0.00555555555555555 WHEN dbo.分拣明细表.作业环节 = '分拣' THEN (CASE WHEN 分拣区 = 'DP' AND SUM(变化量)
> 10 AND SUM(变化量) / 10 < 10 THEN SUM(变化量) / 10 WHEN 分拣区 = 'DP' AND SUM(变化量) > 10 AND SUM(变化量) / 10 > 10 THEN 10 WHEN 分拣区 <> 'DP' AND SUM(变化量) > 20 AND
SUM(变化量) / 20 < 10 THEN SUM(变化量) / 20 WHEN 分拣区 <> 'DP' AND SUM(变化量) > 20 AND SUM(变化量) / 20 > 10 THEN 10 ELSE 1 END) * 平均时效.时间 * 0.00555555555555555 END)
AS 总得分, SUM(dbo.分拣明细表.变化量) AS 数量
FROM dbo.分拣明细表 INNER JOIN
dbo.平均时效 ON dbo.分拣明细表.站点 = dbo.平均时效.中心网格 AND dbo.分拣明细表.SKU = dbo.平均时效.SKU
WHERE (dbo.分拣明细表.配送日期 =@当前日期) AND (dbo.分拣明细表.作业环节 = '拣货') OR
(dbo.分拣明细表.配送日期 =@当前日期) AND (dbo.分拣明细表.作业环节 = '分拣')
GROUP BY dbo.分拣明细表.配送日期, dbo.分拣明细表.作业环节, dbo.分拣明细表.操作时间, dbo.分拣明细表.分拣区, dbo.分拣明细表.完整时长, dbo.分拣明细表.时长, dbo.分拣明细表.操作人, dbo.分拣明细表.SKU, dbo.平均时效.时间) aa
GROUP BY 配送日期,操作人 ORDER BY 分数 DESC
03分时段汇总
declare @姓名 nvarchar(255)
declare @当前日期 date
set @姓名='我叫什么'
set @当前日期=convert(varchar(100),getdate(),23)
--查询分时总产出
SELECT (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) as '时间段', sum(得分) as '得分'
FROm 分拣明细表_历史 WHERE 配送日期 =@当前日期 GROUP BY (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) order by 时间段
--查询所有人分时产出
SELECT (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) as '时间段',操作人, sum(得分) as '得分'
FROm 分拣明细表_历史 WHERE 配送日期 =@当前日期 GROUP BY 操作人,(case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) order by 操作人,时间段
--查询整体分时产出
SELECT 时间段,sum(得分) as '得分',sum(数量) as '件数', COUNT(操作人) as '人数',sum(得分)/COUNT(操作人) as '平均得分' FROm (
SELECT (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) as '时间段', sum(得分) as '得分', 操作人,sum(变化量) as '数量'
FROm 分拣明细表_历史 WHERE 配送日期 =@当前日期 GROUP BY (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) , 操作人) a
GROUP BY 时间段 order by 时间段
--查询个人分时产出
SELECT 时间段,sum(得分) as '得分',sum(数量) as '件数', COUNT(操作人) as '人数',sum(得分)/COUNT(操作人) as '平均得分' FROm (
SELECT (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) as '时间段', sum(得分) as '得分', 操作人,sum(变化量) as '数量'
FROm 分拣明细表_历史 WHERE 配送日期 =@当前日期 and 操作人=@姓名 GROUP BY (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) , 操作人) a
GROUP BY 时间段 order by 时间段
--按部门分时统计
SELECT dbo.部门信息表.部门, dbo.分时统计得分.时间段, SUM(dbo.分时统计得分.得分) / SUM(dbo.分时统计得分.人数) AS 平均分
FROM dbo.分时统计得分 INNER JOIN
dbo.部门信息表 ON dbo.分时统计得分.操作人 = dbo.部门信息表.姓名
GROUP BY dbo.部门信息表.部门, dbo.分时统计得分.时间段
--按部门+人分时统计
SELECT dbo.部门信息表.部门, 操作人, dbo.分时统计得分.时间段, sum(dbo.分时统计得分.得分)as 得分 ,SUM(dbo.分时统计得分.件数) AS 件数
FROM dbo.分时统计得分 INNER JOIN
dbo.部门信息表 ON dbo.分时统计得分.操作人 = dbo.部门信息表.姓名
GROUP BY dbo.部门信息表.部门, 操作人, dbo.分时统计得分.时间段 order by 部门,时间段
部门表
分拣明细表
历史数据表
SKU效率表
视图表
04按部门分时统计
SELECT dbo.部门信息表.部门, dbo.分时统计得分.时间段, SUM(dbo.分时统计得分.得分) / SUM(dbo.分时统计得分.人数) AS 平均分
FROM dbo.分时统计得分 INNER JOIN
dbo.部门信息表 ON dbo.分时统计得分.操作人 = dbo.部门信息表.姓名
GROUP BY dbo.部门信息表.部门, dbo.分时统计得分.时间段
分拣待更新记录表
SELECT TOP (100000) 系数, 操作时间, 配送批次, 配送日期, 生产波次, 站点, 分拣区, 集货位, SKU, 商品名称, 时长, 变化量, 操作人, 容器编号, 作业环节, 开始时间, 完整时长, 容器编码, 变更数量
FROM dbo.分拣明细表
WHERE (配送日期 IS NULL) AND (作业环节 IS NULL)
ORDER BY 操作人, 操作时间 DESC
分时统计得分
SELECT top 100000 时间段,sum(得分) as '得分',sum(数量) as '件数', COUNT(操作人) as '人数',操作人 FROm (
SELECT (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) as '时间段', sum(得分) as '得分', 操作人,sum(变化量) as '数量'
FROm 分拣明细表_历史 WHERE 配送日期 =convert(varchar(100),getdate(),23) GROUP BY (case when datepart(mi,操作时间)<30 then datename(hh,操作时间)+':00' else datename(hh,操作时间)+':30' end) , 操作人) a
GROUP BY 时间段, 操作人 order by 时间段
平均时效
SELECT 中心网格, SKU, AVG(时长) AS 时间
FROM dbo.SKU效率表
GROUP BY 中心网格, SKU