以下存储过程,希望能够改善性能,奇怪的是,算一天的时候是正确的,但是多天的时候数据就不正确了,请高手给改善下,谢谢。
USE [scgl]
GO
/****** Object: StoredProcedure [dbo].[proc_sc_线别生产量统计表1] Script Date: 12/05/2011 18:56:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO-- =============================================
--
-- =============================================
/*
线别生产量统计周报
参数说明:
@SRQ 开始日期
@ERQ 结束日期
@DPTID 事业单位代号
@ZCID 制程代号
*/ALTER PROCEDURE [dbo].[proc_sc_线别生产量统计表1]
(
@SRQ DATETIME ,
@ERQ DATETIME ,
@DPTID VARCHAR(5)
--@ZCID VARCHAR(4)
)
AS
--DECLARE @iYear SMALLINT,@iMonth SMALLINT
DECLARE @SY SMALLINT,@SM SMALLINT,@SD SMALLINT,@EY SMALLINT,@EM SMALLINT ,@ED SMALLINT,@OKDAY SMALLINT
SET @SY = YEAR(@SRQ)
SET @SM = MONTH(@SRQ)
SET @SD = DAY(@SRQ) SET @EY = YEAR(@ERQ)
SET @EM = MONTH(@ERQ)
SET @ED = DAY(@ERQ)
SET @OKDAY = @ED
if @SM!=@EM
begin
-- SET @OKDAY=(case @ED when 30 then 31 else @ED END)
SET @OKDAY=31
endSET NOCOUNT ONDECLARE @TB TABLE(ZC_ID VARCHAR(3),GROUP_ID VARCHAR(5),组别 VARCHAR(20),目标量 INT,实际量 INT,总工时 DECIMAL(9,2),应到平均人数 INT,实到平均人数 INT,应计人均效率 DECIMAL(9,2),应计生产量 DECIMAL(9))
INSERT INTO @TB(GROUP_ID ,组别 ,目标量,实际量,总工时,应到平均人数,实到平均人数,应计人均效率,应计生产量)select b.GROUP_ID,
b.GROUP_NAME as 组别,
sum(排单量1+排单量2) as 目标量,
sum(数量1+数量2+数量3+数量4+数量5+数量6+数量7+数量8+数量9+数量10+数量11+数量12+数量13+数量14+数量15) as 实际量,
sum(应到人数*(isnull(人数1,0)*isnull(工时1,0)+isnull(人数2,0)*isnull(工时2,0)+isnull(人数3,0)*isnull(工时3,0))/nullif (实到人数,0)) as 总工时,
sum(应到人数)/nullif(count(*),0) as 应到平均人数,
sum(实到人数)/nullif(count(*),0) as 实到平均人数,
sum(((case 数量1 when 0 then 0.001 else 数量1 end)+数量2+数量3+数量4+数量5+数量6+数量7+数量8+数量9+数量10+数量11+数量12+数量13+数量14+数量15)/(nullif((case 数量1 when 0 then 0.001 else 数量1 end)/nullif(时效1,0),0)+isnull(数量2/nullif(时效2,0),0)+isnull(数量3/nullif(时效3,0),0)+isnull(数量4/nullif(时效4,0),0)+isnull(数量5/nullif(时效5,0),0)+isnull(数量6/nullif(时效6,0),0)+isnull(数量7/nullif(时效7,0),0)+isnull(数量8/nullif(时效8,0),0)+isnull(数量9/nullif(时效9,0),0)+isnull(数量10/nullif(时效10,0),0)+isnull(数量11/nullif(时效11,0),0)+isnull(数量12/nullif(时效12,0),0)+isnull(数量13/nullif(时效13,0),0)+isnull(数量14/nullif(时效14,0),0)+isnull(数量15/nullif(时效15,0),0)))/nullif(count(*),0) as 应计人均效率,
sum(((case 数量1 when 0 then 0.001 else 数量1 end)+数量2+数量3+数量4+数量5+数量6+数量7+数量8+数量9+数量10+数量11+数量12+数量13+数量14+数量15)/(nullif((case 数量1 when 0 then 0.001 else 数量1 end)/nullif(时效1,0),0)+isnull(数量2/nullif(时效2,0),0)+isnull(数量3/nullif(时效3,0),0)+isnull(数量4/nullif(时效4,0),0)+isnull(数量5/nullif(时效5,0),0)+isnull(数量6/nullif(时效6,0),0)+isnull(数量7/nullif(时效7,0),0)+isnull(数量8/nullif(时效8,0),0)+isnull(数量9/nullif(时效9,0),0)+isnull(数量10/nullif(时效10,0),0)+isnull(数量11/nullif(时效11,0),0)+isnull(数量12/nullif(时效12,0),0)+isnull(数量13/nullif(时效13,0),0)+isnull(数量14/nullif(时效14,0),0)+isnull(数量15/nullif(时效15,0),0)) * (应到人数*(isnull(人数1,0)*isnull(工时1,0)+isnull(人数2,0)*isnull(工时2,0)+isnull(人数3,0)*isnull(工时3,0))/nullif (实到人数,0))) as 应计生产量from(SELECT * FROM sc_班组生产日流量 where (YE >= @SY and MO>=@SM AND DA>=@SD ) and (YE <= @EY and MO<=@EM AND DA<=@OKDAY) and 时效1>0 ) a
--注意:此条件处理工时>0的情况
RIGHT OUTER JOIN
(SELECT GROUP_ID,GROUP_NAME FROM View_组别全称 WHERE DPT_ID = @DPTID and BAOBIAO='True' ) b
ON a.GROUP_ID = b.GROUP_IDGROUP BY b.GROUP_ID,b.GROUP_NAME
ORDER BY b.GROUP_ID,b.GROUP_NAMESELECT a.GROUP_ID ,a.组别 ,a.目标量, a.实际量 ,a.总工时 ,a.应到平均人数,a.实到平均人数,a.应计人均效率, a.应计生产量 , b.ZC_ID,b.ZC_NAME INTO #TEMP1
FROM @TB a,View_组别全称 b WHERE a.GROUP_ID = b.GROUP_ID
ORDER BY b.ZC_ID,a.组别SELECT GROUP_ID, sum(排单量1+排单量2) as 目标量 INTO #TEMP2
FROM sc_班组生产日流量 where (YE >= @SY and MO>=@SM AND DA>=@SD ) and (YE <= @EY and MO<=@EM AND DA<=@OKDAY) and ( 时效1>0 OR (排单量1+排单量2)>0 )
--注意:还应处理当目标量>0,工时=0,即有排单,但实际休假的情况,否则时间段内目标量或应计人均效率统计不准
GROUP BY GROUP_IDSELECT c.GROUP_ID ,c.组别 ,d.目标量, c.实际量 ,c.总工时 ,c.应到平均人数,c.实到平均人数,c.应计人均效率, c.应计生产量 , c.ZC_ID,c.ZC_NAME
FROM #TEMP1 c,#TEMP2 d
--将以上两种情况合并
WHERE c.GROUP_ID = d.GROUP_ID
ORDER BY c.ZC_ID,c.组别
--ORDER BY c.ZC_NAMESET NOCOUNT OFFDROP TABLE #TEMP1
DROP TABLE #TEMP2
USE [scgl]
GO
/****** Object: StoredProcedure [dbo].[proc_sc_线别生产量统计表1] Script Date: 12/05/2011 18:56:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO-- =============================================
--
-- =============================================
/*
线别生产量统计周报
参数说明:
@SRQ 开始日期
@ERQ 结束日期
@DPTID 事业单位代号
@ZCID 制程代号
*/ALTER PROCEDURE [dbo].[proc_sc_线别生产量统计表1]
(
@SRQ DATETIME ,
@ERQ DATETIME ,
@DPTID VARCHAR(5)
--@ZCID VARCHAR(4)
)
AS
--DECLARE @iYear SMALLINT,@iMonth SMALLINT
DECLARE @SY SMALLINT,@SM SMALLINT,@SD SMALLINT,@EY SMALLINT,@EM SMALLINT ,@ED SMALLINT,@OKDAY SMALLINT
SET @SY = YEAR(@SRQ)
SET @SM = MONTH(@SRQ)
SET @SD = DAY(@SRQ) SET @EY = YEAR(@ERQ)
SET @EM = MONTH(@ERQ)
SET @ED = DAY(@ERQ)
SET @OKDAY = @ED
if @SM!=@EM
begin
-- SET @OKDAY=(case @ED when 30 then 31 else @ED END)
SET @OKDAY=31
endSET NOCOUNT ONDECLARE @TB TABLE(ZC_ID VARCHAR(3),GROUP_ID VARCHAR(5),组别 VARCHAR(20),目标量 INT,实际量 INT,总工时 DECIMAL(9,2),应到平均人数 INT,实到平均人数 INT,应计人均效率 DECIMAL(9,2),应计生产量 DECIMAL(9))
INSERT INTO @TB(GROUP_ID ,组别 ,目标量,实际量,总工时,应到平均人数,实到平均人数,应计人均效率,应计生产量)select b.GROUP_ID,
b.GROUP_NAME as 组别,
sum(排单量1+排单量2) as 目标量,
sum(数量1+数量2+数量3+数量4+数量5+数量6+数量7+数量8+数量9+数量10+数量11+数量12+数量13+数量14+数量15) as 实际量,
sum(应到人数*(isnull(人数1,0)*isnull(工时1,0)+isnull(人数2,0)*isnull(工时2,0)+isnull(人数3,0)*isnull(工时3,0))/nullif (实到人数,0)) as 总工时,
sum(应到人数)/nullif(count(*),0) as 应到平均人数,
sum(实到人数)/nullif(count(*),0) as 实到平均人数,
sum(((case 数量1 when 0 then 0.001 else 数量1 end)+数量2+数量3+数量4+数量5+数量6+数量7+数量8+数量9+数量10+数量11+数量12+数量13+数量14+数量15)/(nullif((case 数量1 when 0 then 0.001 else 数量1 end)/nullif(时效1,0),0)+isnull(数量2/nullif(时效2,0),0)+isnull(数量3/nullif(时效3,0),0)+isnull(数量4/nullif(时效4,0),0)+isnull(数量5/nullif(时效5,0),0)+isnull(数量6/nullif(时效6,0),0)+isnull(数量7/nullif(时效7,0),0)+isnull(数量8/nullif(时效8,0),0)+isnull(数量9/nullif(时效9,0),0)+isnull(数量10/nullif(时效10,0),0)+isnull(数量11/nullif(时效11,0),0)+isnull(数量12/nullif(时效12,0),0)+isnull(数量13/nullif(时效13,0),0)+isnull(数量14/nullif(时效14,0),0)+isnull(数量15/nullif(时效15,0),0)))/nullif(count(*),0) as 应计人均效率,
sum(((case 数量1 when 0 then 0.001 else 数量1 end)+数量2+数量3+数量4+数量5+数量6+数量7+数量8+数量9+数量10+数量11+数量12+数量13+数量14+数量15)/(nullif((case 数量1 when 0 then 0.001 else 数量1 end)/nullif(时效1,0),0)+isnull(数量2/nullif(时效2,0),0)+isnull(数量3/nullif(时效3,0),0)+isnull(数量4/nullif(时效4,0),0)+isnull(数量5/nullif(时效5,0),0)+isnull(数量6/nullif(时效6,0),0)+isnull(数量7/nullif(时效7,0),0)+isnull(数量8/nullif(时效8,0),0)+isnull(数量9/nullif(时效9,0),0)+isnull(数量10/nullif(时效10,0),0)+isnull(数量11/nullif(时效11,0),0)+isnull(数量12/nullif(时效12,0),0)+isnull(数量13/nullif(时效13,0),0)+isnull(数量14/nullif(时效14,0),0)+isnull(数量15/nullif(时效15,0),0)) * (应到人数*(isnull(人数1,0)*isnull(工时1,0)+isnull(人数2,0)*isnull(工时2,0)+isnull(人数3,0)*isnull(工时3,0))/nullif (实到人数,0))) as 应计生产量from(SELECT * FROM sc_班组生产日流量 where (YE >= @SY and MO>=@SM AND DA>=@SD ) and (YE <= @EY and MO<=@EM AND DA<=@OKDAY) and 时效1>0 ) a
--注意:此条件处理工时>0的情况
RIGHT OUTER JOIN
(SELECT GROUP_ID,GROUP_NAME FROM View_组别全称 WHERE DPT_ID = @DPTID and BAOBIAO='True' ) b
ON a.GROUP_ID = b.GROUP_IDGROUP BY b.GROUP_ID,b.GROUP_NAME
ORDER BY b.GROUP_ID,b.GROUP_NAMESELECT a.GROUP_ID ,a.组别 ,a.目标量, a.实际量 ,a.总工时 ,a.应到平均人数,a.实到平均人数,a.应计人均效率, a.应计生产量 , b.ZC_ID,b.ZC_NAME INTO #TEMP1
FROM @TB a,View_组别全称 b WHERE a.GROUP_ID = b.GROUP_ID
ORDER BY b.ZC_ID,a.组别SELECT GROUP_ID, sum(排单量1+排单量2) as 目标量 INTO #TEMP2
FROM sc_班组生产日流量 where (YE >= @SY and MO>=@SM AND DA>=@SD ) and (YE <= @EY and MO<=@EM AND DA<=@OKDAY) and ( 时效1>0 OR (排单量1+排单量2)>0 )
--注意:还应处理当目标量>0,工时=0,即有排单,但实际休假的情况,否则时间段内目标量或应计人均效率统计不准
GROUP BY GROUP_IDSELECT c.GROUP_ID ,c.组别 ,d.目标量, c.实际量 ,c.总工时 ,c.应到平均人数,c.实到平均人数,c.应计人均效率, c.应计生产量 , c.ZC_ID,c.ZC_NAME
FROM #TEMP1 c,#TEMP2 d
--将以上两种情况合并
WHERE c.GROUP_ID = d.GROUP_ID
ORDER BY c.ZC_ID,c.组别
--ORDER BY c.ZC_NAMESET NOCOUNT OFFDROP TABLE #TEMP1
DROP TABLE #TEMP2
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货