CREATE PROCEDURE p_fr_subcompany_proamount_toplan(@date1 DATETIME, @date2 DATETIME , @gettype varchar(20)='', @salesdept VARCHAR(30) ='', @subcompanyname VARCHAR(30)='') AS BEGIN-- 事业处预测接单与实际达成报表-- it_zzy 2023-03-13SET NOCOUNT ON IF ISNULL(@gettype,'') IN ('','%') SET @gettype =''-- 1. 切掉时间SELECT @date1= CONVERT(DATETIME,CONVERT(DATE, @date1)),@date2= CONVERT(DATETIME,CONVERT(VARCHAR(10), @date2)+' 23:59:59') -- 2. 取得工作日,和节假日,以及每月最后一天DECLARE @maxdate DATETIME , @workdays INT , @monthworkdays INT , @firstdate DATETIME ,@lastdate DATETIMESELECT @firstdate=CONVERT(VARCHAR(7),@date1,23) +'-01' ,@lastdate=DATEADD(DAY,-1, DATEADD(MONTH,1, CONVERT(VARCHAR(7),@date1,23) +'-01')) SELECT @maxdate = MAX(a.inputdate) FROM goods_order_receiving aWHERE a.inputdate BETWEEN @date1 AND @date2 SELECT @workdays = DATEDIFF(DAY, @date1, @maxdate )- dbo.f_get_holidays(@date1, @maxdate, 'N','%','WYSY') +1 , @monthworkdays = DATEDIFF(DAY,@firstdate, @lastdate )- dbo.f_get_holidays(@firstdate, @lastdate,'N','%','WYSY') +1-- SELECT @workdays, @date1, @maxdate,@monthworkdays , @date2, @maxdateIF OBJECT_ID('tempdb..#t_1') IS NOT NULL DROP TABLE #t_1 -- 按事业处查询IF @gettype =''BEGIN SELECT CONVERT(VARCHAR(7),a.inputdate,23) month_id ,a.subcompanyname ,CONVERT(NUMERIC(10,2),SUM(a.notax_unitpce_amount /10000)) AS notax_unitpce_amount ,t.target ,parktype ,@workdays AS workdays ,t.target_month ,@salesdept AS 营业部 ,@monthworkdays AS monthworkdays FROM goods_order_receiving a OUTER APPLY (SELECT sum(plan_value)/@monthworkdays*@workdays as target ,sum(plan_value) as target_month FROM .fill_plan_production_value AS b WHERE b.month_id = CONVERT(VARCHAR(7),a.inputdate,23) AND b.subcompanyname = a.subcompanyname ) AS t WHERE CONVERT(VARCHAR(10),a.inputdate,120) >= @date1 AND CONVERT(VARCHAR(10),a.inputdate,120) <= @date2 AND a.deptname IS NOT NULL -- AND deptname NOT IN ('润信环保营业部','BG2总经办','越南营业部','网络营销部','至美营业支持B组','至美营业支持A组','华工佳源营业部','供应链A组','华成营业三部-纸板','供应链','营三A部','至美营业四部') and a.protype IN ('N','O','R','X','B') AND ISNULL(a.isinternal,'') != 'Y' GROUP BY CONVERT(VARCHAR(7),a.inputdate,23) ,a.subcompanyname , t.target , t.target_month ORDER BY 1,2 RETURN END-- 按营业部查询各事业处接单情况SELECT CONVERT(VARCHAR(7),a.inputdate,23) month_id ,a.subcompanyname ,CONVERT(NUMERIC(10,2),SUM(a.notax_unitpce_amount /10000)) AS notax_unitpce_amount ,t.target ,@workdays AS workdays ,t.target_month ,@salesdept AS 营业部 ,@monthworkdays AS monthworkdaysFROM goods_order_receiving aOUTER APPLY (SELECT sum(b.target)/@monthworkdays*@workdays as target ,sum(target) as target_month FROM .fill_order_target AS b WHERE b.month_id = CONVERT(VARCHAR(7),a.inputdate,23) AND b.subcompanyname = a.subcompanyname AND b.salesdeptdesc = @salesdept) AS tWHERE a.inputdate BETWEEN @date1 AND @date2 AND a.deptname IS NOT NULL AND a.protype IN ('N','O','R','X','B') AND ISNULL(a.isinternal,'') != 'Y' AND @salesdept IN ('','%', a.deptname, a.salesdeptdesc, a.dept )GROUP BY CONVERT(VARCHAR(7),a.inputdate,23) ,a.subcompanyname , t.target , t.target_month ORDER BY 1,2END
