Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- WITH hr AS
- (SELECT pd.person_id,
- pd.date_day,
- pd.is_locked,
- SUM(CASE WHEN wc.work_code_std_id = 110 THEN pd.TRF_DHR ELSE 0 END) HR_DAY,
- SUM(CASE WHEN wc.work_code_std_id = 114 THEN pd.TRF_DHR ELSE 0 END) HR_NIGHT,
- wc.short_code,
- pd.ref_number,
- listagg (pd.user_comment, '/' ) user_comment
- FROM pd_trf_day pd,
- pd_day pdd,
- work_code wc
- WHERE pd.person_id = pdd.person_id
- AND pd.date_day = pdd.date_day
- AND pd.work_code_id = wc.work_code_id
- AND pd.TRF_DHR > 0
- AND wc.work_code_std_id IN (110,114)
- GROUP BY pd.person_id,
- pd.date_day,
- pd.is_locked,
- wc.short_code,
- pd.ref_number)
- SELECT hr.person_id,
- hr.date_day,
- hr.is_locked,
- hr.ref_number,
- p.company_nr,
- p.last_name,
- p.first_name,
- q.name,
- TO_CHAR(hr.date_day,'dd/MM/yyyy') DATE_STR,
- hr.hr_day,
- hr.hr_night,
- hr.user_comment
- FROM hr, person p,pd_day pdd, qualification q
- WHERE hr.person_id = pdd.person_id
- AND pdd.person_id = p.person_id
- AND hr.date_day = pdd.date_day
- AND p.master_qualification_id = q.qualification_id
- ORDER BY 2,4,5,6,7;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement