ariful_346

Query

Mar 1st, 2021 (edited)
414
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select
  2.     *
  3. from
  4.     client_data cd
  5. inner join data_point dp on
  6.     cd.data_point_id = dp.id
  7. inner join client c on
  8.     c.id = cd.client_id
  9. inner join (
  10.     select
  11.         cd.client_id ,
  12.         cd.data_point_id,
  13.         max(cd.id) as max_client_data_id
  14.     from
  15.         client_data cd
  16.     inner join (
  17.         select
  18.             distinct cd.client_id as client_id
  19.         from
  20.             (
  21.             select
  22.                 cd.client_id ,
  23.                 data_point_id ,
  24.                 max(cd.id) max_client_data_id
  25.             from
  26.                 client_data cd
  27.             group by
  28.                 cd.client_id ,
  29.                 data_point_id) as mcd
  30.         inner join client_data cd on
  31.             cd.id = mcd.max_client_data_id
  32.         inner join data_point dp on
  33.             dp.id = cd.data_point_id
  34.         inner join division dv on
  35.             dv.id =
  36.             (case
  37.                 when regexp_replace(cd."data" ->> 'value', '[^0-9]+', '', 'g')= '' then null
  38.                 else left(regexp_replace(cd."data" ->> 'value', '[^0-9]+', '', 'g'), 7)
  39.             end)::int
  40.             and dp."type" = 'DIVISION'
  41.         where
  42.             dp."name" in ('business_upazilla', 'business_district', 'business_division')
  43.             and dv.id = 4) as sc on
  44.         cd.client_id = sc.client_id
  45.     group by
  46.         cd.client_id ,
  47.         cd.data_point_id ) as ld on
  48.     cd.id = ld.max_client_data_id
  49. left join upazilla u on
  50.     u.id =
  51.     (case
  52.         when regexp_replace(cd."data" ->> 'value', '[^0-9]+', '', 'g')= '' then null
  53.         else left(regexp_replace(cd."data" ->> 'value', '[^0-9]+', '', 'g'), 7)
  54.     end)::int
  55.     and dp."type" = 'UPAZILLA'
  56. left join district ds on
  57.     ds.id =
  58.     (case
  59.         when regexp_replace(cd."data" ->> 'value', '[^0-9]+', '', 'g')= '' then null
  60.         else left(regexp_replace(cd."data" ->> 'value', '[^0-9]+', '', 'g'), 7)
  61.     end)::int
  62.     and dp."type" = 'DISTRICT'
  63. left join division dv on
  64.     dv.id =
  65.     (case
  66.         when regexp_replace(cd."data" ->> 'value', '[^0-9]+', '', 'g')= '' then null
  67.         else left(regexp_replace(cd."data" ->> 'value', '[^0-9]+', '', 'g'), 7)
  68.     end)::int
  69.     and dp."type" = 'DIVISION'
  70. left join data_point_enum dpe on
  71.     dpe.id =
  72.     (case
  73.         when regexp_replace(cd."data" ->> 'value', '[^0-9]+', '', 'g')= '' then null
  74.         else left(regexp_replace(cd."data" ->> 'value', '[^0-9]+', '', 'g'), 18)
  75.     end)::bigint
  76.     and dp."type" = 'ENUM' ;
  77.  
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×