销售和预测订单区间分布

–金蝶K3的销售订单和预测订单的分布情况

USE [AIS20101130170856]
GO
/****** Object:  StoredProcedure [dbo].[test1124]    Script Date: 03/29/2019 17:04:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure [dbo].[sp_yx_dd]
as
declare @date1 as varchar(20)
declare @date2 as varchar(20)
declare @date3 as varchar(20)
declare @date4 as varchar(20)
declare @date5 as varchar(20)
declare @date6 as varchar(20)
declare @date7 as varchar(20)
declare @date8 as varchar(20)
declare @date9 as varchar(20)
declare @date10 as varchar(20)
declare @date11 as varchar(20)
declare @date12 as varchar(20)
set nocount on
select @date1=left(convert( varchar(20),dateadd(mm,datediff(mm,0,getdate()),0),121),7)
select @date2=left(convert( varchar(20),dateadd(mm,datediff(mm,0,getdate())+1,0),121),7)
select @date3=left(convert( varchar(20),dateadd(mm,datediff(mm,0,getdate())+2,0),121),7)
select @date4=left(convert( varchar(20),dateadd(mm,datediff(mm,0,getdate())+3,0),121),7)
select @date5=left(convert( varchar(20),dateadd(mm,datediff(mm,0,getdate())+4,0),121),7)
select @date6=left(convert( varchar(20),dateadd(mm,datediff(mm,0,getdate())+5,0),121),7)
select @date7=left(convert( varchar(20),dateadd(mm,datediff(mm,0,getdate())+6,0),121),7)
select @date8=left(convert( varchar(20),dateadd(mm,datediff(mm,0,getdate())+7,0),121),7)
select @date9=left(convert( varchar(20),dateadd(mm,datediff(mm,0,getdate())+8,0),121),7)
select @date10=left(convert( varchar(20),dateadd(mm,datediff(mm,0,getdate())+9,0),121),7)
select @date11=left(convert( varchar(20),dateadd(mm,datediff(mm,0,getdate())+10,0),121),7)
select @date12=left(convert( varchar(20),dateadd(mm,datediff(mm,0,getdate())+11,0),121),7)

if object_id('tempdb..#test1124') is not null
        drop table #test1124

select *  into  #test1124 from 
(select   convert(varchar(100),b.FDate,21) fldate ,b.fitemid,b.FAuxQty-b.FStockQty ddqty,'销售订单' fenlei
from SEOrder a inner join SEOrderEntry b on a.FInterID=b.FInterID 
where a.FChangeMark=0 and b.FMRPClosed=0 and a.FCancellation=0  
union all
select convert(varchar(100),FNeeddate ,21) fldate,
b.fitemid ,b.fqty-b.FAuxSelQty ,'预测订单'  fenlei
from 
PPOrder  a 
inner join PPOrderEntry b on a.finterid=b.finterid
where  b.FOrderClosed=0  and (b.fqty-b.FAuxSelQty)>0 ) t
order by fldate
--
update  #test1124
set fldate=convert(varchar(100),DATEADD(mm,datediff(mm,0,getdate()),0),21)
where FlDate<DATEADD(mm,datediff(mm,0,getdate()),0)
--
if object_id('tempdb..#test1125') is not null
        drop table #test1125
 select fenlei,left(convert( varchar(20),fldate ,121),7) fldate,fitemid,ddqty into #test1125
 from #test1124    
 
 
 --
 if object_id('a_yx_dd') is not null
        drop table a_yx_dd  
select a.fenlei,b.FNumber 产品编码,b.FName 型号,b.FModel 规格,
sum(case when a.fldate=@date1 then  a.ddqty else 0 end )  as  yuefen1,
sum(case when a.fldate=@date2 then  a.ddqty else 0 end )  as  yuefen2,
sum(case when a.fldate=@date3 then  a.ddqty else 0 end )  as  yuefen3,
sum(case when a.fldate=@date4 then  a.ddqty else 0 end )  as  yuefen4,
sum(case when a.fldate=@date5 then  a.ddqty else 0 end )  as  yuefen5,
sum(case when a.fldate=@date6 then  a.ddqty else 0 end )  as  yuefen6,
sum(case when a.fldate=@date7 then  a.ddqty else 0 end )  as  yuefen7,
sum(case when a.fldate=@date8 then  a.ddqty else 0 end )  as  yuefen8,
sum(case when a.fldate=@date9 then  a.ddqty else 0 end )  as  yuefen9,
sum(case when a.fldate=@date10 then  a.ddqty else 0 end )  as  yuefen10,
sum(case when a.fldate=@date11 then  a.ddqty else 0 end )  as  yuefen11,
sum(case when a.fldate=@date12 then  a.ddqty else 0 end )  as  yuefen12,
kucqty,YXFQTY,shFQTY,wscqty  into a_yx_dd
from 
 #test1125 a
left join t_ICItem b on a.FItemID=b.FItemID
left join 
(select a.FItemID  kcFItemID,sum(FQty) kucqty from 
IcInventory  a 
left join t_Stock d on a.FStockID=d.FItemID
where d.FNumber not in ('TY05','TY22','TY23','TY12','ty18') group by a.FItemID) c on a.FItemID=c.kcFItemID
left join (
select  A.FItemID YXFItemID,FQTY  YXFQTY from 
IcInventory  a 
left join t_Stock d on a.FStockID=d.FItemID where d.FNumber='ty12')  YX on YX.YXFItemID=a.FItemID
left join (
select  A.FItemID SHFItemID,FQTY  shFQTY from 
IcInventory  a 
left join t_Stock d on a.FStockID=d.FItemID where d.FNumber='ty18')  SH on SH.SHFItemID=a.FItemID
left join 
(select a.FItemID scFItemID,sum(a.FQty-isnull(a.FAuxStockQty,0)) wscqty 
from ICMO a
left join SEOrderEntry b on  b.FInterID=a.FOrderInterID and b.FEntryID=a.FSourceEntryID and b.FItemID=a.FItemID
left join  dbo.t_Item_3004 c ON a.FHeadSelfJ0184 = c.FItemID
where a.FStatus in (0,5,1) and b.FQty is not null and a.FCancellation=0 AND c.FName NOT LIKE'%ZZB%'
group by a.FItemID) sc on sc.scFItemID=a.FItemID
group by b.FNumber,b.FName,b.FModel,a.fenlei,kucqty,YXFQTY,shFQTY,wscqty
order by  b.FNumber


exec sp_rename 'a_yx_dd.yuefen1',@date1
exec sp_rename 'a_yx_dd.yuefen2',@date2
exec sp_rename 'a_yx_dd.yuefen3',@date3
exec sp_rename 'a_yx_dd.yuefen4',@date4
exec sp_rename 'a_yx_dd.yuefen5',@date5
exec sp_rename 'a_yx_dd.yuefen6',@date6
exec sp_rename 'a_yx_dd.yuefen7',@date7
exec sp_rename 'a_yx_dd.yuefen8',@date8
exec sp_rename 'a_yx_dd.yuefen9',@date9
exec sp_rename 'a_yx_dd.yuefen10',@date10
exec sp_rename 'a_yx_dd.yuefen11',@date11
exec sp_rename 'a_yx_dd.yuefen12',@date12


--

select  * from a_yx_dd

set nocount off
--exec test1124