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
)
) 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