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

(

'COBRAPROGRAMNAME'

)

) CAInfo,

(

SELECT tphase.program, manager AS WPManager, ca1, ca2, ca3, wp, pmt, descrip, cecode,

CASE

WHEN HOURS <> 0 THEN 'HOURS'

WHEN DIRECT <> 0 AND HOURS = 0 THEN 'DIRECT'

END AS res,

CASE

WHEN HOURS <> 0 THEN HOURS

WHEN DIRECT <> 0 AND HOURS = 0 THEN DIRECT

END AS direct,

Convert(VarChar(10), df_date, 120) AS TphaseDate

FROM

                                  tphase, cawp

WHERE cawp.program IN

(

'COBRAPROGRAMNAME'

)

AND tphase.program IN

(

'COBRAPROGRAMNAME'

)

AND cawp.program = tphase.program

AND cawp.cawpid = tphase.cawpid

AND class IN

(

SELECT class

FROM costrepclass

WHERE program = 'COBRAPROGRAMNAME'

AND cost = 'EAC'

)

AND HOURS + DIRECT <> 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 = 'COBRAPROGRAMNAME'

)

) 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 = 'COBRAPROGRAMNAME'

)

) f ON a.cecode = f.cecode

ORDER BY eacgroup, program, ca1, ca2, ca3, wp, descrip, a.cecode, FiscalDate