商品流通分析 vresion4.0.rar========================================================================= USE GO/****** Object: StoredProcedure . Script Date: 2023/7/4 15:48:43 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE . @BillNo AS varchar(MAX) , -- 手工单号 √ @person AS varchar(MAX) , -- 操作员√ @beginData AS DATETIME , -- 申请生效日期√ @endData AS DATETIME , -- 结束√ @fundtion AS varchar(MAX) , -- 调拨方式√ @cause AS varchar(MAX) , -- 申请原因 @property AS varchar(MAX) , -- 调出仓库属性 @BillNoState AS varchar(MAX) , -- 申请单状态 @State AS varchar(MAX) , -- 业务状态 @count AS int , -- 调入数量>= @OutCountCause AS varchar(MAX) , -- 调出差异原因 @InCountCause AS varchar(MAX) -- 调入差异原因AS BEGIN set nocount on -- routine body goes here, e.g. -- SELECT 'Navicat for SQL Server' CREATE TABLE #Material ( MaterialID VARCHAR(20) ) --create table #Detail (BillNo varchar(500),SourceBillNo varchar(500),Sequence varchar(500),InReasonID varchar(500),ReasonID varchar(500)) CREATE TABLE #TmpAll (BillStatus VARCHAR(20),SourceBillNo varchar(300),BusinessStatus varchar(20), ReasonID varchar(20),CompanyID VARCHAR(20) , ReqBillNo VARCHAR(200) ,ManualBillNo VARCHAR(200) , ReqCheckDate VARCHAR(200) , Operator VARCHAR(50), MoveModeName VARCHAR(200), OutStockID VARCHAR(40) , InStockID VARCHAR(40) , MaterialID VARCHAR(40) , SizeID VARCHAR(40) , ReqQty INT , OutInBillNo VARCHAR(200) , OutCheckDate VARCHAR(40) , InCheckDate VARCHAR(40) , OutQty INT , OutAmount money , InQty INT , InAmount money , OutBillStatus VARCHAR(10), InBillStatus VARCHAR(10), OutDif INT , InDif INT , OutDateDif INT , InDateDif INT, UnionBillNo VARCHAR(200), --合并前单号 UnionCheckdate VARCHAR(200), --合并前单号生效日期 UnionQty INT, IsUnion VARCHAR(10), RowNumber Int ,DeliveryStatusName VARCHAR(20) ,OutRemark VARCHAR(max),Sequenceq varchar(200)) Insert Into #Material(MaterialID) Select MaterialID From vwSD_Material Where CompanyID = 'YM' -- -- create table #personTemp(UserID varchar(200),UserName varchar(200))-- -- IF @person <> ''-- BEGIN-- insert into #personTemp(UserID,UserName)-- select UserID,UserName from Sys_User-- where UserID IN (select distinct value from dbo.Fr_Split(''+@person+'',','))-- END-- ELSE -- BEGIN-- insert into #personTemp(UserID,UserName)-- select UserID,UserName from Sys_User-- END create table #fundtionTemp(MoveModeID varchar(200),MoveModeName varchar(200)) if @fundtion <> '' BEGIN insert into #fundtionTemp select MoveModeID,MoveModeName from SD_Bas_MoveMode where MoveModeID in (select distinct value from dbo.Fr_Split(''+@fundtion+'',',')) END ELSE BEGIN insert into #fundtionTemp select MoveModeID,MoveModeName from SD_Bas_MoveMode END -- insert into #Detail (BillNo ,SourceBillNo ,Sequence ,InReasonID ,ReasonID )-- SELECT main.BillNo, main.SourceBillNo, Detail.Sequence, Detail.InReasonID, Detail.ReasonID-- from SD_Inv_MoveMaster main-- left join SD_Inv_MoveDetail Detail on Detail.BillNo = main.BillNo-- where main.OutCheckDate BETWEEN @beginData AND @endData-- declare @dataCount int select distinct De.ReasonID as ReasonID,RM.SourceBillNo as SourceBillNo,RM.BillStatus,RM.BusinessStatus,rm.CompanyID,rm.BillNo as ReqBillNo,rm.ManualBillNo, convert(varchar(30),rm.CheckDate,120) as ReqCheckDate,Us.UserName as Operator,MMove.MoveModeName,rm.OutStockID,RM.InStockID, De.Sequence as Sequence,Si.MaterialID,Si.SizeID,Si.Qty as ReqQty,rm.Remark ,gg.StateName as DeliveryStatusName,De.Sequence as Sequenceq into #ReqbillNO from SD_Inv_MoveReqMaster RM Inner JOIN dbo.SD_Inv_MoveReqDetail AS De ON RM.CompanyID=De.CompanyID and RM.BillNo=De.BillNo Inner JOIN dbo.SD_Inv_MoveReqSize AS Si ON RM.CompanyID=Si.CompanyID and RM.BillNo=Si.BillNo and de.Sequence=si.Sequence INNER JOIN #Material Mat ON de.MaterialID = Mat.MaterialID LEFT JOIN SD_Bas_MoveMode MMove on rm.MoveModeID=MMove.MoveModeIDinner JOIN Sys_User Us on rm.Operator=Us.UserID LEFT Join (Select StateId, StateType, CNStateName as StateName From Sys_State Where StateFixFlag = 'MoveOutState') as gg On RM.DeliveryStatus = gg.StateType where RM.CompanyID='YM' and (RM.BillStatus in ('4','6') and RM.CheckDate BETWEEN @beginData AND @endData )and (isnull(@BillNo,'')='' or RM.BillNo=@BillNo) and (isnull(@fundtion,'')='' or MMove.MoveModeID in (select distinct value from dbo.Fr_Split(''+@fundtion+'',','))) and (isnull(@BillNoState,'')='' or RM.BillStatus in (select distinct value from dbo.Fr_Split(''+@BillNoState+'',','))) and (isnull(@State,'')='' or RM.BusinessStatus in (select distinct value from dbo.Fr_Split(''+@State+'',','))) and (isnull(@person,'')='' or Us.UserName like '%'+@State+'%')--@beginData AND @endData) -- select * From #ReqbillNO select distinct r.ReasonID,r.SourceBillNo,r.BillStatus,r.BusinessStatus,MM.CompanyID,MM.BillNo,r.ManualBillNo,r.ReqBillNo,r.ReqCheckDate,r.Operator,r.MoveModeName, r.OutStockID,r.InStockID,r.Remark ,DeliveryStatusNameinto #OutbillNO from SD_Inv_MoveMaster MM inner join #ReqbillNO r on mm.SourceBillNo=r.ReqBillNo and mm.CompanyID=r.CompanyID select MM.BillStatus ,MM.BusinessStatus,MM.ReasonID,MM.SourceBillNo,isnull(mm.CompanyID,rm.CompanyID) as CompanyID,isnull(mm.ReqBillNo,rm.ReqBillNo) as ReqBillNo, isnull(mm.ReqCheckDate,rm.ReqCheckDate) as ReqCheckDate,isnull(mm.Operator,rm.Operator) as Operator, isnull(mm.MoveModeName,rm.MoveModeName) as MoveModeName,isnull(mm.OutStockID,rm.OutStockID) as OutStockID, isnull(mm.InStockID,rm.InStockID) as InStockID,isnull(mm.Sequence,rm.Sequence) as Sequence, isnull(mm.MaterialID,rm.MaterialID) as MaterialID,isnull(mm.SizeID,rm.SizeID) as SizeID, isnull(rm.ReqQty,0) ReqQty,mm.BillNo as OutInBillNo, convert(varchar(30),mm.OutCheckDate,120) as OutCheckDate,convert(varchar(30),mm.InCheckDate,120)as InCheckDate, case when isnull(mm.OutBillStatus,0)=4 then mm.OutQty else 0 end as OutQty, (case when isnull(mm.OutBillStatus,0)=4 then mm.OutQty else 0 end)*RetailPrice as OutAmount,mm.OutBillStatus, case when isnull(mm.InBillStatus,0)=4 then mm.InQty else 0 end as InQty, (case when isnull(mm.InBillStatus,0)=4 then mm.InQty else 0 end)*RetailPrice as InAmount,mm.InBillStatus, isnull(MM.Remark,rm.Remark) as Remark,isnull(MM.ManualBillNo,rm.ManualBillNo) as ManualBillNo ,DeliveryStatusName,rm.Sequenceqinto #ReqResult from ( select req.BillStatus ,req.BusinessStatus,req.ReasonID,req.SourceBillNo,MM.CompanyID,MM.BillNo,mm.OutCheckDate,mm.InCheckDate,mm.OutBillStatus,mm.InBillStatus, MD.SourceBillSequence,MD.Sequence,MD.MaterialID,mi.SizeID,mi.OutQty,mi.InQty,MD.RetailPrice, req.ReqBillNo,req.ManualBillNo,req.ReqCheckDate,req.Operator,req.MoveModeName,req.OutStockID,req.InStockID,req.Remarkfrom SD_Inv_MoveMaster as MM inner join #OutbillNO As req ON MM.CompanyID=req.CompanyID and MM.BillNo=req.BillNo inner join SD_Inv_MoveDetail as MD on MM.CompanyID = MD.CompanyID AND MM.BillNo = MD.BillNo inner join SD_Inv_MoveSize as MI on MM.CompanyID = Mi.CompanyID AND MM.BillNo = Mi.BillNo and MD.Sequence=MI.Sequence INNER JOIN #Material Mat ON MD.MaterialID = Mat.MaterialID where MM.CompanyID='YM') MM full join #ReqbillNO as rm on MM.CompanyID = RM.CompanyID AND MM.ReqBillNo = RM.ReqBillNo and MM.SourceBillSequence=RM.Sequenceq and MM.SizeID=RM.SizeID select d.BillStatus ,d.BusinessStatus,d.ReasonID,d.SourceBillNo,CompanyID,ReqBillNo,OutInBillNo,MaterialID,SizeID ,SUM(d.OutQty) OutQty INTO #t from #ReqResult d where Remark not like '%调拨申请单合并生成%' GROUP BY CompanyID,ReqBillNo,OutInBillNo,MaterialID,SizeID,d.BillStatus ,d.BusinessStatus,d.ReasonID,d.SourceBillNoinsert into #TmpAll(BillStatus ,BusinessStatus,ReasonID,SourceBillNo,CompanyID,ReqBillNo,ManualBillNo,ReqCheckDate,Operator,MoveModeName,OutStockID,InStockID,MaterialID,SizeID, ReqQty,OutInBillNo,OutCheckDate,InCheckDate,OutQty,OutAmount,InQty,InAmount,OutBillStatus,InBillStatus,OutDif,InDif,OutDateDif,InDateDif,UnionBillNo,UnionCheckdate,UnionQty,IsUnion,RowNumber,DeliveryStatusName,OutRemark,Sequenceq) select d.BillStatus ,d.BusinessStatus,d.ReasonID,d.SourceBillNo,d.CompanyID,d.ReqBillNo,d.ManualBillNo,d.ReqCheckDate,d.Operator,d.MoveModeName,d.OutStockID,d.InStockID, d.MaterialID,d.SizeID,d.ReqQty,d.OutInBillNo,d.OutCheckDate,d.InCheckDate,d.OutQty,d.OutAmount,d.InQty,d.InAmount,d.OutBillStatus,d.InBillStatus, (ISNULL(d.ReqQty,0))-e.OutQty AS OutDif,isnull(d.OutQty,0)-isnull(d.InQty,0) as InDif, datediff(day,d.ReqCheckDate,d.OutCheckDate) as OutDateDif, datediff(day,d.OutCheckDate,d.InCheckDate) as InDateDif,'' as UnionBillNo,'' as UnionCheckdate,0 as UnionQty,'No' as IsUnion,0 as RowNumber ,DeliveryStatusName ,d.Remark,d.Sequenceqfrom #ReqResult d LEFT JOIN #t e ON d.CompanyID=e.CompanyID AND d.ReqBillNo=e.ReqBillNo AND d.OutInBillNo=e.OutInBillNo AND d.MaterialID=e.MaterialID AND d.SizeID =e.SizeID where d.Remark not like '%调拨申请单合并生成%' CREATE TABLE #UnionBillNo (CompanyID VARCHAR(20),ReqBillNo VARCHAR(60),UnionBillno VARCHAR(60)) insert into #TmpAll(BillStatus ,ReasonID,SourceBillNo,BusinessStatus,CompanyID,ReqBillNo,ManualBillNo,ReqCheckDate,Operator,MoveModeName,OutStockID,InStockID,MaterialID,SizeID,ReqQty,OutInBillNo,OutCheckDate, InCheckDate,OutQty,OutAmount,InQty,InAmount,OutBillStatus,InBillStatus,OutDif,InDif,OutDateDif,InDateDif,UnionBillNo,UnionCheckdate,UnionQty,IsUnion,RowNumber,DeliveryStatusName) select a.BillStatus ,a.BusinessStatus,a.ReasonID,a.SourceBillNo,a.CompanyID,a.ReqBillNo,a.ManualBillNo,a.ReqCheckDate,a.Operator,a.MoveModeName,a.OutStockID,a.InStockID, a.MaterialID,a.SizeID,a.ReqQty,a.OutInBillNo,a.OutCheckDate,a.InCheckDate,a.OutQty,a.OutAmount,a.InQty,a.InAmount,a.OutBillStatus,a.InBillStatus, isnull(a.ReqQty,0)-(SELECT SUM(isnull(t.OutQty,0)) FROM #ReqResult t WHERE t.ReqBillNo=a.ReqBillNo AND t.OutInBillNo<=a.OutInBillNo AND t.MaterialID=a.MaterialID AND a.SizeID=t.SizeID) as OutDif,isnull(a.OutQty,0)-isnull(a.InQty,0) as InDif, datediff(day,a.ReqCheckDate,a.OutCheckDate) as OutDateDif,datediff(day,a.OutCheckDate,a.InCheckDate) as InDateDif, b.UnionBillno,'' as UnionCheckdate,isnull(c.UnionQty,0) as UnionQty,'Yes' as IsUnion, ROW_NUMBER()OVER(Partition by a.CompanyID,a.ReqBillNo,a.MaterialID,a.SizeID order by b.UnionBillno) as RowNumber ,DeliveryStatusNamefrom ( select distinct a.BillStatus ,a.ReasonID,a.SourceBillNo,a.BusinessStatus,a.CompanyID,a.ReqBillNo,a.ManualBillNo,a.ReqCheckDate,a.Operator,a.MoveModeName,a.OutStockID,a.InStockID, a.MaterialID,a.SizeID,OutInBillNo,OutCheckDate,InCheckDate,a.OutBillStatus,a.InBillStatus,a.remark,sum(ReqQty) as ReqQty, sum(OutQty) as OutQty,sum(InQty) as InQty,sum(OutAmount) as OutAmount,sum(InAmount) as InAmount ,DeliveryStatusNamefrom #ReqResult a group by a.CompanyID,a.ReqBillNo,a.ManualBillNo,a.ReqCheckDate,a.Operator,a.MoveModeName,a.OutStockID,a.InStockID, a.MaterialID,a.SizeID,OutInBillNo,OutCheckDate,InCheckDate,a.OutBillStatus,a.InBillStatus,a.remark,DeliveryStatusName,a.BillStatus,a.BusinessStatus,a.ReasonID,a.SourceBillNo) a left join #UnionBillNo b on a.ReqBillNo=b.ReqBillNo left join ( select rm.BillNo,rm.CheckDate,de.MaterialID,si.SizeID,sum(si.Qty) as UnionQty from SD_Inv_MoveReqMaster RM Inner JOIN dbo.SD_Inv_MoveReqDetail AS De ON RM.CompanyID=De.CompanyID and RM.BillNo=De.BillNo Inner JOIN dbo.SD_Inv_MoveReqSize AS Si ON RM.CompanyID=Si.CompanyID and RM.BillNo=Si.BillNo and de.Sequence=si.Sequence where rm.CompanyID='YM' and rm.BillNo in (select UnionBillno from #UnionBillNo) group by rm.BillNo,rm.CheckDate,de.MaterialID,si.SizeID ) c on b.UnionBillno=c.BillNo and a.MaterialID=c.MaterialID and a.SizeID=c.SizeID where a.Remark like '%调拨申请单合并生成%'select mm.CompanyID,MMove.MoveModeName,mm.OutStockID,mm.InStockID, md.Sequence,mi.MaterialID,mi.SizeID,mm.BillNo as OutInBillNo, convert(varchar(30),mm.OutCheckDate,120) as OutCheckDate,convert(varchar(30),mm.InCheckDate,120)as InCheckDate, case when isnull(mm.OutBillStatus,0)=4 then mi.OutQty else 0 end as OutQty, (case when isnull(mm.OutBillStatus,0)=4 then mi.OutQty else 0 end)*MD.RetailPrice as OutAmount,OutBillStatus, case when isnull(mm.InBillStatus,0)=4 then mi.InQty else 0 end as InQty, (case when isnull(mm.InBillStatus,0)=4 then mi.InQty else 0 end)*MD.RetailPrice as InAmount,InBillStatus ,MM.OutRemarkinto #OutResult from SD_Inv_MoveMaster MM INNER JOIN SD_Inv_MoveDetail as MD on MM.CompanyID = MD.CompanyID AND MM.BillNo = MD.BillNo INNER JOIN SD_Inv_MoveSize as MI on MM.CompanyID = Mi.CompanyID AND MM.BillNo = Mi.BillNo and MD.Sequence=MI.Sequence INNER JOIN #Material Mat ON MD.MaterialID = Mat.MaterialID LEFT JOIN SD_Bas_MoveMode MMove on MM.MoveModeID=MMove.MoveModeID LEFT JOIN dbo.SD_Bas_MoveMode AS BM ON MM.MoveModeID = BM.MoveModeID where MM.CompanyID='YM' and isnull(mm.SourceBillNo,'')='' and (MM.OutBillStatus=4 and MM.OutCheckDate BETWEEN @beginData AND @endData ) insert into #TmpAll(CompanyID,ReqBillNo,ManualBillNo,ReqCheckDate,Operator,MoveModeName,OutStockID,InStockID,MaterialID,SizeID, ReqQty,OutInBillNo,OutCheckDate,InCheckDate,OutQty,OutAmount,InQty,InAmount,OutBillStatus,InBillStatus,OutDif,InDif,OutDateDif,InDateDif,UnionBillNo,UnionCheckdate,UnionQty,IsUnion,RowNumber,OutRemark) select a.CompanyID,'' as ReqBillNo,'','' as ReqCheckDate,'' as Operator,a.MoveModeName,a.OutStockID,a.InStockID, a.MaterialID,a.SizeID,0 as ReqQty,a.OutInBillNo,a.OutCheckDate,a.InCheckDate,a.OutQty,a.OutAmount,a.InQty,a.InAmount,OutBillStatus,InBillStatus, 0 as OutDif,isnull(a.OutQty,0)-isnull(a.InQty,0) as InDif, 0 as OutDateDif,datediff(day,a.OutCheckDate,a.InCheckDate) as InDateDif, '' as UnionBillno,'' as UnionCheckdate,0 as UnionQty,'No' as IsUnion,0 as RowNumber ,a.OutRemark as OutRemarkfrom #OutResult a select a.CompanyID ,a.MaterialID ,a.MaterialCode ,pa.RetailPrice,pa.yearNo ,so.SeasonName ,a.MaterialProperty , a.SeriesID ,a.ItemID ,a.ModelID ,a.SexID ,a.StyleCode ,a.MaterialShortName,a.MaterialName ,a.StockDate , convert(varchar(10),pa.SaleDate,121) SaleDate, a.IsActivity ,a.ColorID ,CardName ,KindName ,SeriesName ,a.SubSeries , ItemName ,ModelName ,a.SubModel ,SexName , ColorName ,ColorCode, a.remark,a.WholePrice, a.Channel ,a.SubItem ,a.MasterItem,a.CardID ,a.KindID,a.SizeTypeID,pa.SeasonID,a.Discount,a.ColorName2,a.stuffname,b.accreditName, c.CommodityLevelName,a.styleID,a.VendCustID,a.Volume,a.WeightNo,a.BoxBoard, ma.BandCode,ma.ListingBatchinto #vwSD_Material from vwSD_Material a left join SD_Mat_Accredit b on a.accreditID = b.accreditID left join SD_Mat_CommodityLevel c on a.commoditylevelID = c.commoditylevelID left join sd_mat_materialParam pa on A.MaterialID = pa.MaterialID AND A.COMPANYID=pa.COMPANYID left join sd_mat_season so on pa.seasonid=so.seasonid left join SD_Mat_Material ma on ma.MaterialID=a.MaterialIDWHERE a.CompanyID='YM' AND a.MaterialID in (select MaterialID from #Material) and a.YearNo >= DATEPART(yy,@beginData)-1CREATE INDEX #vwSD_Material ON #vwSD_Material(MaterialID,CompanyID) create table #MoveReasonTemp (ReasonID varchar(500),ReasonName varchar(500),ReasonType varchar(200))if @cause <> ''BEGIN insert into #MoveReasonTemp (ReasonID ,ReasonName,ReasonType ) select ReasonID,ReasonName,ReasonType from SD_Bas_MoveReason where ReasonID in (select distinct value from dbo.Fr_Split(''+@cause+'',','))ENDELSEBEGIN insert into #MoveReasonTemp (ReasonID ,ReasonName,ReasonType ) select ReasonID,ReasonName,ReasonType from SD_Bas_MoveReasonEND create table #propertyTemp (StockID varchar(500),StockName varchar(200),CompanyID varchar(500))if @property <> ''BEGIN insert into #propertyTemp (StockID ,StockName ,CompanyID ) select StockID,StockName,'YM' from Bas_Stock where StockPropertyID in (select distinct value from dbo.Fr_Split(''+@property+'',','))END ELSEBEGINinsert into #propertyTemp (StockID ,StockName ,CompanyID ) select StockID,StockName,'YM' from Bas_Stock END create table #tableA_ (qty int , CheckDate DATETIME , SizeName varchar(300), MaterialCode varchar(300), StockID varchar(300),days int,roud int )insert into #tableA_ (qty,CheckDate,SizeName,MaterialCode,StockID,days)SELECT SUM(Detail1.qty) counts,master1.CheckDate ,sizes1.SizeName,Detail1.MaterialCode,Detail1.StockID,7 AS days FROM SD_POS_SaleDetail Detail1 left join SD_POS_SaleMaster master1 on master1.BillNo = Detail1.BillNo and master1.CompanyID='YM' left join SD_Mat_Size sizes1 on sizes1.SizeID = Detail1.SizeIDWHERE master1.CheckDate BETWEEN CONVERT(varchar(100), @beginData, 20) and CONVERT(varchar(100),@endData, 20) and Detail1.CompanyID='YM'group by Detail1.BillNo,sizes1.SizeName,Detail1.MaterialCode,Detail1.StockID,master1.CheckDateUNION ALLSELECT SUM(Detail2.qty) counts,master2.CheckDate ,sizes2.SizeName,Detail2.MaterialCode,Detail2.StockID ,7 AS daysFROM SD_POS_SaleDetail2 Detail2left join SD_POS_SaleMaster2 master2 on master2.BillNo = Detail2.BillNo and master2.CompanyID='YM'left join SD_Mat_Size sizes2 on sizes2.SizeID = Detail2.SizeID-- CONVERT(varchar(100),@endData, 20) WHERE master2.CheckDate BETWEEN CONVERT(varchar(100), @beginData, 20) and CONVERT(varchar(100),@endData, 20) and Detail2.CompanyID='YM'group by Detail2.BillNo,sizes2.SizeName,Detail2.MaterialCode,Detail2.StockID,master2.CheckDate create table #tableB_ (qty int , CheckDate DATETIME , SizeName varchar(300), MaterialCode varchar(300), StockID varchar(300),days int ) insert into #tableB_ (qty,CheckDate,SizeName,MaterialCode,StockID,days)SELECT SUM(Detail1.qty) counts,master1.CheckDate ,sizes1.SizeName,Detail1.MaterialCode,Detail1.StockID ,14 AS days FROM SD_POS_SaleDetail Detail1 left join SD_POS_SaleMaster master1 on master1.BillNo = Detail1.BillNo and master1.CompanyID='YM' left join SD_Mat_Size sizes1 on sizes1.SizeID = Detail1.SizeIDWHERE master1.CheckDate BETWEEN CONVERT(varchar(100), @beginData, 20) and CONVERT(varchar(100),@endData, 20) and Detail1.CompanyID='YM'group by Detail1.BillNo,sizes1.SizeName,Detail1.MaterialCode,Detail1.StockID,master1.CheckDateUNION ALLSELECT SUM(Detail2.qty) counts,master2.CheckDate ,sizes2.SizeName,Detail2.MaterialCode,Detail2.StockID ,14 AS daysFROM SD_POS_SaleDetail2 Detail2left join SD_POS_SaleMaster2 master2 on master2.BillNo = Detail2.BillNo and master2.CompanyID='YM'left join SD_Mat_Size sizes2 on sizes2.SizeID = Detail2.SizeID-- CONVERT(varchar(100), @endData, 20 ）WHERE master2.CheckDate BETWEEN CONVERT(varchar(100),@beginData, 20) and CONVERT(varchar(100),@endData, 20) and Detail2.CompanyID='YM'group by Detail2.BillNo,sizes2.SizeName,Detail2.MaterialCode,Detail2.StockID,master2.CheckDate create table #tableC_ (qty int , CheckDate DATETIME , SizeName varchar(300), MaterialCode varchar(300), StockID varchar(300),days int ) insert into #tableC_ (qty,CheckDate,SizeName,MaterialCode,StockID,days)SELECT SUM(Detail1.qty) counts,master1.CheckDate ,sizes1.SizeName,Detail1.MaterialCode,Detail1.StockID ,21 AS days FROM SD_POS_SaleDetail Detail1 left join SD_POS_SaleMaster master1 on master1.BillNo = Detail1.BillNo and master1.CompanyID='YM' left join SD_Mat_Size sizes1 on sizes1.SizeID = Detail1.SizeIDWHERE master1.CheckDate BETWEEN CONVERT(varchar(100), @beginData, 20) and CONVERT(varchar(100),@endData, 20) and Detail1.CompanyID='YM'group by Detail1.BillNo,sizes1.SizeName,Detail1.MaterialCode,Detail1.StockID,master1.CheckDateUNION ALL SELECT SUM(Detail2.qty) counts,master2.CheckDate ,sizes2.SizeName,Detail2.MaterialCode,Detail2.StockID ,21 AS daysFROM SD_POS_SaleDetail2 Detail2left join SD_POS_SaleMaster2 master2 on master2.BillNo = Detail2.BillNo and master2.CompanyID='YM'left join SD_Mat_Size sizes2 on sizes2.SizeID = Detail2.SizeID -- CONVERT(varchar(100), @endData, 20 )WHERE master2.CheckDate BETWEEN CONVERT(varchar(100), @beginData, 20) and CONVERT(varchar(100),@endData, 20) and Detail2.CompanyID='YM'group by Detail2.BillNo,sizes2.SizeName,Detail2.MaterialCode,Detail2.StockID,master2.CheckDate create table #tableD_ (qty int , CheckDate DATETIME , SizeName varchar(300), MaterialCode varchar(300), StockID varchar(300),days int ) insert into #tableD_ (qty,CheckDate,SizeName,MaterialCode,StockID,days)SELECT SUM(Detail1.qty) counts,master1.CheckDate ,sizes1.SizeName,Detail1.MaterialCode,Detail1.StockID ,28 AS days FROM SD_POS_SaleDetail Detail1 left join SD_POS_SaleMaster master1 on master1.BillNo = Detail1.BillNo and master1.CompanyID='YM' left join SD_Mat_Size sizes1 on sizes1.SizeID = Detail1.SizeIDWHERE master1.CheckDate BETWEEN CONVERT(varchar(100), @beginData, 20) and CONVERT(varchar(100),@endData, 20) and Detail1.CompanyID='YM'group by Detail1.BillNo,sizes1.SizeName,Detail1.MaterialCode,Detail1.StockID,master1.CheckDateUNION ALLSELECT SUM(Detail2.qty) counts,master2.CheckDate ,sizes2.SizeName,Detail2.MaterialCode,Detail2.StockID ,28 AS daysFROM SD_POS_SaleDetail2 Detail2left join SD_POS_SaleMaster2 master2 on master2.BillNo = Detail2.BillNo and master2.CompanyID='YM'left join SD_Mat_Size sizes2 on sizes2.SizeID = Detail2.SizeID-- CONVERT(varchar(100), @endData, 20 WHERE master2.CheckDate BETWEEN CONVERT(varchar(100), @beginData, 20) and CONVERT(varchar(100),@endData, 20) and Detail2.CompanyID='YM'group by Detail2.BillNo,sizes2.SizeName,Detail2.MaterialCode,Detail2.StockID,master2.CheckDatecreate table #tableA_2 (id int ,qty int ,CheckDate DATETIME, SizeName varchar(300), MaterialCode varchar(300), StockID varchar(300),days int )INSERT INTO #tableA_2 (id,qty , CheckDate,SizeName , MaterialCode , StockID ,days )SELECT round(1000*rand(),0),SUM(qty) AS qty,CONVERT(DATETIME, left(CheckDate,10), 0),SizeName,MaterialCode,StockID,days FROM #tableA_GROUP BY MaterialCode,SizeName,StockID,days,CheckDatecreate table #tableB_2 (id int ,qty int ,CheckDate DATETIME, SizeName varchar(300), MaterialCode varchar(300), StockID varchar(300),days int )INSERT INTO #tableB_2 (id,qty ,CheckDate, SizeName , MaterialCode , StockID ,days )SELECT round(1000*rand(),0),SUM(qty) AS qty,CONVERT(DATETIME, left(CheckDate,10), 0),SizeName,MaterialCode,StockID,days FROM #tableB_GROUP BY MaterialCode,SizeName,StockID,days,CheckDatecreate table #tableC_2 (id int ,qty int ,CheckDate DATETIME, SizeName varchar(300), MaterialCode varchar(300), StockID varchar(300),days int )INSERT INTO #tableC_2 (id,qty , CheckDate,SizeName , MaterialCode , StockID ,days )SELECT round(1000*rand(),0),SUM(qty) AS qty,CONVERT(DATETIME, left(CheckDate,10), 0),SizeName,MaterialCode,StockID,days FROM #tableC_GROUP BY MaterialCode,SizeName,StockID,days,CheckDatecreate table #tableD_2 (id int ,qty int ,CheckDate DATETIME, SizeName varchar(300), MaterialCode varchar(300), StockID varchar(300),days int )INSERT INTO #tableD_2 (id,qty ,CheckDate, SizeName , MaterialCode , StockID ,days ) SELECT round(10000*rand(),0),SUM(qty) AS qty,CONVERT(DATETIME, left(CheckDate,10), 0),SizeName,MaterialCode,StockID,days FROM #tableD_GROUP BY MaterialCode,SizeName,StockID,days,CheckDate select * from #tableA_2 where MaterialCode = 'TG4F38054ADA0A' and SizeName = 'L' and StockID = 'YM001622' select * from #tableB_2 where MaterialCode = 'TG4F38054ADA0A' and SizeName = 'L' and StockID = 'YM001622' select * from #tableC_2 where MaterialCode = 'TG4F38054ADA0A' and SizeName = 'L' and StockID = 'YM001622' select * from #tableD_2 where MaterialCode = 'TG4F38054ADA0A' and SizeName = 'L' and StockID = 'YM001622' SELECT a.CompanyID, a.ManualBillNo, a.OutStockID, a.InStockID, a.InBillStatus, a.OutBillStatus , a.SourceBillNo, b.MaterialID, c.SizeID, c.OutQty, CASE WHEN a.BillTypeID = 'Inv_MovIn' THEN c.InQty ELSE NULL END AS InQty, b.SourceBillSequence, b.ReasonID, b.InReasonID,a.OutChecker,a.InChecker Into #Tmp12FROM SD_Inv_MoveMaster aINNER JOIN SD_Inv_MoveDetail b ON a.CompanyID = b.CompanyID AND a.BillNo = b.BillNo INNER JOIN SD_Inv_MoveSize c ON b.CompanyID = c.CompanyID AND b.BillNo = c.BillNo AND b.Sequence = c.SequenceWhere ( a.OutCheckDate between @beginData and @endData or a.InCheckDate between @beginData and @endData ) declare @strString VARCHAR(max) select ReqBillNo,ReqCheckDate,state.CNStateName as ReqBillNoStates, states.CNStateName as businessStates,DeliveryStatusName,a.ManualBillNo, Operator,MoveModeName, VOPL.AreaName4 as OutAreaName4, VOPL.AreaName5 as OutAreaName5,st1.StockName as OutStockName,VOPL4.AreaName4 as inAreaName4, VOPL4.AreaName5 as inAreaName5,st2.StockName as InStockName,a.OutRemark, a.MaterialID,sz.SizeName,OutInBillNo,OutCheckDate,InCheckDate,ReqQty,a.OutQty,a.InQty,OutDif,InDif,'' as outDifRemark , '' as inDifRemark , DATEDIFF (DAY,ReqCheckDate , a.OutCheckDate) as outDay ,DATEDIFF (DAY,OutCheckDate, a.InCheckDate) as inDay ,a.OutStockID, a.BillStatus,a.BusinessStatus,a.SourceBillNo,a.InStockID , a_2_M.ReasonName AS MoveReqReasonName,b_2_O.ReasonName as MoveOutReasonName,sum(sonTable1.qty) as counts1,sum(sonTable2.qty) as counts2 ,sum(sonTable3.qty) as counts3,sum(sonTable4.qty) as counts4 from #TmpAll a LEFT OUTER JOIN #Tmp12 as b_2 ON a.ReqBillNo = b_2.SourceBillNo AND a.Sequenceq = b_2.SourceBillSequence AND a.SizeID = b_2.SizeID LEFT OUTER JOIN SD_Bas_MoveReason AS b_2_O ON b_2.ReasonID = b_2_O.ReasonID AND b_2_O.ReasonType = '1' inner join #propertyTemp ST1 on a.OutStockID=ST1.StockID and st1.CompanyID='YM' inner join Bas_Stock ST2 on a.InStockID=ST2.StockID and st2.CompanyID='YM' inner join SD_Mat_Size sz on a.SizeID=sz.SizeID left join #vwSD_Material sm on sm.CompanyID = 'YM' AND a.MaterialID = sm.MaterialID left join vwPM_Bas_Style St on sm.styleID = St.StyleID left join SD_Mat_MaterialSize bar on a.MaterialID=bar.MaterialID and a.SizeID=bar.SizeID left join Sys_State s1 on a.OutBillStatus=s1.StateId and s1.StateFixFlag='BillState' left join Sys_State s2 on a.InBillStatus=s2.StateId and s2.StateFixFlag='BillState' Left Join vwSD_Rpt_StockBy4LevelArea As VOPL On a.CompanyID = VOPL.CompanyID And a.OutStockID = VOPL.StockID Left Join vwSD_Rpt_StockBy4LevelArea As VOPL4 On a.CompanyID = VOPL4.CompanyID And a.InStockID = VOPL4.StockID left join Sys_State as state on state.StateId = a.BillStatus and state.StateFixFlag = 'AuditResult' left join Sys_State as states on states.StateId = a.BusinessStatus and states.StateFixFlag = 'BusState_Move' LEFT OUTER JOIN SD_Bas_MoveReason AS a_2_M ON a.ReasonID = a_2_M.ReasonID AND a_2_M.ReasonType = '0' left join #tableA_2 sonTable1 on sonTable1.SizeName = sz.SizeName and sonTable1.MaterialCode = a.MaterialID and sonTable1.StockID = a.InStockID AND sonTable1.days = 7 and sonTable1.CheckDate BETWEEN CONVERT(varchar(100), a.InCheckDate, 20) and DATEADD(dd,8,CONVERT(varchar(100), a.InCheckDate, 20)) left join #tableB_2 sonTable2 on sonTable2.SizeName = sz.SizeName and sonTable2.MaterialCode = a.MaterialID and sonTable2.StockID = a.InStockID AND sonTable2.days = 14 and sonTable2.CheckDate BETWEEN CONVERT(varchar(100), a.InCheckDate, 20) and DATEADD(dd,15,CONVERT(varchar(100), a.InCheckDate, 20)) left join #tableC_2 sonTable3 on sonTable3.SizeName = sz.SizeName and sonTable3.MaterialCode = a.MaterialID and sonTable3.StockID = a.InStockID AND sonTable3.days = 21 and sonTable3.CheckDate BETWEEN CONVERT(varchar(100), a.InCheckDate, 20) and DATEADD(dd,22,CONVERT(varchar(100), a.InCheckDate, 20)) left join #tableD_2 sonTable4 on sonTable4.SizeName = sz.SizeName and sonTable4.MaterialCode = a.MaterialID and sonTable4.StockID = a.InStockID AND sonTable4.days = 28 and sonTable4.CheckDate BETWEEN CONVERT(varchar(100), a.InCheckDate, 20) and DATEADD(dd,29,CONVERT(varchar(100), a.InCheckDate, 20)) where 1= 1 and a.InQty >= @count and (isnull(@OutCountCause,'')='' or b_2.ReasonID in (select distinct value from dbo.Fr_Split(''+@OutCountCause+'',','))) and (isnull(@InCountCause,'')='' or b_2.ReasonID in (select distinct value from dbo.Fr_Split(''+@InCountCause+'',','))) GROUP BY ReqBillNo,ReqCheckDate,state.CNStateName , states.CNStateName ,DeliveryStatusName,a.ManualBillNo, Operator,MoveModeName, VOPL.AreaName4 , VOPL.AreaName5 ,st1.StockName ,VOPL4.AreaName4 , VOPL4.AreaName5 ,st2.StockName ,a.OutRemark, a.MaterialID,sz.SizeName,OutInBillNo,OutCheckDate,InCheckDate,ReqQty,a.OutQty,a.InQty,OutDif,InDif , ReqCheckDate , OutCheckDate ,a.OutStockID, a.BillStatus,a.BusinessStatus,a.SourceBillNo,a.InStockID,a_2_M.ReasonName,a.Sequenceq,b_2_O.ReasonName order by a.Sequenceq drop table #TmpAll drop table #Material drop table #fundtionTemp drop table #ReqbillNO drop table #OutbillNO drop table #ReqResult drop table #OutResult drop table #vwSD_Material drop table #MoveReasonTemp drop table #propertyTemp drop table #tableA_ drop table #tableB_ drop table #tableC_ drop table #tableD_ END
