SELECT CONCAT(CONCAT(program, '-'), CAManager) AS eacgroup, ca1, ca2, ca3, wp, descrip, CONCAT('''', a.cecode) AS apcecode, cedesc, res, FiscalDate, direct, pmt, StatusDate
FROM
(
SELECT program, CAManager, WPManager, ca1, ca2, ca3, wp, pmt, descrip, cecode, res, FiscalDate, StatusDate, SUM(direct) AS direct
FROM
(
SELECT tc.program, CAManager, WPManager, tc.ca1, tc.ca2, tc.ca3, tc.wp, tc.pmt, tc.descrip, tc.cecode, tc.res, tc.direct, tc.TphaseDate, tc.StatusDate, Convert(VarChar(10), MIN(cal.FiscalDate), 120) AS FiscalDate
FROM
(
SELECT FirstGroup.program, CAManager, WPManager, FirstGroup.ca1, FirstGroup.ca2, FirstGroup.ca3, FirstGroup.wp, FirstGroup.pmt, FirstGroup.descrip, FirstGroup.cecode, FirstGroup.res, FirstGroup.TphaseDate, FirstGroup.StatusDate, SUM(FirstGroup.direct) AS direct
FROM
(
SELECT DirectTP.program, CAManager, WPManager, DirectTP.ca1, DirectTP.ca2, DirectTP.ca3, DirectTP.wp, DirectTP.pmt, DirectTP.descrip, DirectTP.cecode, DirectTP.res, DirectTP.direct, DirectTP.TphaseDate, Convert(VarChar(10), p.statusdate, 120) AS StatusDate
FROM
program p,
(
SELECT program, ca1, ca2, ca3, manager AS CAManager
FROM cawp
WHERE wp = ''
AND program IN
(
'COBRAPROGRAMNAME1', 'COBRAPROGRAMNAME2'
)
) CAInfo,
(
SELECT tphase.program, manager AS WPManager, ca1, ca2, ca3, wp, pmt, descrip, cecode,
CASE
WHEN Hours <> 0 THEN 'Hours'
WHEN ODC <> 0 THEN 'ODC'
WHEN MATERIAL <> 0 THEN 'MATERIAL'
WHEN SUBCONT <> 0 THEN 'SUBCONT'
END AS res,
CASE
WHEN Hours <> 0 THEN Hours
WHEN ODC <> 0 THEN ODC
WHEN MATERIAL <> 0 THEN MATERIAL
WHEN SUBCONT <> 0 THEN SUBCONT
END AS direct,
Convert(VarChar(10), df_date, 120) AS TphaseDate
FROM
tphase, cawp
WHERE cawp.program IN
(
'COBRAPROGRAMNAME1',
'COBRAPROGRAMNAME2'
)
AND tphase.program IN
(
'COBRAPROGRAMNAME1', 'COBRAPROGRAMNAME2'
)
AND cawp.program = tphase.program
AND cawp.cawpid = tphase.cawpid
AND class IN
(
SELECT class
FROM costrepclass
WHERE program = 'COBRAPROGRAMNAME1'
AND cost = 'EAC'
)
AND Hours + ODC + MATERIAL + SUBCONT <> 0
AND df_date > '01/01/2019'
AND cecode NOT IN ('0')
AND PMT NOT IN ('J')
) DirectTP
WHERE p.program = DirectTP.program
AND p.program = CAInfo.program
AND DirectTP.program = CAInfo.program
AND DirectTP.ca1 = CAInfo.ca1
AND DirectTP.ca2 = CAInfo.ca2
AND DirectTP.ca3 = CAInfo.ca3
) FirstGroup
GROUP BY FirstGroup.program, CAManager, WPManager, FirstGroup.ca1, FirstGroup.ca2, FirstGroup.ca3, FirstGroup.wp, FirstGroup.pmt, FirstGroup.descrip, FirstGroup.cecode, FirstGroup.res, FirstGroup.TphaseDate, FirstGroup.StatusDate
) tc
LEFT OUTER JOIN
(
SELECT Convert(VarChar(10), fsc_date, 120) AS FiscalDate
FROM fiscdetl
WHERE fiscfile =
(
SELECT fisc_file
FROM program
WHERE program = 'COBRAPROGRAMNAME1'
)
) cal
ON tc.TphaseDate <= cal.FiscalDate
GROUP BY tc.program, CAManager, WPManager, tc.ca1, tc.ca2, tc.ca3, tc.wp, tc.pmt, tc.descrip, tc.cecode, tc.res, tc.direct, tc.TphaseDate, tc.StatusDate
) newd
GROUP BY program, CAManager, WPManager, ca1, ca2, ca3, wp, pmt, descrip, newd.cecode, res, FiscalDate, StatusDate
) a LEFT OUTER JOIN
(
SELECT cecode, cedesc
FROM calcdesc
WHERE calcfile =
(
SELECT calc_file
FROM program
WHERE program = 'COBRAPROGRAMNAME1'
)
) f ON a.cecode = f.cecode
ORDER BY eacgroup, program, ca1, ca2, ca3, wp, descrip, a.cecode, FiscalDate