Guest User

Untitled

a guest
Dec 8th, 2019
89
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