SELECT M3.IDHINMOKU
      ,BT010.TXHINBAN
      ,BT010.TXHINMEI
      ,BT010.KBHINSYU
      ,BT010.KBUNIT
      ,M3.HIZUKE
      ,M3.KBN
      ,M3.RUIKEI
FROM (
SELECT M2.*
      ,ROW_NUMBER() OVER (PARTITION BY IDHINMOKU ORDER BY M2.SEQ DESC) SEQ2
      ,SUM(CASE WHEN SEQ = 1 THEN M2.SURYO + (CASE WHEN M2.QTGENZAIKO IS NULL THEN 0 ELSE M2.QTGENZAIKO END) ELSE M2.SURYO END) over(PARTITION BY IDHINMOKU order by M2.SEQ) as RUIKEI
  FROM (

SELECT
 M1.*
,ROW_NUMBER() OVER (PARTITION BY IDHINMOKU ORDER BY HIZUKE ASC) SEQ
,(SELECT SUM(QTGENZAIKO) FROM FT010 WHERE FT010.IDHINMOKU = M1.IDHINMOKU) QTGENZAIKO

FROM (
-- 製造入庫予定
SELECT
   JT010.QTSEIZO AS SURYO
  ,JT010.IDHINMOKU
  ,JT030.DTTHRU AS HIZUKE
  ,'060' AS KBN -- 完成入庫
FROM JT010
LEFT OUTER JOIN (SELECT
                    NRSEIZO
                   ,SQKOUTEI
                   ,CASE WHEN DTTHRU IS NULL THEN GETDATE() ELSE DTTHRU END AS DTTHRU
                   ,ROW_NUMBER() OVER (PARTITION BY NRSEIZO ORDER BY SQLIST DESC) RNUM
                   FROM JT030 ) JT030 ON JT030.NRSEIZO = JT010.NRSEIZO AND JT030.RNUM = 1
WHERE JT010.STSEIZO < 40
AND JT030.DTTHRU <= /**%HIZUKE%**/'9999/12/31'
UNION ALL
-- 仕入入庫予定
SELECT
   HT011.QTHATTYU * BT010.VLKANZAN AS SURYO
  ,HT011.IDHINMOKU
  ,HT011.DTNOUKI AS HIZUKE
  ,'030' AS KBN -- 仕入入庫
FROM HT011
LEFT JOIN HT020 ON HT020.NRNYUKA = HT011.NRNYUKA
LEFT JOIN BT010 ON BT010.IDHINMOKU = HT011.IDHINMOKU
WHERE ( HT011.FGNYUKA IS NULL OR HT020.STNYUKA = '10')
AND HT011.DTNOUKI <= /**%HIZUKE%**/'9999/12/31'
UNION ALL
-- 売上出庫
SELECT
   JT010.QTJYUTYU * -1 AS SURYO
  ,JT010.IDHINMOKU
  ,JT010.DTNOUKI AS HIZUKE
  ,'130' AS KBN -- 売上出庫
FROM JT010
WHERE JT010.STSEIZO < 50
  AND JT010.QTJYUTYU > 0
  AND JT010.DTNOUKI <= /**%HIZUKE%**/'9999/12/31'
UNION ALL
-- 材料出庫
SELECT
   JT040.QTTOUNYU * -1 AS SURYO
  ,JT040.IDHINMOKU
  ,CASE WHEN JT030.DTFROM IS NULL THEN GETDATE() ELSE JT030.DTFROM END AS HIZUKE
  ,'160' AS KBN -- 材料出庫
FROM JT010
INNER JOIN JT040 ON JT040.NRSEIZO = JT010.NRSEIZO
LEFT  JOIN JT030 ON JT030.NRSEIZO = JT040.NRSEIZO AND JT030.SQKOUTEI = JT040.SQKOUTEI
WHERE JT010.STSEIZO < 30
  AND (CASE WHEN JT030.DTFROM IS NULL THEN GETDATE() ELSE JT030.DTFROM END) <= /**%HIZUKE%**/'9999/12/31'
) M1
) M2
) M3
LEFT JOIN BT010 ON M3.IDHINMOKU = BT010.IDHINMOKU
WHERE M3.SEQ2 = 1