API tools faq
paste
Advertisement
psi_mmobile

Untitled

psi_mmobile
Oct 15th, 2024
98
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 7.98 KB | None | 0 0
raw download clone embed print report
  1. SELECT
  2.     'nbr_hr' AS field_name,
  3.     TO_CHAR(MAX(CASE WHEN day_id = 1 THEN nbr_hr END)) AS monday,
  4.     TO_CHAR(MAX(CASE WHEN day_id = 2 THEN nbr_hr END)) AS tuesday,
  5.     TO_CHAR(MAX(CASE WHEN day_id = 3 THEN nbr_hr END)) AS wednesday,
  6.     TO_CHAR(MAX(CASE WHEN day_id = 4 THEN nbr_hr END)) AS thursday,
  7.     TO_CHAR(MAX(CASE WHEN day_id = 5 THEN nbr_hr END)) AS friday,
  8.     TO_CHAR(MAX(CASE WHEN day_id = 6 THEN nbr_hr END)) AS saturday,
  9.     TO_CHAR(MAX(CASE WHEN day_id = 7 THEN nbr_hr END)) AS sunday
  10. FROM working_schedule_day
  11. WHERE working_schedule_id = :working_schedule_id
  12. UNION ALL
  13. SELECT
  14.     'nbr_hr_am' AS field_name,
  15.     TO_CHAR(MAX(CASE WHEN day_id = 1 THEN nbr_hr_am END)) AS monday,
  16.     TO_CHAR(MAX(CASE WHEN day_id = 2 THEN nbr_hr_am END)) AS tuesday,
  17.     TO_CHAR(MAX(CASE WHEN day_id = 3 THEN nbr_hr_am END)) AS wednesday,
  18.     TO_CHAR(MAX(CASE WHEN day_id = 4 THEN nbr_hr_am END)) AS thursday,
  19.     TO_CHAR(MAX(CASE WHEN day_id = 5 THEN nbr_hr_am END)) AS friday,
  20.     TO_CHAR(MAX(CASE WHEN day_id = 6 THEN nbr_hr_am END)) AS saturday,
  21.     TO_CHAR(MAX(CASE WHEN day_id = 7 THEN nbr_hr_am END)) AS sunday
  22. FROM working_schedule_day
  23. WHERE working_schedule_id = :working_schedule_id
  24. UNION ALL
  25. SELECT
  26.     'nbr_hr_am' AS field_name,
  27.     TO_CHAR(MAX(CASE WHEN day_id = 1 THEN nbr_hr_am END)) AS monday,
  28.     TO_CHAR(MAX(CASE WHEN day_id = 2 THEN nbr_hr_am END)) AS tuesday,
  29.     TO_CHAR(MAX(CASE WHEN day_id = 3 THEN nbr_hr_am END)) AS wednesday,
  30.     TO_CHAR(MAX(CASE WHEN day_id = 4 THEN nbr_hr_am END)) AS thursday,
  31.     TO_CHAR(MAX(CASE WHEN day_id = 5 THEN nbr_hr_am END)) AS friday,
  32.     TO_CHAR(MAX(CASE WHEN day_id = 6 THEN nbr_hr_am END)) AS saturday,
  33.     TO_CHAR(MAX(CASE WHEN day_id = 7 THEN nbr_hr_am END)) AS sunday
  34. FROM working_schedule_day
  35. WHERE working_schedule_id = :working_schedule_id
  36. UNION ALL
  37. SELECT
  38.     'nbr_hr_for_absence' AS field_name,
  39.     TO_CHAR(MAX(CASE WHEN day_id = 1 THEN nbr_hr_for_absence END)) AS monday,
  40.     TO_CHAR(MAX(CASE WHEN day_id = 2 THEN nbr_hr_for_absence END)) AS tuesday,
  41.     TO_CHAR(MAX(CASE WHEN day_id = 3 THEN nbr_hr_for_absence END)) AS wednesday,
  42.     TO_CHAR(MAX(CASE WHEN day_id = 4 THEN nbr_hr_for_absence END)) AS thursday,
  43.     TO_CHAR(MAX(CASE WHEN day_id = 5 THEN nbr_hr_for_absence END)) AS friday,
  44.     TO_CHAR(MAX(CASE WHEN day_id = 6 THEN nbr_hr_for_absence END)) AS saturday,
  45.     TO_CHAR(MAX(CASE WHEN day_id = 7 THEN nbr_hr_for_absence END)) AS sunday
  46. FROM working_schedule_day
  47. WHERE working_schedule_id = :working_schedule_id
  48. UNION ALL
  49. SELECT
  50.     'ph_mobility' AS field_name,
  51.     TO_CHAR(MAX(CASE WHEN day_id = 1 THEN ph_mobility END)) AS monday,
  52.     TO_CHAR(MAX(CASE WHEN day_id = 2 THEN ph_mobility END)) AS tuesday,
  53.     TO_CHAR(MAX(CASE WHEN day_id = 3 THEN ph_mobility END)) AS wednesday,
  54.     TO_CHAR(MAX(CASE WHEN day_id = 4 THEN ph_mobility END)) AS thursday,
  55.     TO_CHAR(MAX(CASE WHEN day_id = 5 THEN ph_mobility END)) AS friday,
  56.     TO_CHAR(MAX(CASE WHEN day_id = 6 THEN ph_mobility END)) AS saturday,
  57.     TO_CHAR(MAX(CASE WHEN day_id = 7 THEN ph_mobility END)) AS sunday
  58. FROM working_schedule_day
  59. WHERE working_schedule_id = :working_schedule_id
  60. UNION ALL
  61. SELECT
  62.     'hp_mobility' AS field_name,
  63.     TO_CHAR(MAX(CASE WHEN day_id = 1 THEN hp_mobility END)) AS monday,
  64.     TO_CHAR(MAX(CASE WHEN day_id = 2 THEN hp_mobility END)) AS tuesday,
  65.     TO_CHAR(MAX(CASE WHEN day_id = 3 THEN hp_mobility END)) AS wednesday,
  66.     TO_CHAR(MAX(CASE WHEN day_id = 4 THEN hp_mobility END)) AS thursday,
  67.     TO_CHAR(MAX(CASE WHEN day_id = 5 THEN hp_mobility END)) AS friday,
  68.     TO_CHAR(MAX(CASE WHEN day_id = 6 THEN hp_mobility END)) AS saturday,
  69.     TO_CHAR(MAX(CASE WHEN day_id = 7 THEN hp_mobility END)) AS sunday
  70. FROM working_schedule_day
  71. WHERE working_schedule_id = :working_schedule_id
  72. UNION ALL
  73. SELECT
  74.     'start_working_time' AS field_name,
  75.     TO_CHAR(MAX(CASE WHEN day_id = 1 THEN start_working_time END), 'HH24:MI') AS monday,
  76.     TO_CHAR(MAX(CASE WHEN day_id = 2 THEN start_working_time END), 'HH24:MI') AS tuesday,
  77.     TO_CHAR(MAX(CASE WHEN day_id = 3 THEN start_working_time END), 'HH24:MI') AS wednesday,
  78.     TO_CHAR(MAX(CASE WHEN day_id = 4 THEN start_working_time END), 'HH24:MI') AS thursday,
  79.     TO_CHAR(MAX(CASE WHEN day_id = 5 THEN start_working_time END), 'HH24:MI') AS friday,
  80.     TO_CHAR(MAX(CASE WHEN day_id = 6 THEN start_working_time END), 'HH24:MI') AS saturday,
  81.     TO_CHAR(MAX(CASE WHEN day_id = 7 THEN start_working_time END), 'HH24:MI') AS sunday
  82. FROM working_schedule_day
  83. WHERE working_schedule_id = :working_schedule_id
  84. UNION ALL
  85. SELECT
  86.     'stop_working_time' AS field_name,
  87.     TO_CHAR(MAX(CASE WHEN day_id = 1 THEN stop_working_time END), 'HH24:MI') AS monday,
  88.     TO_CHAR(MAX(CASE WHEN day_id = 2 THEN stop_working_time END), 'HH24:MI') AS tuesday,
  89.     TO_CHAR(MAX(CASE WHEN day_id = 3 THEN stop_working_time END), 'HH24:MI') AS wednesday,
  90.     TO_CHAR(MAX(CASE WHEN day_id = 4 THEN stop_working_time END), 'HH24:MI') AS thursday,
  91.     TO_CHAR(MAX(CASE WHEN day_id = 5 THEN stop_working_time END), 'HH24:MI') AS friday,
  92.     TO_CHAR(MAX(CASE WHEN day_id = 6 THEN stop_working_time END), 'HH24:MI') AS saturday,
  93.     TO_CHAR(MAX(CASE WHEN day_id = 7 THEN stop_working_time END), 'HH24:MI') AS sunday
  94. FROM working_schedule_day
  95. WHERE working_schedule_id = :working_schedule_id
  96. UNION ALL
  97. SELECT
  98.     'pause_start_time' AS field_name,
  99.     TO_CHAR(MAX(CASE WHEN day_id = 1 THEN pause_start_time END), 'HH24:MI') AS monday,
  100.     TO_CHAR(MAX(CASE WHEN day_id = 2 THEN pause_start_time END), 'HH24:MI') AS tuesday,
  101.     TO_CHAR(MAX(CASE WHEN day_id = 3 THEN pause_start_time END), 'HH24:MI') AS wednesday,
  102.     TO_CHAR(MAX(CASE WHEN day_id = 4 THEN pause_start_time END), 'HH24:MI') AS thursday,
  103.     TO_CHAR(MAX(CASE WHEN day_id = 5 THEN pause_start_time END), 'HH24:MI') AS friday,
  104.     TO_CHAR(MAX(CASE WHEN day_id = 6 THEN pause_start_time END), 'HH24:MI') AS saturday,
  105.     TO_CHAR(MAX(CASE WHEN day_id = 7 THEN pause_start_time END), 'HH24:MI') AS sunday
  106. FROM working_schedule_day
  107. WHERE working_schedule_id = :working_schedule_id
  108. UNION ALL
  109. SELECT
  110.     'pause_stop_time' AS field_name,
  111.     TO_CHAR(MAX(CASE WHEN day_id = 1 THEN pause_stop_time END), 'HH24:MI') AS monday,
  112.     TO_CHAR(MAX(CASE WHEN day_id = 2 THEN pause_stop_time END), 'HH24:MI') AS tuesday,
  113.     TO_CHAR(MAX(CASE WHEN day_id = 3 THEN pause_stop_time END), 'HH24:MI') AS wednesday,
  114.     TO_CHAR(MAX(CASE WHEN day_id = 4 THEN pause_stop_time END), 'HH24:MI') AS thursday,
  115.     TO_CHAR(MAX(CASE WHEN day_id = 5 THEN pause_stop_time END), 'HH24:MI') AS friday,
  116.     TO_CHAR(MAX(CASE WHEN day_id = 6 THEN pause_stop_time END), 'HH24:MI') AS saturday,
  117.     TO_CHAR(MAX(CASE WHEN day_id = 7 THEN pause_stop_time END), 'HH24:MI') AS sunday
  118. FROM working_schedule_day
  119. WHERE working_schedule_id = :working_schedule_id
  120. UNION ALL
  121. SELECT
  122.     'working_schedule_id' AS field_name,
  123.     TO_CHAR(MAX(CASE WHEN day_id = 1 THEN working_schedule_id END)) AS monday,
  124.     TO_CHAR(MAX(CASE WHEN day_id = 2 THEN working_schedule_id END)) AS tuesday,
  125.     TO_CHAR(MAX(CASE WHEN day_id = 3 THEN working_schedule_id END)) AS wednesday,
  126.     TO_CHAR(MAX(CASE WHEN day_id = 4 THEN working_schedule_id END)) AS thursday,
  127.     TO_CHAR(MAX(CASE WHEN day_id = 5 THEN working_schedule_id END)) AS friday,
  128.     TO_CHAR(MAX(CASE WHEN day_id = 6 THEN working_schedule_id END)) AS saturday,
  129.     TO_CHAR(MAX(CASE WHEN day_id = 7 THEN working_schedule_id END)) AS sunday
  130. FROM working_schedule_day
  131. WHERE working_schedule_id = :working_schedule_id
  132. UNION ALL
  133. SELECT
  134.     'day_id' AS field_name,
  135.     TO_CHAR(MAX(CASE WHEN day_id = 1 THEN day_id END)) AS monday,
  136.     TO_CHAR(MAX(CASE WHEN day_id = 2 THEN day_id END)) AS tuesday,
  137.     TO_CHAR(MAX(CASE WHEN day_id = 3 THEN day_id END)) AS wednesday,
  138.     TO_CHAR(MAX(CASE WHEN day_id = 4 THEN day_id END)) AS thursday,
  139.     TO_CHAR(MAX(CASE WHEN day_id = 5 THEN day_id END)) AS friday,
  140.     TO_CHAR(MAX(CASE WHEN day_id = 6 THEN day_id END)) AS saturday,
  141.     TO_CHAR(MAX(CASE WHEN day_id = 7 THEN day_id END)) AS sunday
  142. FROM working_schedule_day
  143. WHERE working_schedule_id = :working_schedule_id;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!