API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Aug 1st, 2018
103
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.97 KB | None | 0 0
raw download clone embed print report
  1. SELECT * FROM dblink('dbname=fulcrum user=postgres password=postgres','SELECT * FROM fulcrum.adsl_poles')AS tb2
  2. (sk bigint, parent_id text, adsl_id text, telco text, reference_number text, _version int, coordinates text, updated_at timestamp, created_at text);
  3.  
  4.  
  5.  
  6.  
  7. SELECT MAX(id)id, MAX(store_id)store_id, MAX(store_name)store_name, MAX(store_status)store_status, CASE WHEN MAX(updated_at)= '2001-01-01 00:00:00' THEN NULL ELSE MAX(updated_at)END AS updated_at
  8. FROM(
  9. SELECT 1 AS sk, foo1.id, foo1.store_name, '' AS store_id, 'For Workshop and Contract Signing' AS store_status, foo2.updated_at FROM (SELECT filt1.id, filt1.store_name, filt1.updated_at FROM public.store filt1 LEFT JOIN public.store filt2 ON (filt1.id = filt2.id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.store_status = 'For Workshop and Contract Signing') foo1 LEFT JOIN (SELECT filt1.* FROM obiwan.workshop_attendees filt1 LEFT JOIN obiwan.workshop_attendees filt2 ON (filt1.fulcrum_id = filt2.fulcrum_id AND filt1.sk < filt2.sk)WHERE filt2.sk IS NULL) foo2 ON foo1.id = foo2.fulcrum_id WHERE foo2.fulcrum_id IS NULL
  10. UNION
  11. SELECT * FROM(SELECT 2 AS sk, foo1.fulcrum_id, foo1.store_name, CAST('' AS varchar) store_id, CAST('For Store ID Creation' AS varchar)store_status, greatest(foo1.updated_at, foo2.updated_at)AS updated_at FROM(SELECT filt1.* FROM obiwan.workshop_attendees filt1 LEFT JOIN obiwan.workshop_attendees filt2 ON (filt1.fulcrum_id = filt2.fulcrum_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.contract_sign = 'Yes')AS foo1 LEFT JOIN (SELECT filt1.* FROM obiwan.migo_store_directory filt1 LEFT JOIN obiwan.migo_store_directory filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.fulcrum_id IS NOT NULL) AS foo2 ON foo1.fulcrum_id = foo2.fulcrum_id WHERE (foo2.store_id = '') IS NOT FALSE ORDER BY foo2.store_id) AS cons1
  12. UNION
  13. SELECT 3 AS sk, foo3.fulcrum_id, foo3.store_name, store_id, 'For Production' AS store_status, greatest(foo3.updated_at, foo4.updated_at)AS updated_at FROM (SELECT fulcrum_id, store_name, foo1.store_id, greatest(foo1.updated_at, foo2.updated_at)AS updated_at FROM (SELECT filt1.* FROM obiwan.migo_store_directory filt1 LEFT JOIN obiwan.migo_store_directory filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.fulcrum_id IS NOT NULL) AS foo1 LEFT JOIN (SELECT filt1.* FROM obiwan.assembly filt1 LEFT JOIN obiwan.assembly filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND (filt1.hotspot_num = '') IS FALSE) AS foo2 ON foo1.store_id = foo2.store_id WHERE foo2.store_id IS NULL) AS foo3 LEFT JOIN (SELECT filt1.* FROM obiwan.workshop_attendees filt1 LEFT JOIN obiwan.workshop_attendees filt2 ON (filt1.fulcrum_id = filt2.fulcrum_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL) AS foo4 ON foo3.fulcrum_id = foo4.fulcrum_id WHERE contract_sign = 'Yes'
  14. UNION
  15. SELECT 4 AS sk, foo4.fulcrum_id, foo4.store_name, foo4.store_id, 'For Hotspot Deployment' AS store_status, greatest(foo3.updated_at, foo4.updated_at)AS updated_at FROM(SELECT foo1.store_id, greatest(foo1.updated_at, foo2.updated_at)AS updated_at FROM(SELECT filt1.* FROM obiwan.assembly filt1 LEFT JOIN obiwan.assembly filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND (filt1.hotspot_num = '') IS NOT TRUE) AS foo1 LEFT JOIN (SELECT filt1.* FROM obiwan.deploy_pullout filt1 LEFT JOIN obiwan.deploy_pullout filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.activity IN ('deploy','pullout')) AS foo2 ON foo1.store_id = foo2.store_id WHERE (foo2.store_id = '') IS NOT FALSE)AS foo3 LEFT JOIN (SELECT filt1.* FROM obiwan.migo_store_directory filt1 LEFT JOIN obiwan.migo_store_directory filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.fulcrum_id IS NOT NULL) AS foo4 ON foo3.store_id = foo4.store_id LEFT JOIN (SELECT filt1.* FROM obiwan.workshop_attendees filt1 LEFT JOIN obiwan.workshop_attendees filt2 ON (filt1.fulcrum_id = filt2.fulcrum_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL) foo5 ON foo4.fulcrum_id = foo5.fulcrum_id WHERE contract_sign = 'Yes'
  16. UNION
  17. SELECT 5 AS sk, foo4.fulcrum_id, foo4.store_name, foo4.store_id, 'For Liv Onboarding' AS store_status, greatest(foo3.updated_at, foo4.updated_at, foo5.updated_at)AS updated_at FROM (SELECT foo1.updated_at, foo1.store_id FROM(SELECT filt1.* FROM obiwan.deploy_pullout filt1 LEFT JOIN obiwan.deploy_pullout filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.activity = 'deploy') AS foo1 LEFT JOIN (SELECT filt1.* FROM obiwan.liv_on_boarding filt1 LEFT JOIN obiwan.liv_on_boarding filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.act_stat = 'Onboarding')AS foo2 ON foo1.store_id = foo2.store_id WHERE (foo2.store_id = '') IS NOT FALSE)AS foo3 LEFT JOIN (SELECT filt1.* FROM obiwan.migo_store_directory filt1 LEFT JOIN obiwan.migo_store_directory filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.fulcrum_id IS NOT NULL) AS foo4 ON foo3.store_id = foo4.store_id LEFT JOIN (SELECT filt1.* FROM obiwan.workshop_attendees filt1 LEFT JOIN obiwan.workshop_attendees filt2 ON (filt1.fulcrum_id = filt2.fulcrum_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL) AS foo5 ON foo4.fulcrum_id = foo5.fulcrum_id WHERE contract_sign = 'Yes'
  18. UNION
  19. SELECT 6 AS sk, foo4.fulcrum_id, foo4.store_name, foo4.store_id, 'For Store Opening' AS store_status, foo3.updated_at FROM(SELECT foo1.updated_at, foo1.store_id FROM(SELECT filt1.* FROM obiwan.liv_on_boarding filt1 LEFT JOIN obiwan.liv_on_boarding filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.act_stat = 'Onboarding') AS foo1 LEFT JOIN (SELECT filt1.* FROM obiwan.store_open filt1 LEFT JOIN obiwan.store_open filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.act_stat = 'Store Open') AS foo2 ON foo1.store_id = foo2.store_id WHERE (foo2.store_id = '') IS NOT FALSE) AS foo3 LEFT JOIN (SELECT filt1.* FROM obiwan.migo_store_directory filt1 LEFT JOIN obiwan.migo_store_directory filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.fulcrum_id IS NOT NULL) AS foo4 ON foo3.store_id = foo4.store_id LEFT JOIN (SELECT filt1.* FROM obiwan.workshop_attendees filt1 LEFT JOIN obiwan.workshop_attendees filt2 ON (filt1.fulcrum_id = filt2.fulcrum_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL) AS foo5 ON foo4.fulcrum_id = foo5.fulcrum_id WHERE contract_sign = 'Yes'
  20. UNION
  21. SELECT 7 AS sk, fulcrum_id, store_name, foo4.store_id, 'Migo Store' AS store_status, greatest(foo3.updated_at, foo4.updated_at)AS updated_at FROM (SELECT foo1.store_id, foo1.updated_at FROM (SELECT filt1.* FROM obiwan.store_open filt1 LEFT JOIN obiwan.store_open filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.act_stat = 'Store Open') foo1 LEFT JOIN (SELECT filt1.* FROM obiwan.deploy_pullout filt1 LEFT JOIN obiwan.deploy_pullout filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.activity = 'pullout') foo2 ON foo1.store_id = foo2.store_id WHERE foo2.store_id IS NULL) AS foo3 LEFT JOIN (SELECT filt1.* FROM obiwan.migo_store_directory filt1 LEFT JOIN obiwan.migo_store_directory filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.fulcrum_id IS NOT NULL) AS foo4 ON foo3.store_id = foo4.store_id
  22. UNION
  23. SELECT 8 AS sk, foo1.fulcrum_id, foo1.store_name, foo1.store_id, 'Juana Declined' AS store_status, greatest(foo1.updated_at, foo2.updated_at, foo3.updated_at, foo4.updated_at, foo5.updated_at, foo6.updated_at) AS updated_at FROM (SELECT filt1.* FROM obiwan.migo_store_directory filt1 LEFT JOIN obiwan.migo_store_directory filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.fulcrum_id IS NOT NULL) foo1 LEFT JOIN (SELECT filt1.* FROM obiwan.assembly filt1 LEFT JOIN obiwan.assembly filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND (filt1.hotspot_num = '') IS FALSE) foo2 ON foo1.store_id = foo2.store_id LEFT JOIN (SELECT filt1.* FROM obiwan.deploy_pullout filt1 LEFT JOIN obiwan.deploy_pullout filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.activity IN ('deploy','pullout')) foo3 ON foo1.store_id = foo3.store_id LEFT JOIN (SELECT filt1.* FROM obiwan.liv_on_boarding filt1 LEFT JOIN obiwan.liv_on_boarding filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.act_stat = 'Onboarding') foo4 ON foo1.store_id = foo4.store_id LEFT JOIN (SELECT filt1.* FROM obiwan.store_open filt1 LEFT JOIN obiwan.store_open filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.act_stat = 'Store Open') foo5 ON foo1.store_id = foo5.store_id LEFT JOIN (SELECT filt1.* FROM obiwan.workshop_attendees filt1 LEFT JOIN obiwan.workshop_attendees filt2 ON (filt1.fulcrum_id = filt2.fulcrum_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL) foo6 ON foo1.fulcrum_id = foo6.fulcrum_id WHERE contract_sign = 'No'
  24. UNION
  25. SELECT 9 AS sk, fulcrum_id, store_name, foo1.store_id, 'For Churn' AS store_status, greatest(foo1.updated_at, foo2.updated_at)AS updated_at FROM(SELECT * FROM(SELECT filt1.fulcrum_id, filt1.store_name, filt1.stop_operate_date, filt1.initiated_by, filt1.reason, filt1.sugg_pullout_date, filt1.preferred_pullout_date, filt1.sched_pullout_date, filt1.ap_closure_date, filt1.acct_closure_date, filt1.updated_at, filt1.store_id, filt1.req_id, CASE WHEN filt1.sched_pullout_date = '2001-01-01 00:00:00' THEN FALSE ELSE TRUE END AS is_exist FROM obiwan.sales_request filt1 LEFT JOIN obiwan.sales_request filt2 ON(filt1.fulcrum_id = filt2.fulcrum_id AND filt1.sk < filt2.sk)WHERE filt2.sk IS NULL)AS foo WHERE is_exist IS FALSE)AS foo1 LEFT JOIN(SELECT filt1.store_id, filt1.date, filt1.updated_at, filt1.activity FROM obiwan.deploy_pullout filt1 LEFT JOIN obiwan.deploy_pullout filt2 ON(filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk)WHERE filt2.sk IS NULL AND filt1.activity = 'pullout')AS foo2 ON foo1.store_id = foo2.store_id WHERE foo2.date IS NULL
  26. UNION
  27. SELECT 10 AS sk, fulcrum_id, store_name, foo1.store_id,'Churned' AS store_status, greatest(foo1.updated_at, foo2.updated_at)AS updated_at FROM (SELECT filt1.updated_at, filt1.store_id FROM obiwan.deploy_pullout filt1 LEFT JOIN obiwan.deploy_pullout filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk)WHERE filt2.sk IS NULL AND filt1.activity = 'pullout')AS foo1 LEFT JOIN (SELECT filt1.* FROM obiwan.migo_store_directory filt1 LEFT JOIN obiwan.migo_store_directory filt2 ON (filt1.store_id = filt2.store_id AND filt1.sk < filt2.sk) WHERE filt2.sk IS NULL AND filt1.fulcrum_id IS NOT NULL) AS foo2 ON foo1.store_id = foo2.store_id
  28. )AS foo
  29. WHERE id != '' GROUP BY id
  30. ORDER BY updated_at desc
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!