整合SQL,立即解決,立即給分
declare @inventbatchid varchar(50)
declare @itemid varchar(50)
declare @thk_inventd2id varchar(20)
declare @thk_inventd3id varchar(20)
declare @thk_inventd4id varchar(20)
declare @thk_inventdgoldtypeid varchar(10)
declare @thk_inventproducttypeid varchar(10)
declare @startexpDate varchar(10)
declare @endexpdate varchar(10)
declare @litemid varchar(50)
declare @itemgroupid varchar(10)
declare @ktl_stonetypeid varchar(10)
declare @THK_StoneComplexityId varchar(10)
drop table #getinventbatch
select INVENTBATCHID ,ITEMID,expDate
into #getinventbatch from INVENTBATCH
where INVENTBATCHID =@inventbatchid
and ITEMID =@itemid
and CONVERT(VARCHAR(10),expDate ,120) between @startexpDate and @endexpdate
drop table #getbomversion
select distinct #getinventbatch.*,BOMVERSION.THK_INVENTD2ID,
BOMVERSION .THK_INVENTD3ID,BOMVERSION .THK_INVENTD4ID,
BOMVERSION.THK_INVENTDGOLDTYPEID into #getbomversion
from #getinventbatch inner join BOMVERSION
on #getinventbatch.itemid = BOMVERSION .ITEMID
and BOMVERSION.THK_INVENTD2ID=@thk_inventd2id
and BOMVERSION .THK_INVENTD3ID=@thk_inventd3id
and BOMVERSION .THK_INVENTD4ID=@thk_inventd4id
and BOMVERSION.THK_INVENTDGOLDTYPEID=@thk_inventdgoldtypeid
drop table #getinventtable
select distinct #getbomversion.*,INVENTTABLE.THK_INVENTPRODUCTTYPEID
into #getinventtable from #getbomversion inner join INVENTTABLE
on #getbomversion.itemid = INVENTTABLE.ITEMID
and INVENTTABLE.THK_INVENTPRODUCTTYPEID=@thk_inventproducttypeid
and INVENTTABLE.THK_INVENTSILVERITEM
drop table #getinventdim
select distinct #getinventtable.*,INVENTDIM.INVENTDIMID
into #getinventdim from #getinventtable inner join INVENTDIM
on #getinventtable.inventbatchid = INVENTDIM.INVENTBATCHID
drop table #getprodtable
select distinct #getinventdim.*,PRODTABLE.PRODID
into #getprodtable from #getinventdim inner join PRODTABLE
on #getinventdim.inventdimid = PRODTABLE.INVENTDIMID
drop table #getlastwip
select distinct #getprodtable.*, THK_LastWIPMaterialTrans.ITEMID as 'Litemid',
THK_LastWIPMaterialTrans.OUTQTY ,THK_LastWIPMaterialTrans.UNITID
into #getlastwip from #getprodtable inner join
THK_LastWIPMaterialTrans
on #getprodtable.prodid = THK_LastWIPMaterialTrans.PRODID
and THK_LastWIPMaterialTrans.ITEMID=@litemid
drop table #getlastinvent
select distinct #getlastwip.*,INVENTTABLE.ITEMGROUPID ,
INVENTTABLE.KTL_STONETYPEID,INVENTTABLE.THK_StoneComplexityId
into #getlastinvent from #getlastwip inner join INVENTTABLE
on #getlastwip.litemid = INVENTTABLE.ITEMID
and INVENTTABLE.ITEMGROUPID = @itemgroupid
and INVENTTABLE.KTL_STONETYPEID = @ktl_stonetypeid
and INVENTTABLE.THK_StoneComplexityId = @THK_StoneComplexityId