postgresql calculate quantity of stock per each age group

user_7823702 0
Viewed 228

I want to calculate the age of stock in my table and group them per ages <30 days, 30 days to 60 days exclusive, 60 days to 90 days exclusive, 90 days to 6 months exclusive and > 6 months. Here is a sample table of my inventory:

<id:1, item_id:1, adjustment: 5,  amount:5,  date:"2020-01-01T00:00:00-04:00">
<id:2, item_id:1, adjustment: 1,  amount:6,  date:"2020-01-01T10:00:00-04:00">
<id:3, item_id:2, adjustment: 10, amount:10, date:"2020-01-01T00:00:00-04:00">
<id:4, item_id:1, adjustment: 5,  amount:11, date:"2020-02-01T00:00:00-04:00">    
<id:5, item_id:1, adjustment: -1, amount:10, date:"2020-03-02T00:00:00-04:00">
<id:6, item_id:2, adjustment: 1,  amount:11, date:"2020-03-02T00:00:00-04:00">

The query will be given a date parameter from which the dates will apply to. So in this case, let's say the date parameter is 2020-03-02T00:00:00 and if we are looking at <30 days, that will imply 30 days prior to 2020-03-02T00:00:00. Important thing to note here: whenever the adjustment is less than 0, it implies that an item was sold and this stock is removed from the oldest stock. Also, per each date category, you'll want to grab the latest item_id because that will represent your most recent stock for that item. To be more clear, a sample response querying the table above would be:

result = {
  "<30 days": 1,
  "30 days to 60 days exclusive": 5,
  "60 days to 90 days exclusive": 15, 
  "90 days to 6 months exclusive": 0,
  "> 6 months": 0 
}

Allow me to explain the result:

  1. The amount of stock less than 30 days old is taken from the amount of id:6 (the last record in the table). This one is pretty straightforward, it is the result of adding an extra item at the start of March.
  2. The amount of stock that are 30 to 60 days old are the ones added on 2020-02-01T00:00:00 (id: 4, item_id: 1). This amount is 5 because that's the quantity added on that date. This value does not include items from 2020-01-01 since those items are exactly 60 days old.
  3. The amount of stock that are 60 to 90 days old are the ones added on 2020-01-01. Important: So this takes the latest record for each item_id from that time (so for item_id:1 it would be: id:2 and for item_id:2 it would be id:3). Also, for item_id:1 we can see that in record with id:5, an item was removed and since we are assuming it was the oldest stock, it would be removed from that batch of items. Therefore this value is calculated by doing 6(id:2) + 10(id:3) - 1(id:5) = 25.

An important thing to note here, is when the adjustment is negative (meaning an item was removed/sold) we will assume that it's the oldest item that was removed and that is why we didn't subtract the 1 removed item from id:4 when we were calculating the stock that are 30 to 60 days (exclusive) old.

So I'm trying to write an sql query and execute it using Model.find_by_sql. Any help would be greatly appreciated. I created the table in fiddle here for testing purposes. Thanks.

postgresql ruby-on-rails
1 Answers

This is could be a solution with a hardcoded date '2020-03-02T00:00:00'.

But It needs to be tested with some hedge case, mainly for negative adjustments.

For example, if the adjustment on date 2020-03-02T00:00:00-04:00 instead of being -1 it was -7, and of course for different input dates.

The most difficult scenario to handle is when you have a negative adjustment for an item_id and a positive adjustment for another item_id.

I'm not sure what the behaviour in those cases could be. So more than a perfectly working solution consider it a start point, a roughly sketched soution.

SELECT
  SUM (
    CASE
    WHEN grouped_item.difference < interval '30 days' and grouped_item.difference >= interval '0 days' THEN
        grouped_item.sum
      ELSE
        0
    END
  ) AS "<30 days",
  SUM (
    CASE
    WHEN grouped_item.difference >= interval '30 days' and grouped_item.difference < interval '60 days' THEN
      grouped_item.sum
    ELSE
      0
    END
  ) AS "30 days to 60 days exclusive",
  SUM (
    CASE
    WHEN grouped_item.difference >= interval '60 days' and grouped_item.difference < interval '90 days' THEN
      grouped_item.sum
    ELSE
      0
    END
  ) AS "60 days to 90 days exclusive",
  SUM (
    CASE
    WHEN grouped_item.difference >= interval '90 days' and grouped_item.difference < interval '6 month' THEN
      grouped_item.sum
    ELSE
      0
    END
  ) AS "90 days to 6 months exclusive",
  SUM (
    CASE
    WHEN grouped_item.difference >= interval '6 month' THEN
      grouped_item.sum
    ELSE
      0
    END
  ) AS "> 6 months"
FROM
  (
    select inventory.item_id, sum(inventory.adjustment),
    case
      when inventory.adjustment >= 0 then
        '2020-03-02T00:00:00'::timestamp - inventory.date::timestamp
      else (
        select '2020-03-02T00:00:00'::timestamp - min(I.date::timestamp)
        from inventory I
        where I.item_id = inventory.item_id
        )
    end difference
    from inventory
    where inventory.date::timestamp <= '2020-03-02T00:00:00'::timestamp
    group by inventory.item_id, difference
    ) as grouped_item;

/*
 <30 days | 30 days to 60 days exclusive | 60 days to 90 days exclusive | 90 days to 6 months exclusive | > 6 months 
----------+------------------------------+------------------------------+-------------------------------+------------
        1 |                            5 |                           15 |                             0 |          0

*/
user_91744310
Related