SELECT DISTINCT
ISNULL(A.kaicode, 0) AS kaicode,
ISNULL(A.dennumb, 0) AS dennumb,
CONVERT(char(7), A.denymd, 120) AS denymdx,
ISNULL(A.ukecode, 0) AS autcode,
ISNULL(A.ukecode, 0) AS ukecode,
ISNULL(A.ukename, '') AS ukename,
CAST(CONVERT(char(10), A.denymd, 120) AS datetime) AS denymd,
ISNULL(c.kmkkubn, 0) AS kmkkubn,
ISNULL(A.gyou, 0) AS gyou,
ISNULL(A.kmkcode, 0) AS kmkcode,
ISNULL(A.hojcode, 0) AS hojcode,
ISNULL(A.bumcode2, 0) AS bumcode2,
ISNULL(A.kmkcode2, 0) AS kmkcode2,
ISNULL(A.hojcode2, 0) AS hojcode2,
ISNULL(A.tekname, '') AS tekname,
ISNULL(B.kainame, '') AS kainame,
B.kessans,
B.kessane,
ISNULL((SELECT ISNULL(SUM(zenzan), 0) FROM kekbhojzan
WHERE (kaicode = A.kaicode) AND
(kmkcode = A.kmkcode) AND
(hojcode = A.hojcode) AND
(bumcode = A.bumcode) AND
(CONVERT(char(7), kymd, 120) = CONVERT(char(7), A.denymd, 120))), 0)
AS zenzanb,
ISNULL((SELECT ISNULL(SUM(zenzan), 0) FROM kekhojzan
WHERE (kaicode = A.kaicode) AND
(kmkcode = A.kmkcode) AND
(hojcode = A.hojcode) AND
(CONVERT(char(7), kymd,120) = CONVERT(char(7), A.denymd, 120))), 0) AS zenzan, ISNULL(A.bumcode, 0)
AS bumcode,
ISNULL((SELECT ISNULL(money1, 0) FROM keksiwdat
WHERE (taikubn = 1) AND
(kaicode = A.kaicode) AND
(dennumb = A.dennumb) AND
(denymd = A.denymd) AND
(gyou = A.gyou)), 0)
AS money1,
ISNULL((SELECT ISNULL(money1, 0) FROM keksiwdat
WHERE (taikubn = 2) AND
(kaicode = A.kaicode) AND
(dennumb = A.dennumb) AND
(denymd = A.denymd) AND
(gyou = A.gyou)), 0)
AS money2,
ISNULL((SELECT ISNULL(bumname, '') FROM kekbummst
WHERE (kaicode = A.kaicode) AND
(bumcode = A.bumcode)), '')
AS bumname,
ISNULL((SELECT ISNULL(bumname, '') FROM kekbummst
WHERE (kaicode = A.kaicode) AND
(bumcode = A.bumcode2)), '')
AS bumname2,
ISNULL((SELECT ISNULL(hojname, '') FROM kekhojmst
WHERE (kaicode = A.kaicode) AND
(kmkcode = A.kmkcode) AND