June 15, 2012 at 2:15 pm
SELECT to_char(LP.F_WK_END_DT, 'WW') AS Wk_Reported
, LP.F_EMPLID
, LP.F_RESOURCE_NAME
, T_DM_HUMAN_RESOURCES.F_RESOURCE_MGR_NAME
, LP.F_WK_END_DT
, LP.F_CCSI_WORK_HRS
FROM
DW.T_DM_LABOR_PAYTIME LP
INNER JOIN
DW.T_DM_HUMAN_RESOURCES T_DM_HUMAN_RESOURCES
ON (LP.F_EMPLID = T_DM_HUMAN_RESOURCES.F_RESOURCE_ID)
What I am trying to do is group by EMPLOYEE ID and Wk_Reported to return a SUM of LP.F_CCSI_WORK_HRS, but I have been running into problems.
When I tried to use subselect, I was getting an ORA group by error.
Could someone point me to a page or resource that would help me work through this problem? I'd really appreciate any help.
June 15, 2012 at 4:23 pm
Grouped by LP.F_EMPLID and Wk_Reported
select mysummedhours.F_EMPLID, mysummedhours.Wk_Reported,summedworkhours
from
(
SELECT LP.F_EMPLID,to_char(LP.F_WK_END_DT, 'WW') Wk_Reported ,SUM( LP.F_CCSI_WORK_HRS) summedworkhours
from DW.T_DM_LABOR_PAYTIME LP
WHERE EXISTS (SELECT 1 FROM DW.T_DM_HUMAN_RESOURCES HR WHERE LP.F_EMPLID=T_DM_HUMAN_RESOURCES.F_RESOURCE_ID)
GROUP BY LP.F_EMPLID,to_char(LP.F_WK_END_DT, 'WW')
) mysummedhours
June 18, 2012 at 11:31 am
Thank you very much.
I just tested this, and it gives exactly the results that I need.
Now I get to study what you did and be a little more dangerous going forward 🙂
June 18, 2012 at 3:08 pm
Do you know if there is a way to bring the T_DM_HUMAN_RESOURCES.F_RESOURCE_MGR_NAME
field back into this? I've tried but keep receiving a grouping error.
June 18, 2012 at 5:14 pm
Answer: join mysummedhours with T_DM_HUMAN_RESOURCES
select mysummedhours.F_EMPLID, mysummedhours.Wk_Reported,summedworkhours
,T_DM_HUMAN_RESOURCES.F_RESOURCE_MGR_NAME
from
(
SELECT LP.F_EMPLID,to_char(LP.F_WK_END_DT, 'WW') Wk_Reported ,SUM( LP.F_CCSI_WORK_HRS) summedworkhours
from DW.T_DM_LABOR_PAYTIME LP
WHERE EXISTS (SELECT 1 FROM DW.T_DM_HUMAN_RESOURCES HR WHERE LP.F_EMPLID=T_DM_HUMAN_RESOURCES.F_RESOURCE_ID)
GROUP BY LP.F_EMPLID,to_char(LP.F_WK_END_DT, 'WW')
) mysummedhours
INNER JOIN
DW.T_DM_HUMAN_RESOURCES T_DM_HUMAN_RESOURCES
ON (mysummedhours.F_EMPLID = T_DM_HUMAN_RESOURCES.F_RESOURCE_ID)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply