有没有大佬提供下物料,批号对应的期初数据的sql,自己写的sql语句总是跟存货收发汇总表里面的期初数据对不上,下面是我写的

WITH CTE_Data AS (

SELECT

STOCKDIM.FMATERIALID AS FMASTERID,

STOCKDIM.FLOT AS LotID,

CALD.FFINORGID AS FFINORGID,

sum(IV.FQTY) AS Quantity,

AG.FYEAR AS FYEAR,

AG.FPERIOD AS FPERIOD

FROM T_HS_OUTACCTG AG

INNER JOIN T_HS_InivBalance_H IV ON AG.FID = IV.FID

INNER JOIN T_HS_CALDIMENSIONS CALD ON AG.FDIMENSIONID = CALD.FDIMENSIONID

INNER JOIN T_HS_InivStockDimension STOCKDIM ON IV.FDimeEntryId = STOCKDIM.FENTRYID

INNER JOIN T_HS_StockDimension HSDIM ON HSDIM.FENTRYID = IV.FACCTGDIMEENTRYID

left join T_BD_MATERIAL_L MAT ON STOCKDIM.FMATERIALID = MAT.FMATERIALID and MAT.FLOCALEID=2052

left join T_BD_LOTMASTER e on STOCKDIM.FLOT=e.FLOTID

group by STOCKDIM.FMATERIALID,

STOCKDIM.FLOT,

CALD.FFINORGID,

AG.FYEAR,

AG.FPERIOD

)

SELECT

e.FNUMBER,a.*,

isnull(b.Quantity,0) AS '期初'

FROM

CTE_Data a

LEFT JOIN

CTE_Data b

a.FMASTERID = b.FMASTERID

AND a.FFINORGID = b.FFINORGID

and a.LotID=b.LotID

AND b.FYEAR = CASE WHEN a.FPERIOD - 1 > 0 THEN a.FYEAR ELSE a.FYEAR - 1 END

AND b.FPERIOD = CASE WHEN a.FPERIOD - 1 > 0 THEN a.FPERIOD - 1 ELSE 12 END

left join T_BD_LOTMASTER e on a.LotID=e.FLOTID

WHERE a.Quantity IS NOT NULL

产品问题有没有大佬提供下物料,批号对应的期初数据的sql

500 产品问题 有没有大佬提供下物料,批号对应的期初数据的sql