Advertisement
psi_mmobile

Untitled

Jan 27th, 2023
1,639
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 1.37 KB | None | 0 0
  1. SELECT first_date, last_date, work_code,TO_CHAR(first_date,'Day') "FROM",TO_CHAR(last_date,'Day') "TO",'Sick',SUM(hrs) FROM (SELECT ptd.*  
  2. FROM   pd_trf_day  ptd, work_code wc, person p, vo_person_category vopc
  3. WHERE ptd.work_code_id = wc.work_code_id
  4. AND wc.work_code_std_category_id = 2
  5. AND ptd.person_id = p.person_id
  6. AND p.vo_person_category_id = vopc.vo_person_category_id
  7. AND TO_CHAR(ptd.date_day, 'DY','NLS_DATE_LANGUAGE=AMERICAN') NOT IN ('SAT', 'SUN') -- not sure if weekends should be excluded since there are records for them
  8. AND (:person_id IS NULL OR ptd.person_id = :person_id) -- person_id param
  9. AND (:vehicle_owner_id IS NULL OR vopc.vehicle_owner_id = :vehicle_owner_id) -- vehicle_owner_id param
  10. AND (:vo_person_category_id IS NULL OR vopc.vo_person_category_id = :vo_person_category_id) -- vo_person_category_id param
  11. AND (:from_date IS NULL OR ptd.date_day >= :from_date) -- from_date param
  12. AND (:TO_DATE IS NULL OR ptd.date_day <= :TO_DATE) -- to_date param
  13. )
  14. match_recognize (
  15.   ORDER BY date_day
  16.   measures  
  17.     FIRST(date_day) AS first_date,
  18.     LAST(date_day) AS last_date,
  19.     NVL(trf_dhr,0) AS hrs,
  20.     COUNT(*) AS days,
  21.     work_code_id AS work_code,
  22.     match_number() AS grp
  23.   pattern ( strt consecutive* )
  24.   define  
  25.     consecutive AS date_day = ( prev ( date_day ) + 1 )
  26. )
  27. GROUP BY first_date,last_date,work_code
  28. ORDER BY first_date;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement