get hourly results for count in the last 12 hours - MySql

18
January 13, 2019, at 02:10 AM

I have a big query that is supposed to return the count for rfid's for each product have an association with one order

the purpose is to batch the count per 12 hours with 1 hour interval since the first entry, and count the min(first_in) for all the locations. The query should only account for the first rfid read or the first occurence in the db i ignore the rest that is why use min(first_in).

so far i am able to query across all locations with batching works, the problem is with the count. the total lables should aggregate all the counts from the 12 slots and display it on total_labels, this the result of the query which is not correct

    ----------------------------------------------------------------------------------------------------------------------------------------------------------
    | order_id  | location_id | total_lables | hour_1 | hour_2 | hour_3 | hour_4 | hour_5 | hour_6 | hour_7 | hour_8 | hour_9 | hour_10 | hour_11 | hour_12 |
    -----------------------------------------------------------------------------------------------------------------------------------------------------------
    | 122324-07 |    454645    |      38    |    2   |   4    |   5    |    1   |   5    |    6   |   3    |   6    |    6   |    1    |  NULL   |   1     |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------

the query shows that total_labels are less than the sum of all the 12 labels together? i am wondering what went wrong? or even if the query is right?

also i would like to know if there is a cleaner way to do this query it is very big and hard to debug?

  create table if not exists rfid_first_in(rfid varchar(100), 
  location_id varchar(100), zone varchar(100), first_in timestamp(3));
    insert into rfid_first_in(rfid, location_id, zone, first_in) 
    select rfid, location_id, facility_id, min(last_read) first_in
     from inventory_event_history ieh WHERE last_read is NOT NULL
    group by rfid, location_id, facility_id;
    create table if not exists product_first_in(product_uuid varchar(100), location_id varchar(100), order_id varchar(100), first_in timestamp(3));
    insert into product_first_in(product_uuid, location_id, order_id, first_in)
    select aor.product_uuid, o.location_id, o.id order_id, min(first_in)
    from order o
    join order_product ii
    on ii.order_id = o.id
    join invoice_order io
    on io.order_id = o.id
    join act_order ao
    on ao.order_id = io.order_id
    join act_order_rfid aor
    on aor.asn_id = ao.asn_id
    join rfid_first_in t
    on aor.rfid = t.rfid
    group by aor.product_uuid, o.location_id, o.id;
    create table order_time_slot(order_id varchar(100), 
      location_id varchar(100),
      hour_1 timestamp(3) null default null, 
      hour_2 timestamp(3) null default null, 
      hour_3 timestamp(3) null default null, 
      hour_4 timestamp(3) null default null, 
      hour_5 timestamp(3) null default null, 
      hour_6 timestamp(3) null default null, 
      hour_7 timestamp(3) null default null, 
      hour_8 timestamp(3) null default null, 
      hour_9 timestamp(3) null default null, 
      hour_10 timestamp(3) null default null, 
      hour_11 timestamp(3) null default null, 
      hour_12 timestamp(3) null default null);
    insert into order_time_slot(order_id, location_id,
      hour_1,
      hour_2,
      hour_3,
      hour_4,
      hour_5,
      hour_6,
      hour_7,
      hour_8,
      hour_9,
      hour_10,
      hour_11,
      hour_12)
    select pfi.order_id, 
      pfi.location_id, 
      pfi.first_in, 
      addtime(pfi.first_in, '01:00:00'),
      addtime(pfi.first_in, '02:00:00'),
      addtime(pfi.first_in, '03:00:00'),
      addtime(pfi.first_in, '04:00:00'),
      addtime(pfi.first_in, '05:00:00'),
      addtime(pfi.first_in, '06:00:00'),
      addtime(pfi.first_in, '07:00:00'),
      addtime(pfi.first_in, '08:00:00'),
      addtime(pfi.first_in, '09:00:00'),
      addtime(pfi.first_in, '10:00:00'),
      addtime(pfi.first_in, '11:00:00')
    from product_first_in pfi;
    create table order_rfid_count_hour(order_id varchar(100), location_id varchar(100),
      hour_1 int, hour_2 int, hour_3 int, hour_4 int, hour_5 int, hour_6 int, hour_7 int, hour_8 int, 
      hour_9 int, hour_10 int, hour_11 int, hour_12 int);
    insert into order_rfid_count_hour(order_id, location_id,
      hour_1,
      hour_2,
      hour_3,
      hour_4,
      hour_5,
      hour_6,
      hour_7,
      hour_8,
      hour_9,
      hour_10,
      hour_11,
      hour_12)
    select t1.order_id, t1.location_id, 
      t1.label, t2.label, t3.label, t4.label, t5.label, t6.label, t7.label, t8.label, t9.label, t10.label, t11.label, t12.label
    from (
      select pfi.order_id, pfi.location_id, count(rfi.first_in) label
      from product_first_in pfi
      join act_order_rfid aor
      on aor.product_uuid = pfi.product_uuid
      join rfid_first_in rfi
      on rfi.rfid = aor.rfid
      and rfi.location_id = pfi.location_id
      and rfi.first_in between addtime(pfi.first_in, '00:00:00') and addtime(pfi.first_in, '01:00:00')
      group by pfi.order_id, pfi.location_id) t1
    left join (
      select pfi.order_id, pfi.location_id, count(rfi.first_in) label
      from product_first_in pfi
      join act_order_rfid aor
      on aor.product_uuid = pfi.product_uuid
      join rfid_first_in rfi
      on rfi.rfid = aor.rfid
      and rfi.location_id = pfi.location_id
      and rfi.first_in between addtime(pfi.first_in, '01:00:00') and addtime(pfi.first_in, '02:00:00')
      group by pfi.order_id, pfi.location_id) t2
    on t1.order_id = t2.order_id
    and t1.location_id = t2.location_id
    left join (
      select pfi.order_id, pfi.location_id, count(rfi.first_in) label
      from product_first_in pfi
      join act_order_rfid aor
      on aor.product_uuid = pfi.product_uuid
      join rfid_first_in rfi
      on rfi.rfid = aor.rfid
      and rfi.location_id = pfi.location_id
      and rfi.first_in between addtime(pfi.first_in, '02:00:00') and addtime(pfi.first_in, '03:00:00')
      group by pfi.order_id, pfi.location_id) t3
    on t1.order_id = t3.order_id
    and t1.location_id = t3.location_id
    left join (
      select pfi.order_id, pfi.location_id, count(rfi.first_in) label
      from product_first_in pfi
      join act_order_rfid aor
      on aor.product_uuid = pfi.product_uuid
      join rfid_first_in rfi
      on rfi.rfid = aor.rfid
      and rfi.location_id = pfi.location_id
      and rfi.first_in between addtime(pfi.first_in, '03:00:00') and addtime(pfi.first_in, '04:00:00')
      group by pfi.order_id, pfi.location_id) t4
    on t1.order_id = t4.order_id
    and t1.location_id = t4.location_id
    left join (
      select pfi.order_id, pfi.location_id, count(rfi.first_in) label
      from product_first_in pfi
      join act_order_rfid aor
      on aor.product_uuid = pfi.product_uuid
      join rfid_first_in rfi
      on rfi.rfid = aor.rfid
      and rfi.location_id = pfi.location_id
      and rfi.first_in between addtime(pfi.first_in, '04:00:00') and addtime(pfi.first_in, '05:00:00')
      group by pfi.order_id, pfi.location_id) t5
    on t1.order_id = t5.order_id
    and t1.location_id = t5.location_id
    left join (
      select pfi.order_id, pfi.location_id, count(rfi.first_in) label
      from product_first_in pfi
      join act_order_rfid aor
      on aor.product_uuid = pfi.product_uuid
      join rfid_first_in rfi
      on rfi.rfid = aor.rfid
      and rfi.location_id = pfi.location_id
      and rfi.first_in between addtime(pfi.first_in, '05:00:00') and addtime(pfi.first_in, '06:00:00')
      group by pfi.order_id, pfi.location_id) t6
    on t1.order_id = t6.order_id
    and t1.location_id = t6.location_id
    left join (
      select pfi.order_id, pfi.location_id, count(rfi.first_in) label
      from product_first_in pfi
      join act_order_rfid aor
      on aor.product_uuid = pfi.product_uuid
      join rfid_first_in rfi
      on rfi.rfid = aor.rfid
      and rfi.location_id = pfi.location_id
      and rfi.first_in between addtime(pfi.first_in, '06:00:00') and addtime(pfi.first_in, '07:00:00')
      group by pfi.order_id, pfi.location_id) t7
    on t1.order_id = t7.order_id
    and t1.location_id = t7.location_id
    left join (
      select pfi.order_id, pfi.location_id, count(rfi.first_in) label
      from product_first_in pfi
      join act_order_rfid aor
      on aor.product_uuid = pfi.product_uuid
      join rfid_first_in rfi
      on rfi.rfid = aor.rfid
      and rfi.location_id = pfi.location_id
      and rfi.first_in between addtime(pfi.first_in, '07:00:00') and addtime(pfi.first_in, '08:00:00')
      group by pfi.order_id, pfi.location_id) t8
    on t1.order_id = t8.order_id
    and t1.location_id = t8.location_id
    left join (
      select pfi.order_id, pfi.location_id, count(rfi.first_in) label
      from product_first_in pfi
      join act_order_rfid aor
      on aor.product_uuid = pfi.product_uuid
      join rfid_first_in rfi
      on rfi.rfid = aor.rfid
      and rfi.location_id = pfi.location_id
      and rfi.first_in between addtime(pfi.first_in, '08:00:00') and addtime(pfi.first_in, '09:00:00')
      group by pfi.order_id, pfi.location_id) t9
    on t1.order_id = t9.order_id
    and t1.location_id = t9.location_id
    left join (
      select pfi.order_id, pfi.location_id, count(rfi.first_in) label
      from product_first_in pfi
      join act_order_rfid aor
      on aor.product_uuid = pfi.product_uuid
      join rfid_first_in rfi
      on rfi.rfid = aor.rfid
      and rfi.location_id = pfi.location_id
      and rfi.first_in between addtime(pfi.first_in, '09:00:00') and addtime(pfi.first_in, '10:00:00')
      group by pfi.order_id, pfi.location_id) t10
    on t1.order_id = t10.order_id
    and t1.location_id = t10.location_id
    left join (
      select pfi.order_id, pfi.location_id, count(rfi.first_in) label
      from product_first_in pfi
      join act_order_rfid aor
      on aor.product_uuid = pfi.product_uuid
      join rfid_first_in rfi
      on rfi.rfid = aor.rfid
      and rfi.location_id = pfi.location_id
      and rfi.first_in between addtime(pfi.first_in, '10:00:00') and addtime(pfi.first_in, '11:00:00')
      group by pfi.order_id, pfi.location_id) t11
    on t1.order_id = t11.order_id
    and t1.location_id = t11.location_id
    left join (
      select pfi.order_id, pfi.location_id, count(rfi.first_in) label
      from product_first_in pfi
      join act_order_rfid aor
      on aor.product_uuid = pfi.product_uuid
      join rfid_first_in rfi
      on rfi.rfid = aor.rfid
      and rfi.location_id = pfi.location_id
      and rfi.first_in between addtime(pfi.first_in, '11:00:00') and addtime(pfi.first_in, '12:00:00')
      group by pfi.order_id, pfi.location_id) t12
    on t1.order_id = t12.order_id
    and t1.location_id = t12.location_id;
    select s.order_id, s.location_id, t.total total_labels,
      s.hour_1,
      s.hour_2,
      s.hour_3,
      s.hour_4,
      s.hour_5,
      s.hour_6,
      s.hour_7,
      s.hour_8,
      s.hour_9,
      s.hour_10,
      s.hour_11,
      s.hour_12
    from order_rfid_count_hour s
    left join (
      select o.id order_id, o.location_id, count(aor.rfid) total
      from order o
      join order_product ii
      on ii.order_id = o.id
      join invoice_order io
      on io.order_id = o.id
      join act_order ao
      on ao.order_id = io.order_id
      join act_order_rfid aor
      on aor.product_uuid = ii.product_uuid
      and aor.asn_id = ao.asn_id
      and aor.last_read IS NOT NULL
      group by o.id, o.location_id) t
    on t.order_id = s.order_id
    and t.location_id = s.location_id
    order by s.location_id, s.order_id;
READ ALSO
Android Pie 9.0 not playing the audio

Android Pie 9.0 not playing the audio

I am getting the following errors when I run my app on Android 90

40
Not updating ViewModel after Retrofit CREATE request

Not updating ViewModel after Retrofit CREATE request

I'm using the Retrofit client to perform HTTP requests in a simple Android projectWhen I send a CREATE request, the item is successfully created in the remote SQLite database, but the ViewModel still contains the old list

22