Advertisement
psi_mmobile

Untitled

Jul 9th, 2020
1,542
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 1.58 KB | None | 0 0
  1. --person difference
  2. SELECT d.person_id, d.last_person_data_id, b.person_data_id
  3. FROM
  4.   (SELECT p.person_id,
  5.     p.last_person_data_id
  6.   FROM person p,
  7.     vo_person_category vopc
  8.   WHERE p.vo_person_category_id = vopc.vo_person_category_id
  9.   AND NVL(person_status_id,0)   = 0
  10.   AND NVL(vopc.is_active,'Y')    = 'Y'
  11.   AND p.last_person_data_id    IS NOT NULL
  12.   ) d,
  13. (SELECT pd.person_data_id,
  14.   pd.person_id,
  15.   pd.event_date
  16. FROM person_data pd,
  17.   (SELECT p.person_id,
  18.     MAX(pd.event_date) AS dt
  19.   FROM person_data pd,
  20.     person p
  21.   WHERE pd.person_id            = p.person_id
  22.   AND NVL(p.person_status_id,0) = 0
  23.   GROUP BY p.person_id
  24.   ) d
  25. WHERE d.person_id = pd.person_id
  26. AND d.dt          = pd.event_date
  27. ) b
  28.   WHERE d.person_id = b.person_id;
  29.  
  30. --vehicle difference
  31. SELECT d.vehicle_id, d.last_vehicle_data_id, b.vehicle_data_id
  32. FROM
  33.   (SELECT v.vehicle_id,
  34.     v.last_vehicle_data_id
  35.   FROM vehicle v,
  36.     vo_vehicle_category vovc
  37.   WHERE v.vo_vehicle_category_id = vovc.vo_vehicle_category_id
  38.   AND NVL(vehicle_status_id,0)   = 0
  39.   AND NVL(vovc.is_active,'Y')    = 'Y'
  40.   AND v.last_vehicle_data_id    IS NOT NULL
  41.   ) d,
  42.   (SELECT vd.vehicle_data_id,
  43.     vd.vehicle_id,
  44.     vd.gps_full_date
  45.   FROM vehicle_data vd,
  46.     (SELECT v.vehicle_id,
  47.       MAX(vd.gps_full_date) AS dt
  48.     FROM vehicle_data vd,
  49.       vehicle v
  50.     WHERE vd.vehicle_id            = v.vehicle_id
  51.     AND NVL(v.vehicle_status_id,0) = 0
  52.     GROUP BY v.vehicle_id
  53.     ) d
  54.   WHERE d.vehicle_id = vd.vehicle_id
  55.   AND d.dt           = vd.gps_full_date
  56.   ) b
  57.   WHERE d.vehicle_id = b.vehicle_id;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement