SELECT manager As eacgroup, ca1, ca2, ca3, wp, descrip, CONCAT('''',a.cecode) AS cecode, cedesc, result, Date, direct, pmt, program, statusdate

 

FROM

 

(

 

       (

 

              SELECT manager, d.*, Convert(VarChar(10), p.statusdate, 120) AS statusdate from cawp, program p,

 

              (

 

                     SELECT program, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, Date, SUM(direct) AS direct

 

                     FROM

 

                     (

 

                           SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, direct, df_date, Convert(VarChar(10), MIN(fsc_date), 120) AS Date

 

                           FROM

                           (

 

                                  SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, df_date, SUM(Hours) AS direct

 

                                  FROM

 

                                  (

 

                                         SELECT cawp.program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, 'Hours' AS result, Hours, df_date

 

                                         FROM tphase, cawp

 

                                         WHERE cawp.program = tphase.program

 

                                         AND cawp.cawpid = tphase.cawpid

 

                                         AND cawp.program = 'COBRAPROGRAMNAME’                                      

 

                                         AND tphase.program = 'COBRAPROGRAMNAME’

                                        

                                         AND class IN

 

                                         (

                                                       SELECT class

 

                                                       FROM costrepclass

 

                                                       WHERE program = 'COBRAPROGRAMNAME'

 

                                                       AND cost = 'EAC'

                                         )

 

                                         AND Hours <> 0

                                  )

 

                                  GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, df_date

 

                           ) tc

 

                           LEFT OUTER JOIN

 

                           (

 

                                  SELECT fsc_date

 

                                  FROM fiscdetl

 

                                  WHERE fiscfile =

 

                                  (

 

                                         SELECT fisc_file

 

                                         FROM program

 

                                         WHERE program = 'COBRAPROGRAMNAME'

 

                                  )

 

                           ) cal

 

                           ON tc.df_date <= cal.fsc_date

 

                           GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, direct, df_date

 

                     ) newd

 

                     GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, newd.cecode, result, Date

 

              ) d

 

              WHERE d.program = p.program

         

 

              AND d.program = cawp.program

         

              AND cawp.ca1 = d.ca1

 

              AND cawp.ca2 = d.ca2

 

              AND cawp.ca3 = d.ca3

 

              AND cawp.wp = ‘’

 

              AND cawp.program = 'COBRAPROGRAMNAME’          

 

              GROUP BY manager, p.statusdate, d.program, d.ca1, d.ca2, d.ca3, d.wp, d.pmt, d.descrip, d.cecode, result, Date, direct

 

       )

 

       UNION ALL

 

       (

              SELECT manager, d.*, Convert(VarChar(10), p.statusdate, 120) AS statusdate from cawp, program p,

 

              (

 

                     SELECT program, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, Date, SUM(direct) AS direct

 

                     FROM

 

                     (

 

                           SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, direct, df_date, Convert(VarChar(10), MIN(fsc_date) AS Date

 

                           FROM

                           (

 

                                  SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, df_date, SUM(ODC) AS direct

 

                                  FROM

 

                                  (

 

                                         SELECT cawp.program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, 'ODC' AS result, ODC, df_date

 

                                         FROM tphase, cawp

 

                                         WHERE cawp.program = tphase.program

 

                                         AND cawp.cawpid = tphase.cawpid

 

                                         AND cawp.program = 'COBRAPROGRAMNAME’                                      

 

                                         AND tphase.program = 'COBRAPROGRAMNAME’

                                        

                                         AND class IN

 

                                         (

                                                       SELECT class

 

                                                       FROM costrepclass

 

                                                       WHERE program = 'COBRAPROGRAMNAME'

 

                                                       AND cost = 'EAC'

                                         )

 

                                         AND ODC <> 0

                                  )

 

                                  GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, df_date

 

                           ) tc

 

                           LEFT OUTER JOIN

 

                           (

 

                                  SELECT fsc_date

 

                                  FROM fiscdetl

 

                                  WHERE fiscfile =

 

                                  (

 

                                         SELECT fisc_file

 

                                         FROM program

 

                                         WHERE program = 'COBRAPROGRAMNAME'

 

                                  )

 

                           ) cal

 

                           ON tc.df_date <= cal.fsc_date

 

                           GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, direct, df_date

 

                     ) newd

 

                     GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, newd.cecode, result, Date

 

              ) d

 

              WHERE d.program = p.program

         

 

              AND d.program = cawp.program

         

              AND cawp.ca1 = d.ca1

 

              AND cawp.ca2 = d.ca2

 

              AND cawp.ca3 = d.ca3

 

              AND cawp.wp = ‘’

 

              AND cawp.program = 'COBRAPROGRAMNAME’          

 

              GROUP BY manager, p.statusdate, d.program, d.ca1, d.ca2, d.ca3, d.wp, d.pmt, d.descrip, d.cecode, result, Date, direct

       )

 

UNION ALL

 

       (

              SELECT manager, d.*, Convert(VarChar(10), p.statusdate, 120) AS statusdate from cawp, program p,

 

              (

 

                     SELECT program, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, Date, SUM(direct) AS direct

 

                     FROM

 

                     (

 

                           SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, direct, df_date, Convert(VarChar(10), MIN(fsc_date) AS Date

 

                           FROM

                           (

 

                                  SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, df_date, SUM(MATERIAL) AS direct

 

                                  FROM

 

                                  (

 

                                         SELECT cawp.program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, 'MATERIAL' AS result, MATERIAL, df_date

 

                                         FROM tphase, cawp

 

                                         WHERE cawp.program = tphase.program

 

                                         AND cawp.cawpid = tphase.cawpid

 

                                         AND cawp.program = 'COBRAPROGRAMNAME’                                      

 

                                         AND tphase.program = 'COBRAPROGRAMNAME’

                                        

                                         AND class IN

 

                                         (

                                                       SELECT class

 

                                                       FROM costrepclass

 

                                                       WHERE program = 'COBRAPROGRAMNAME'

 

                                                       AND cost = 'EAC'

                                         )

 

                                         AND MATERIAL <> 0

                                  )

 

                                  GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, df_date

 

                           ) tc

 

                           LEFT OUTER JOIN

 

                           (

 

                                  SELECT fsc_date

 

                                  FROM fiscdetl

 

                                  WHERE fiscfile =

 

                                  (

 

                                         SELECT fisc_file

 

                                         FROM program

 

                                         WHERE program = 'COBRAPROGRAMNAME'

 

                                  )

 

                           ) cal

 

                           ON tc.df_date <= cal.fsc_date

 

                           GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, direct, df_date

 

                     ) newd

 

                     GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, newd.cecode, result, Date

 

              ) d

 

              WHERE d.program = p.program

         

 

              AND d.program = cawp.program

         

              AND cawp.ca1 = d.ca1

 

              AND cawp.ca2 = d.ca2

 

              AND cawp.ca3 = d.ca3

 

              AND cawp.wp = ‘’

 

              AND cawp.program = 'COBRAPROGRAMNAME’          

 

              GROUP BY manager, p.statusdate, d.program, d.ca1, d.ca2, d.ca3, d.wp, d.pmt, d.descrip, d.cecode, result, Date, direct

 

       )

 

       UNION ALL

 

       (

              SELECT manager, d.*, Convert(VarChar(10), p.statusdate, 120) AS statusdate from cawp, program p,

 

              (

 

                     SELECT program, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, Date, SUM(direct) AS direct

 

                     FROM

 

                     (

 

                           SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, direct, df_date, Convert(VarChar(10), MIN(fsc_date) AS Date

 

                           FROM

                           (

 

                                  SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, df_date, SUM(SUBCONT) AS direct

 

                                  FROM

 

                                  (

 

                                         SELECT cawp.program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, 'SUBCONT' AS result, SUBCONT, df_date

 

                                         FROM tphase, cawp

 

                                         WHERE cawp.program = tphase.program

 

                                         AND cawp.cawpid = tphase.cawpid

 

                                         AND cawp.program = 'COBRAPROGRAMNAME’                                      

 

                                         AND tphase.program = 'COBRAPROGRAMNAME’

                                        

                                         AND class IN

 

                                         (

                                                       SELECT class

 

                                                       FROM costrepclass

 

                                                       WHERE program = 'COBRAPROGRAMNAME'

 

                                                       AND cost = 'EAC'

                                         )

 

                                         AND SUBCONT <> 0

                                  )

 

                                  GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, df_date

 

                           ) tc

 

                           LEFT OUTER JOIN

 

                           (

 

                                  SELECT fsc_date

 

                                  FROM fiscdetl

 

                                  WHERE fiscfile =

 

                                  (

 

                                         SELECT fisc_file

 

                                         FROM program

 

                                         WHERE program = 'COBRAPROGRAMNAME'

 

                                  )

 

                           ) cal

 

                           ON tc.df_date <= cal.fsc_date

 

                           GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, direct, df_date

 

                     ) newd

 

                     GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, newd.cecode, result, Date

 

              ) d

 

              WHERE d.program = p.program

         

 

              AND d.program = cawp.program

         

              AND cawp.ca1 = d.ca1

 

              AND cawp.ca2 = d.ca2

 

              AND cawp.ca3 = d.ca3

 

              AND cawp.wp = ‘’

 

              AND cawp.program = 'COBRAPROGRAMNAME’

 

              GROUP BY manager, p.statusdate, d.program, d.ca1, d.ca2, d.ca3, d.wp, d.pmt, d.descrip, d.cecode, result, Date, direct

 

       )

 

       UNION ALL

 

       (

              SELECT manager, d.*, Convert(VarChar(10), p.statusdate, 120) AS statusdate from cawp, program p,

 

              (

 

                     SELECT program, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, Date, SUM(direct) AS direct

 

                     FROM

 

                     (

 

                           SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, direct, df_date, TO_CHAR(MIN(fsc_date), 'YYYY-MM-DD') AS Date

 

                           FROM

                            (

 

                                  SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, df_date, SUM(IWO) AS direct

 

                                  FROM

 

                                  (

 

                                         SELECT cawp.program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, 'IWO' AS result, IWO, df_date

 

                                         FROM tphase, cawp

 

                                         WHERE cawp.program = tphase.program

 

                                         AND cawp.cawpid = tphase.cawpid

 

                                         AND cawp.program = 'COBRAPROGRAMNAME’                                      

 

                                         AND tphase.program = 'COBRAPROGRAMNAME’

                                        

                                         AND class IN

 

                                         (

                                                       SELECT class

 

                                                       FROM costrepclass

 

                                                       WHERE program = 'COBRAPROGRAMNAME'

 

                                                       AND cost = 'EAC'

                                         )

 

                                         AND IWO <> 0

                                  )

 

                                  GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, df_date

 

                           ) tc

 

                           LEFT OUTER JOIN

 

                           (

 

                                  SELECT fsc_date

 

                                  FROM fiscdetl

 

                                  WHERE fiscfile =

 

                                  (

 

                                         SELECT fisc_file

 

                                         FROM program

 

                                         WHERE program = 'COBRAPROGRAMNAME'

 

                                  )

 

                           ) cal

 

                           ON tc.df_date <= cal.fsc_date

 

                           GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, result, direct, df_date

 

                     ) newd

 

                     GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, newd.cecode, result, Date

 

              ) d

 

              WHERE d.program = p.program

         

 

              AND d.program = cawp.program

         

              AND cawp.ca1 = d.ca1

 

              AND cawp.ca2 = d.ca2

 

              AND cawp.ca3 = d.ca3

 

              AND cawp.wp = ‘’

 

              AND cawp.program = 'COBRAPROGRAMNAME’

 

              GROUP BY manager, p.statusdate, d.program, d.ca1, d.ca2, d.ca3, d.wp, d.pmt, d.descrip, d.cecode, result, Date, direct

 

       )

 

 

) 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

 

WHERE a.cecode NOT IN ('0')

 

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