sql5

SELECT
SUM(KEIYAK.CNS030) AS SUMCNS030
,SUM(KEIYAK.CNS129) AS SUMCNS129
,KEIYAK.CNSA33
,KEIYAK.CNSB33
,custmr1.CSCTRJ AS CSCTRJ1
,SUM(s1.SJLSRY) AS SUMS1SJLSRY
        ,SUM(s1.SJSTXA) AS SUMS1SJSTXA
        ,SUM(s2.SJLSRY) AS SUMS2SJLSRY
        ,SUM(s2.SJSTXA) AS SUMS2SJSTXA
        ,SUM(s3.SJLSRY) AS SUMS3SJLSRY
        ,SUM(s3.SJSTXA) AS SUMS3SJSTXA
       
FROM
KEIYAK
LEFT OUTER JOIN (
SELECT
SJCNO1
,SJCNO2
,SJCNO3
,SJCNO4
,SJCNO5
,SJSLKI
,SJNKSB
,MAX(SJNKD2) AS SJNKD2
,SUM(SJLSRY) AS SJLSRY
,SUM(SJSTXA) AS SJSTXA
,SJDLTM
FROM
SAIKNJ
GROUP BY
SJCNO1
,SJCNO2
,SJCNO3
,SJCNO4
,SJCNO5
,SJSLKI
,SJNKSB
,SJDLTM
) s1
ON (
s1.SJCNO1 = KEIYAK.CNCNO1
AND s1.SJCNO2 = KEIYAK.CNCNO2
AND s1.SJCNO3 =
KEIYAK.CNCNO3
AND s1.SJCNO4 = KEIYAK.CNCNO4
AND s1.SJCNO5 =
KEIYAK.CNCNO5
AND s1.SJSLKI = KEIYAK.CNSLKI
AND s1.SJDLTM = ''
)
LEFT
OUTER JOIN (
SELECT
SJCNO1
,SJCNO2
,SJCNO3
,SJCNO4
,SJCNO5
,SJSLKI
,SJNKSB
,SUM(SJLSRY) AS SJLSRY
,SUM(SJSTXA) AS SJSTXA
,SJDLTM
,SJNKK2
FROM
SAIKNJ
GROUP BY
SJCNO1
,SJCNO2
,SJCNO3
,SJCNO4
,SJCNO5
,SJSLKI
,SJNKSB
,SJDLTM
,SJNKK2
) s2
ON (
s2.SJCNO1 = KEIYAK.CNCNO1
AND s2.SJCNO2 = KEIYAK.CNCNO2
AND s2.SJCNO3 = KEIYAK.CNCNO3
AND s2.SJCNO4 = KEIYAK.CNCNO4
AND s2.SJCNO5 = KEIYAK.CNCNO5
AND s2.SJSLKI = KEIYAK.CNSLKI
AND s2.SJNKSB =
s1.SJNKSB
AND s2.SJDLTM = ''
AND s2.SJNKK2 != 0
)
LEFT OUTER JOIN (
SELECT
SJCNO1
,SJCNO2
,SJCNO3
,SJCNO4
,SJCNO5
,SJSLKI
,SJNKSB
,SUM(SJLSRY) AS SJLSRY
,SUM(SJSTXA) AS SJSTXA
,SJDLTM
,SJNKK2
FROM
SAIKNJ
GROUP BY
SJCNO1
,SJCNO2
,SJCNO3
,SJCNO4
,SJCNO5
,SJSLKI
,SJNKSB
,SJDLTM
,SJNKK2
) s3
ON (
s3.SJCNO1 =
KEIYAK.CNCNO1
AND s3.SJCNO2 = KEIYAK.CNCNO2
AND s3.SJCNO3 =
KEIYAK.CNCNO3
AND s3.SJCNO4 = KEIYAK.CNCNO4
AND s3.SJCNO5 =
KEIYAK.CNCNO5
AND s3.SJSLKI = KEIYAK.CNSLKI
AND s3.SJNKSB = s1.SJNKSB
AND s3.SJDLTM = ''
AND s3.SJNKK2 = 0
)
LEFT OUTER JOIN ACNTRA
ON (
ANCNO1 =
KEIYAK.CNCNO1
AND ANCNO2 = KEIYAK.CNCNO2
AND ANCNO3 = KEIYAK.CNCNO3
AND
ANCNO4 = KEIYAK.CNCNO4
AND ANCNO5 = KEIYAK.CNCNO5
AND ANSLKI =
KEIYAK.CNSLKI
AND ANDLTM = ''
)
LEFT OUTER JOIN CUSTMR custmr1
ON (
custmr1.CSCST1 = KEIYAK.CNSA33
AND custmr1.CSCST2 = KEIYAK.CNSB33
AND
custmr1.CSDLTM = ''
)
WHERE
KEIYAK.CNDLTM=''
AND KEIYAK.CNKNT2!= 0
AND
KEIYAK.CNMVDT= 0
AND KEIYAK.CNVDT2=0
AND KEIYAK.CNKNT2 >=
#bookingYearMonth1DAT1#
AND KEIYAK.CNKNT2 <= #vSearchEndDate#
AND
KEIYAK.CNS030!=0
AND s1.SJNKSB = '5'

AND


KEIYAK.CNSA33= '5'
AND

KEIYAK.CNSB33='8'

AND

KEIYAK.CNBMCD='9'
AND


TRIM(KEIYAK.CNCNO1) ||
TRIM(KEIYAK.CNCNO2) || TRIM(KEIYAK.CNCNO3)||
TRIM(KEIYAK.CNCNO4) ||
TRIM(KEIYAK.CNCNO5) = '8'
AND
KEIYAK.CNSLKI ='8'


GROUP BY
custmr1.CSCTRJ,
KEIYAK.CNSA33
,KEIYAK.CNSB33
ORDER BY
KEIYAK.CNSA33 ASC
,KEIYAK.CNSB33 ASC