Aggregating multiple records by date

Question

I have a Model that looks like:

MedicationAdherence {
                 :id => :integer,
     :adherence_date => :date,
     :scheduled_time => :string,
    :acknowledged_at => :datetime,
         :patient_id => :integer,
         :created_at => :datetime,
         :updated_at => :datetime
}

I have 7 records (same patient_id):

{ id: 1, adherence_date: 2017-10-01, scheduled_time: 'morning', acknowledged_at: Tue, 31 Oct 2017 19:59:19 UTC +00:00 }
{ id: 2, adherence_date: 2017-10-01, scheduled_time: 'afternoon', acknowledged_at: nil }
{ id: 3, adherence_date: 2017-10-01, scheduled_time: 'night', acknowledged_at: Tue, 31 Oct 2017 19:59:19 UTC +00:00 }
{ id: 4, adherence_date: 2017-10-02, scheduled_time: 'morning', acknowledged_at: Tue, 31 Oct 2017 19:59:19 UTC +00:00 }
{ id: 5, adherence_date: 2017-10-02, scheduled_time: 'afternoon', acknowledged_at: Tue, 31 Oct 2017 19:59:19 UTC +00:00 }
{ id: 6, adherence_date: 2017-10-02, scheduled_time: 'evening', acknowledged_at: Tue, 31 Oct 2017 19:59:19 UTC +00:00 }
{ id: 7, adherence_date: 2017-10-02, scheduled_time: 'night', acknowledged_at: nil }

My desired outcome is to group the records above into the following output:

{
    "adherence_date" => 2017-10-1,
           "morning" => 1,
         "afternoon" => 0,
           "evening" => nil,
             "night" => 1
},
{
    "adherence_date" => 2017-10-2,
           "morning" => 1,
         "afternoon" => 1,
           "evening" => 1,
             "night" => 0
}

When there is no-record (evening 2017-10-1) it should return nil. When there is a record but no acknowledged_at it should return false (0), and when there is acknowledged_at returns true(1)

Below is the query I used to try and combine all this data, but it gives me duplicate records. How can I sum my data into what I have above...I'm sure there's a simplier way to do this

WITH
  adherences AS (
    SELECT * FROM medication_adherences WHERE patient_id = 10049
  ),

  morning AS (
    SELECT adherence_date,
      CASE acknowledged_at WHEN null THEN 0 ELSE 1 END as morning
    FROM adherences
    WHERE scheduled_time = 'morning'
  ),

  afternoon as (
    SELECT adherence_date,
      CASE acknowledged_at WHEN null THEN 0 ELSE 1 END as afternoon
    FROM adherences
    WHERE scheduled_time = 'afternoon'
  ),

  evening as (
    SELECT adherence_date,
      CASE acknowledged_at WHEN null THEN 0 ELSE 1 END as evening
    FROM adherences
    WHERE scheduled_time = 'evening'
  ),

  night as (
    SELECT adherence_date,
      CASE acknowledged_at WHEN null THEN 0 ELSE 1 END as night
    FROM adherences
    WHERE scheduled_time = 'night'
  )

SELECT morning.morning, afternoon.afternoon, evening.evening, night.night, adherences.adherence_date
FROM adherences
LEFT JOIN morning ON morning.adherence_date = adherences.adherence_date
LEFT JOIN afternoon ON afternoon.adherence_date = adherences.adherence_date
LEFT JOIN evening ON evening.adherence_date = adherences.adherence_date
LEFT JOIN night ON night.adherence_date = adherences.adherence_date

I'm running oracle-12c

EDIT

Looks like I had to add GROUP BY morning.morning, afternoon.afternoon, evening.evening, night.night, adherences.adherence_date to my query for it to properly group by. Is there a simpler way to aggregate this data?

Ronnis · Accepted Answer

I'm assuming that (patient_id, adherence_date, scheduled_time) is unique in your table, meaning that a patient can book once per "slot" and date.

with medication_adherences  as(
-- This is your test data
   select 10049 as patient_id, 1 as id, date '2017-10-01' as adherence_date, 'morning'    as scheduled_time, timestamp '2017-10-31 19:59:19' as acknowledged_at from dual union all
   select 10049 as patient_id, 2 as id, date '2017-10-01' as adherence_date, 'afternoon'  as scheduled_time, null                            as acknowledged_at from dual union all                          
   select 10049 as patient_id, 3 as id, date '2017-10-01' as adherence_date, 'night'      as scheduled_time, timestamp '2017-10-31 19:59:19' as acknowledged_at from dual union all
   select 10049 as patient_id, 4 as id, date '2017-10-02' as adherence_date, 'morning'    as scheduled_time, timestamp '2017-10-31 19:59:19' as acknowledged_at from dual union all
   select 10049 as patient_id, 5 as id, date '2017-10-02' as adherence_date, 'afternoon'  as scheduled_time, timestamp '2017-10-31 19:59:19' as acknowledged_at from dual union all
   select 10049 as patient_id, 6 as id, date '2017-10-02' as adherence_date, 'evening'    as scheduled_time, timestamp '2017-10-31 19:59:19' as acknowledged_at from dual union all
   select 10049 as patient_id, 7 as id, date '2017-10-02' as adherence_date, 'night'      as scheduled_time, null                            as acknowledged_at from dual
)
select adherence_date
      ,sum(case when scheduled_time = 'morning'   then nvl2(acknowledged_at,1,0) end) as morning
      ,sum(case when scheduled_time = 'afternoon' then nvl2(acknowledged_at,1,0) end) as afternoon
      ,sum(case when scheduled_time = 'evening'   then nvl2(acknowledged_at,1,0) end) as evening
      ,sum(case when scheduled_time = 'night'     then nvl2(acknowledged_at,1,0) end) as night
  from medication_adherences 
 where patient_id = 10049
 group
    by adherence_date;

The logic works like this:

if acknowledged_at is null then we aggregate 0 (via nvl2)
if acknowledged_at is not null then we aggregate 1 (via nvl2)
if there is no record for this timeslot we aggregate null (since case when ... failed)

Aggregating multiple records by date

Answers (2)

Related Questions