Advertisement
psi_mmobile

Untitled

Feb 20th, 2023
1,194
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 1.13 KB | None | 0 0
  1. WITH base_data AS (SELECT PdTrfDay.date_day,PdTrfDay.person_id,PdTrfDay.work_code_id, NVL(PdTrfDay.trf_dhr,0) trf_dhr, WorkCode.WORK_CODE_NAME
  2. FROM pd_trf_day PdTrfDay, work_code WorkCode
  3. WHERE PdTrfDay.work_code_id = WorkCode.work_code_id
  4. AND WorkCode.work_code_std_category_id = 2
  5. --and PdTrfDay.person_id = 471152 and date_day between to_date('13/01/2023 00:00:00', 'DD/MM/YYYY HH24:MI:SS') and to_date('15/01/2023 00:00:00', 'DD/MM/YYYY HH24:MI:SS')
  6. )
  7. SELECT "PERSON_ID","FIRST_DATE","LAST_DATE","SICK_DAYS_IN_A_ROW","SUMMED_HRS","MATCHES", "WORK_CODE_ID","WORK_CODE_NAME", "CL" FROM base_data
  8. match_recognize(
  9. PARTITION BY person_id, work_code_id
  10. ORDER BY date_day
  11. measures
  12. NVL(FIRST(date_day),date_day) AS first_date,
  13. NVL(LAST(date_day),date_day) AS last_date,
  14. DECODE(COUNT(*),0,1,COUNT(*)) AS sick_days_in_a_row,
  15. NVL(SUM(trf_dhr),trf_dhr) AS summed_hrs,
  16. match_number() AS matches,
  17. work_code_id AS wc_id,
  18. work_code_name AS work_code_name,
  19. classifier() AS CL
  20. pattern (strt consecutive*)
  21. define
  22. consecutive AS date_day = (prev(date_day)+1) AND work_code_id = consecutive.work_code_id
  23. )
  24. ORDER BY person_id,first_date,last_date;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement