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

                                  (

                                         SELECT subprogram

                                         FROM multprog, program

                                         WHERE master = 'COBRAMASTERNAME'

                                         AND program.program = multprog.subprogram

                                  )                         

                           ) 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

                                  (

                                         SELECT subprogram

                                         FROM multprog, program

                                         WHERE master = 'COBRAMASTERNAME'

                                         AND program.program = multprog.subprogram

                                  )

                                  AND tphase.program IN

                                  (

                                         SELECT subprogram

                                         FROM multprog, program

                                         WHERE master = 'COBRAMASTERNAME'

                                         AND program.program = multprog.subprogram

                                  )

                                 

                                  AND cawp.program = tphase.program

                                  AND cawp.cawpid = tphase.cawpid

                                 

                                  AND class IN

                                  (

                                         SELECT class

                                         FROM costrepclass

                                         WHERE program = 'COBRAMASTERNAME'

                                         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 = 'COBRAMASTERNAME'

                     )

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

       )

) f ON a.cecode = f.cecode

 

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