SELECT KCRKD1_CKBH ,KCRKD2_WLBH 物料编号,RK.KCRKD2_ZYX1 幅宽,KCRKD2_PCH 批次号,ROUND(ISNULL(RK.RKSL ,0)-ISNULL(CK.CKSL ,0),2) 库存,
ROUND(ISNULL(RK.RKJS,0) -ISNULL(CK.CKJS,0),2) 库存件数,RK.KCRKD1_KCYWRQ 入库日期,KCRKD2_LSBH INTO #RFKC
FROM (SELECT KCRKD1_CKBH ,KCRKD2_WLBH ,KCRKD2_PCH ,SUM(ROUND(KCRKD2_SSSL,2)) RKSL,SUM(KCRKD2_U4) RKJS,KCRKD2_ZYX1,KCRKD1_KCYWRQ ,KCRKD2_LSBH
FROM KCRKD1,KCRKD2,LSWLZD
WHERE KCRKD1_LSBH =KCRKD2_LSBH AND KCRKD2_WLBH =LSWLZD_WLBH AND LSWLZD_LBBH LIKE '60%'
GROUP BY KCRKD1_CKBH ,KCRKD2_WLBH ,KCRKD2_PCH ,KCRKD2_ZYX1,KCRKD1_KCYWRQ ,KCRKD2_LSBH
) RK
LEFT JOIN (SELECT KCCKD1_CKBH ,KCCKD2_WLBH ,KCCKD2_PCH ,SUM(ROUND(KCCKD2_SL,2)) CKSL,SUM(KCCKD2_U1) CKJS
FROM KCCKD1,KCCKD2,LSWLZD
WHERE KCCKD1_LSBH =KCCKD2_LSBH AND KCCKD2_WLBH =LSWLZD_WLBH AND LSWLZD_LBBH LIKE '60%'
GROUP BY KCCKD1_CKBH ,KCCKD2_WLBH ,KCCKD2_PCH
) CK
ON CK.KCCKD2_PCH =RK.KCRKD2_PCH AND CK.KCCKD2_WLBH =RK.KCRKD2_WLBH AND RK.KCRKD1_CKBH =CK.KCCKD1_CKBH
WHERE ROUND(ISNULL(RK.RKSL ,0)-ISNULL(CK.CKSL ,0),2)>'0'
SELECT LSCKZD_CKMC 仓库名称,#RFKC.批次号 ,#RFKC.物料编号 ,#RFKC.入库日期 ,#RFKC.幅宽 ,#RFKC.库存 ,#RFKC.库存件数 ,
SCDDCP_C8 交货期,XSDD_SHDKHMC 客户,ZWZGZD_ZGXM 销售员 ,DATEDIFF(D,#RFKC.入库日期,GETDATE ()) 库龄
INTO #RFXS
FROM #RFKC ,SCDDCP ,XSDD,ZWZGZD ,LSCKZD
WHERE #RFKC.批次号 =SCDDCP_JHPCH AND SCDDCP_LYLSBH=XSDD_DDLS AND LSCKZD_CKBH=#RFKC.KCRKD1_CKBH AND ZWZGZD_ZGBH =XSDD_RYBH
SELECT * FROM #RFXS -----成品库存信息
-------联查原料信息
SELECT #RFXS.* ,LL.批次,LL.原料 ,LL.原料数量
FROM #RFXS
LEFT JOIN (SELECT KCCKD2_CPPC 批次,KCCKD2_WLBH 原料 ,SUM(KCCKD2_SL ) 原料数量
FROM KCCKD2 GROUP BY KCCKD2_CPPC,KCCKD2_WLBH
) LL
ON LL.批次 =#RFXS.批次号
ORDER BY #RFXS.库龄
DROP TABLE #RFKC
DROP TABLE #RFXS