SHARE
TWEET

Untitled

a guest Dec 8th, 2019 81 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. with epmTable as (select epm.member_id,
  2.                          event_plan_id,
  3.                          group_id,
  4.                          data::jsonb ->> 'date'      as                                   date,
  5.                          data::jsonb ->> 'isPresent' as                                   present,
  6.                          case when data::jsonb ->> 'isPresent' = 'true' then 1 else 0 end present_absent
  7.  
  8.                   from (select epmember.attendance,
  9.                                epmember.member_id,
  10.                                epmember.group_id,
  11.                                epmember.event_plan_id,
  12.                                jsonb_array_elements_text(epmember.attendance::jsonb) as data
  13.                         from event_plan_member epmember
  14.                         where is_json(attendance) = true
  15.                           and attendance <> '[]'
  16.                        ) as epm),
  17.      attTable as (
  18.          select epmTable.event_plan_id,
  19.                 epmTable.group_id,
  20.                 epmTable.date::date,
  21.                 epmTable.member_id,
  22.                 sum(present_absent) as attendSum
  23.          from epmTable
  24.          group by epmTable.event_plan_id, epmTable.date, epmTable.member_id, epmTable.present, epmTable.group_id),
  25.      eventTable1 as (
  26.          select es.name                                     as session_name,
  27.                 epmAttend.member_id,
  28.                 epmAttend.group_id,
  29.                 epmAttend.date,
  30.                 epmAttend.attendSum,
  31.                 ep.id                                       as plan_id,
  32.                 ep.name                                     as plan_name,
  33.                 ep.from_date::date                          as plan_from_date,
  34.                 ep.to_date::date                            as plan_to_date,
  35.                 (ep.to_date::date - ep.from_date::date) + 1 as total_plan_days,
  36.                 e.name                                      as eventName,
  37.                 et.name                                     as eventType,
  38.                 a.name                                      as activity,
  39.                 at.name                                     as activityType
  40.  
  41.  
  42.          from attTable epmAttend
  43.                   join event_plan ep on ep.id = epmAttend.event_plan_id
  44.                   join event_session_for_user esfu on esfu.id = ep.event_session_for_user_id
  45.                   join event_session es on es.id = esfu.event_session_id
  46.                   join event e on e.id = es.event_id
  47.                   join event_type et on et.id = e.event_type_id
  48.                   join activity a on a.id = e.activity_id
  49.                   join activity_type at on at.id = a.activity_type_id
  50.  
  51.          where epmAttend.member_id like '%BD%'
  52.            and at.name like '%19%'
  53.            and ep.is_executed = true),
  54.      groupTable as (select cp."Upazila"     as Group_upazila,
  55.                            cp."Union"       as group_union,
  56.                            cp."Para"        as group_para,
  57.                            pp.purpose,
  58.                            pp.componenet,
  59.                            pp.participent_domain,
  60.                            pgt.name         as Group_type,
  61.                            pg.id            as group_id,
  62.                            pg.name          as Group_name,
  63.                            pg.group_formation_date::date,
  64.                            count(member_id) as current_total_of_register_participants
  65.                     from participant_group_member pgm
  66.                              join participant_group pg on pgm.group_id = pg.id
  67.                              join participant_group_type pgt on pgt.id = pg.participant_group_type_id
  68.                              join program_pivoted pp on pp.program_id = pgt.component_id
  69.                              join catchment_pivoted cp on cp.catchment_id = pg.catchment_id
  70.                     where pgm.member_id like '%BD%'
  71.                       and pg.status = 'Approved'
  72.                     group by pg.id, pg.name, pp.purpose, pp.componenet, pp.participent_domain, pgt.name,
  73.                              pg.group_formation_date, cp."Upazila", cp."Union", cp."Para")
  74. select gt1.Group_upazila,
  75.        gt1.group_union,
  76.        gt1.group_para,
  77.        gt1.purpose,
  78.        gt1.componenet,
  79.        gt1.participent_domain,
  80.        gt1.Group_type,
  81.        gt1.group_id,
  82.        gt1.Group_name,
  83.        gt1.current_total_of_register_participants,
  84.        et1.eventType,
  85.        et1.eventName,
  86.        et1.session_name,
  87.        et1.plan_id,
  88.        et1.plan_to_date,
  89.        et1.plan_from_date,
  90.        et1.total_plan_days,
  91.        min(et1.date) as execution_date,
  92.        sum(et1.attendSum) as total_participant_attend
  93. from eventTable1 et1
  94.          join groupTable gt1 on gt1.group_id = et1.group_id
  95. where gt1.group_id = 'b9719c51-e565-19da-8190-0ec55fc5f0c3'
  96. group by et1.plan_id, et1.eventType, gt1.group_id, gt1.Group_name, gt1.current_total_of_register_participants,
  97.          gt1.Group_type, et1.eventName, et1.session_name, et1.plan_to_date, et1.plan_from_date, et1.total_plan_days,
  98.          gt1.Group_upazila, gt1.group_union, gt1.group_para, gt1.purpose,
  99.          gt1.componenet,
  100.          gt1.participent_domain
RAW Paste Data
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!
 
Top