SELECT DISTINCT F2ITNO, MAX(MMITDS),
(CASE WHEN F2TRDT LIKE '201801%' THEN ISNULL(F2RPQT,0) END )+
(CASE WHEN F2TRDT LIKE '201802%' THEN ISNULL(F2RPQT,0) END )+
(CASE WHEN F2TRDT LIKE '201803%' THEN ISNULL(F2RPQT,0) END )+
(CASE WHEN F2TRDT LIKE '201803%' THEN ISNULL(F2RPQT,0) END )+
(CASE WHEN F2TRDT LIKE '201805%' THEN ISNULL(F2RPQT,0) END ) AS [SUM],
(CASE WHEN F2TRDT LIKE '201801%' THEN F2RPQT END ) AS[j1],
(CASE WHEN F2TRDT LIKE '201801%' THEN F2SCOC END) AS[j2] ,
(CASE WHEN F2TRDT LIKE '201801%' THEN F2RCAC END) AS[j3],
(CASE WHEN F2TRDT LIKE '201802%' THEN F2RPQT END) AS[f1],
(CASE WHEN F2TRDT LIKE '201802%' THEN F2SCOC END) AS[f2] ,
(CASE WHEN F2TRDT LIKE '201802%' THEN F2RCAC END) AS[f3],
(CASE WHEN F2TRDT LIKE '201803%' THEN F2RPQT END) AS[m1],
(CASE WHEN F2TRDT LIKE '201803%' THEN F2SCOC END) AS[m2] ,
(CASE WHEN F2TRDT LIKE '201803%' THEN F2RCAC END) AS[m3],
(CASE WHEN F2TRDT LIKE '201804%' THEN F2RPQT END) AS[n1],
(CASE WHEN F2TRDT LIKE '201804%' THEN F2SCOC END) AS[n2] ,
(CASE WHEN F2TRDT LIKE '201804%' THEN F2RCAC END) AS[n3],
(CASE WHEN F2TRDT LIKE '201805%' THEN F2RPQT END) AS[l1],
(CASE WHEN F2TRDT LIKE '201805%' THEN F2SCOC END) AS[l2] ,
(CASE WHEN F2TRDT LIKE '201805%' THEN F2RCAC END) AS[l3]
FROM MVXJDTA.FGRECL
JOIN MVXJDTA.MITMAS ON MMITNO = F2ITNO
WHERE MMITTY IN('400')
GROUP BY F2ITNO,MMITDS,F2SCOC,F2TRDT,F2RPQT,F2RCAC
ORDER BY F2ITNO ASC
SELECT DISTINCT U.F2ITNO,MMITDS,F2PUNO,
(SELECT TOP 1 coalesce(SUM(F2RPQT),0) FROM MVXJDTA.FGRECL WHERE U.F2ITNO = F2ITNO AND F2TRDT LIKE '201801%') +
(SELECT TOP 1 coalesce(SUM(F2RPQT),0) FROM MVXJDTA.FGRECL WHERE U.F2ITNO = F2ITNO AND F2TRDT LIKE '201802%') +
(SELECT TOP 1 coalesce(SUM(F2RPQT),0) FROM MVXJDTA.FGRECL WHERE U.F2ITNO = F2ITNO AND F2TRDT LIKE '201803%') AS SSS,
(CASE WHEN F2TRDT LIKE '201801%' THEN F2RPQT END) AS[j1],
(CASE WHEN F2TRDT LIKE '201801%' THEN F2SCOC END) AS[j2] ,
(CASE WHEN F2TRDT LIKE '201801%' THEN F2RCAC END) AS[j3],
(CASE WHEN F2TRDT LIKE '201802%' THEN F2RPQT END) AS[f1],
(CASE WHEN F2TRDT LIKE '201802%' THEN F2SCOC END) AS[f2] ,
(CASE WHEN F2TRDT LIKE '201802%' THEN F2RCAC END) AS[f3],
(CASE WHEN F2TRDT LIKE '201803%' THEN F2RPQT END) AS[m1],
(CASE WHEN F2TRDT LIKE '201803%' THEN F2SCOC END) AS[m2] ,
(CASE WHEN F2TRDT LIKE '201803%' THEN F2RCAC END) AS[m3]
FROM MVXJDTA.FGRECL U
left OUTER JOIN MVXJDTA.MITMAS ON MMITNO = U.F2ITNO
WHERE MMITTY IN('400')
GROUP BY U.F2ITNO,MMITDS,U.F2SCOC,U.F2RPQT,U.F2RCAC,U.F2TRDT,F2PUNO
ORDER BY U.F2ITNO ASC
ผมติดที่ว่าต้องการจะเอา column ที่เป็น null ทั้งหมด ให้เอาออกไปครับ ผมใช้ is not null แล้วก็ไม่ออก ผมต้องเอาข้อมูลไป fill ลงใน gridview ครับ
Load balance : Server 04
