SELECT
TS."Resource", TS."Status", TS."Start", TS."Resource Dept.",
SUM(CASE WHEN TS.Day < ts."Start" THEN 'IsNull' END) Mon,
SUM(CASE WHEN TS.Day < ts."Start" THEN 'IsNull' END) Tue,
SUM(CASE WHEN TS.Day < ts."Start" THEN 'IsNull' END) Wed,
SUM(CASE WHEN TS.Day=TS."Start" THEN TS.Hours END) Thu,
SUM(CASE WHEN TS.Day=TS."Start"+1 THEN TS.Hours END) Fri,
SUM(CASE WHEN TS.Day=TS."Start"+2 THEN TS.Hours END) Sat,
SUM(CASE WHEN TS.Day=TS."Start"+3 THEN TS.Hours END) Sun,
SUM(TS.Hours) "Weekly Total",
Case When Sum(TS.Hours)>37 Then Sum(TS.Hours)-37 Else Null End "Week ST/OT"

FROM
(SELECT
pr.name Project, pr.unique_name ProjectID, r.full_name "Resource", p.slice Hours, p.slice_date Day, 
tp.prstart "Start", pre.PRCATEGORY "Resource Dept.", pre.PRUSERTEXT2 "Status"
FROM
Niku.PRTASK t, Niku.PRAssignment a, Niku.prj_blb_slices p, Niku.srm_resources r, Niku.srm_projects pr,
Niku.prresource pre, niku.prtimesheet tst, niku.prtimeperiod tp

WHERE
a.prtaskid=t.prid AND
r.id=a.prresourceid AND p.prj_object_ID=a.prid AND t.PRPROJECTID=pr.id
And r.id=pre.PRID
AND r.id=tst.prresourceid And tst.prtimeperiodid=tp.prid
AND pre.prExternalID='&ResourceID'
AND tst.prstatus=4
AND p.slice_request_id=2
AND p.slice_date Between '01-JAN-10' And '03-JAN-10'
AND tp.prstart Between '01-JAN-10' and '03-JAN-10') TS

GROUP BY TS."Resource", TS."Status", TS."Start", TS."Resource Dept."

UNION

SELECT
TS."Resource", TS."Status", TS."Start", TS."Resource Dept.",
SUM(CASE WHEN TS.Day=TS."Start" THEN TS.Hours END) Mon,
SUM(CASE WHEN TS.Day=TS."Start"+1 THEN TS.Hours END) Tue,
SUM(CASE WHEN TS.Day=TS."Start"+2 THEN TS.Hours END) Wed,
SUM(CASE WHEN TS.Day=TS."Start"+3 THEN TS.Hours END) Thu,
SUM(CASE WHEN TS.Day=TS."Start"+4 THEN TS.Hours END) Fri,
SUM(CASE WHEN TS.Day=TS."Start"+5 THEN TS.Hours END) Sat,
SUM(CASE WHEN TS.Day=TS."Start"+6 THEN TS.Hours END) Sun,
SUM(TS.Hours) "Weekly Total",
Case When Sum(TS.Hours)>37 Then Sum(TS.Hours)-37 Else Null End "Week ST/OT"

FROM
(SELECT
pr.name Project, pr.unique_name ProjectID, r.full_name "Resource", p.slice Hours, p.slice_date Day, 
tp.prstart "Start", pre.PRCATEGORY "Resource Dept.", pre.PRUSERTEXT2 "Status"
FROM
Niku.PRTASK t, Niku.PRAssignment a, Niku.prj_blb_slices p, Niku.srm_resources r, Niku.srm_projects pr,
Niku.prresource pre, niku.prtimesheet tst, niku.prtimeperiod tp

WHERE
a.prtaskid=t.prid AND
r.id=a.prresourceid AND p.prj_object_ID=a.prid AND t.PRPROJECTID=pr.id
And r.id=pre.PRID
AND r.id=tst.prresourceid And tst.prtimeperiodid=tp.prid
AND pre.prExternalID='&ResourceID'
AND tst.prstatus=4
AND p.slice_request_id=2
AND tp.prstart > '03-JAN-10'
AND p.slice_date between tp.prstart and tp.prstart+6) TS

GROUP BY TS."Resource", TS."Status", TS."Start", TS."Resource Dept."

MINUS

SELECT
TS."Resource", TS."Status", TS."Start", TS."Resource Dept.",
SUM(CASE WHEN TS.Day < ts."Start" THEN 'IsNull' END) Mon,
SUM(CASE WHEN TS.Day < ts."Start" THEN 'IsNull' END) Tue,
SUM(CASE WHEN TS.Day < ts."Start" THEN 'IsNull' END) Wed,
SUM(CASE WHEN TS.Day=TS."Start" THEN TS.Hours END) Thu,
SUM(CASE WHEN TS.Day=TS."Start"+1 THEN TS.Hours END) Fri,
SUM(CASE WHEN TS.Day=TS."Start"+2 THEN TS.Hours END) Sat,
SUM(CASE WHEN TS.Day=TS."Start"+3 THEN TS.Hours END) Sun,
SUM(TS.Hours) "Weekly Total",
Case When Sum(TS.Hours)>37 Then Sum(TS.Hours)-37 Else Null End "Week ST/OT"

FROM
(SELECT
pr.name Project, pr.unique_name ProjectID, r.full_name "Resource", p.slice Hours, p.slice_date Day, 
tp.prstart "Start", pre.PRCATEGORY "Resource Dept.", pre.PRUSERTEXT2 "Status"
FROM
Niku.PRTASK t, Niku.PRAssignment a, Niku.prj_blb_slices p, Niku.srm_resources r, Niku.srm_projects pr,
Niku.prresource pre, niku.prtimesheet tst, niku.prtimeperiod tp

WHERE
a.prtaskid=t.prid AND
r.id=a.prresourceid AND p.prj_object_ID=a.prid AND t.PRPROJECTID=pr.id
And r.id=pre.PRID
AND r.id=tst.prresourceid And tst.prtimeperiodid=tp.prid
AND pre.prExternalID='&ResourceID'
AND tst.prstatus= ALL (4, 5)
AND p.slice_request_id=2
AND p.slice_date Between '01-JAN-10' And '03-JAN-10'
AND tp.prstart Between '01-JAN-10' and '03-JAN-10') TS

GROUP BY TS."Resource", TS."Status", TS."Start", TS."Resource Dept."

UNION

SELECT
TS."Resource", TS."Status", TS."Start", TS."Resource Dept.",
SUM(CASE WHEN TS.Day=TS."Start" THEN TS.Hours END) Mon,
SUM(CASE WHEN TS.Day=TS."Start"+1 THEN TS.Hours END) Tue,
SUM(CASE WHEN TS.Day=TS."Start"+2 THEN TS.Hours END) Wed,
SUM(CASE WHEN TS.Day=TS."Start"+3 THEN TS.Hours END) Thu,
SUM(CASE WHEN TS.Day=TS."Start"+4 THEN TS.Hours END) Fri,
SUM(CASE WHEN TS.Day=TS."Start"+5 THEN TS.Hours END) Sat,
SUM(CASE WHEN TS.Day=TS."Start"+6 THEN TS.Hours END) Sun,
SUM(TS.Hours) "Weekly Total",
Case When Sum(TS.Hours)>37 Then Sum(TS.Hours)-37 Else Null End "Week ST/OT"

FROM
(SELECT
pr.name Project, pr.unique_name ProjectID, r.full_name "Resource", p.slice Hours, p.slice_date Day, 
tp.prstart "Start", pre.PRCATEGORY "Resource Dept.", pre.PRUSERTEXT2 "Status"
FROM
Niku.PRTASK t, Niku.PRAssignment a, Niku.prj_blb_slices p, Niku.srm_resources r, Niku.srm_projects pr,
Niku.prresource pre, niku.prtimesheet tst, niku.prtimeperiod tp

WHERE
a.prtaskid=t.prid AND
r.id=a.prresourceid AND p.prj_object_ID=a.prid AND t.PRPROJECTID=pr.id
And r.id=pre.PRID
AND r.id=tst.prresourceid And tst.prtimeperiodid=tp.prid
AND pre.prExternalID='&ResourceID'
AND tst.prstatus= ALL (4, 5)
AND p.slice_request_id=2
AND tp.prstart > '03-JAN-10'
AND p.slice_date between tp.prstart and tp.prstart+6) TS

GROUP BY TS."Resource", TS."Status", TS."Start", TS."Resource Dept."