sql主表链接四个临时表后，单独求和每个临时表里的值，得到的值却是全部临时表相乘

商品流通分析 vresion4.0.rar

================================================

=========================

USE [HKERP_QZ]

/****** Object: StoredProcedure [dbo].[spSD_Rpt_MovTrack_Ultimate] Script Date: 2023/7/4 15:48:43 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[spSD_Rpt_MovTrack_Ultimate]

@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) -- 调入差异原因

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.MoveModeID

inner 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 ,DeliveryStatusName

into #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.Sequenceq

into #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.Remark

from 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.SourceBillNo

insert 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.Sequenceq

from #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 ,DeliveryStatusName

from

( 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 ,DeliveryStatusName

from #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.OutRemark

into #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 OutRemark

from #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.ListingBatch

into #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.MaterialID

WHERE a.CompanyID='YM' AND a.MaterialID in (select MaterialID from #Material) and a.YearNo >= DATEPART(yy,@beginData)-1

CREATE 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+'',','))

END

ELSE

BEGIN

insert into #MoveReasonTemp (ReasonID ,ReasonName,ReasonType )

select ReasonID,ReasonName,ReasonType from SD_Bas_MoveReason

END

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

ELSE

BEGIN

insert 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.SizeID

WHERE 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.CheckDate

UNION ALL

SELECT SUM(Detail2.qty) counts,master2.CheckDate ,sizes2.SizeName,Detail2.MaterialCode,Detail2.StockID ,7 AS days

FROM SD_POS_SaleDetail2 Detail2

left 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.SizeID

WHERE 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.CheckDate

UNION ALL

SELECT SUM(Detail2.qty) counts,master2.CheckDate ,sizes2.SizeName,Detail2.MaterialCode,Detail2.StockID ,14 AS days

FROM SD_POS_SaleDetail2 Detail2

left 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.SizeID

WHERE 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.CheckDate

UNION ALL

SELECT SUM(Detail2.qty) counts,master2.CheckDate ,sizes2.SizeName,Detail2.MaterialCode,Detail2.StockID ,21 AS days

FROM SD_POS_SaleDetail2 Detail2

left 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.SizeID

WHERE 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.CheckDate

UNION ALL

SELECT SUM(Detail2.qty) counts,master2.CheckDate ,sizes2.SizeName,Detail2.MaterialCode,Detail2.StockID ,28 AS days

FROM SD_POS_SaleDetail2 Detail2

left 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 #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,CheckDate

create 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,CheckDate

create 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,CheckDate

create 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 #Tmp12

FROM SD_Inv_MoveMaster a

INNER 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.Sequence

Where ( 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