SQL calculating stock per month

Question

I have specific task, and don't know how to realize it. I hope someone can help me =)

I have stock_move table:

product_id |location_id |location_dest_id |product_qty |date_expected       |
-----------|------------|-----------------|------------|--------------------|
327        |80          |84               |10          |2014-05-28 00:00:00 |
327        |80          |84               |10          |2014-05-23 00:00:00 |
327        |80          |84               |10          |2014-02-26 00:00:00 |
327        |80          |85               |10          |2014-02-21 00:00:00 |
327        |80          |84               |10          |2014-02-12 00:00:00 |
327        |84          |85               |20          |2014-02-06 00:00:00 |
322        |84          |80               |120         |2015-12-16 00:00:00 |
322        |80          |84               |30          |2015-12-10 00:00:00 |
322        |80          |84               |30          |2015-12-04 00:00:00 |
322        |80          |84               |15          |2015-11-26 00:00:00 |

i.e. it's table of product moves from one warehouse to second.

I can calculate stock at custom date if I use something like this:

select
    coalesce(si.product_id, so.product_id) as "Product",
    (coalesce(si.stock, 0) - coalesce(so.stock, 0)) as "Stock"
from
    (
        select
            product_id
            ,sum(product_qty * price_unit) as stock
        from stock_move
        where
            location_dest_id = 80
            and date_expected < now()
        group by product_id
    ) as si
    full outer join (
        select
            product_id
            ,sum(product_qty * price_unit) as stock
        from stock_move
        where
            location_id = 80
            and date_expected < now()
        group by product_id
    ) as so
    on si.product_id = so.product_id

Result I have current stock:

Product |Stock |
--------|------|
325     |1058  |
313     |34862 |
304     |2364  |

BUT what to do if I need stock per month? something like this?

Month   |Total Stock |
--------|------------|
Jan     |130238      |
Feb     |348262      |
Mar     |2323364     |

How can I sum product qty from start period to end of each month?

I have just one idea - it's use 24 sub queries for get stock per each month (ex. below)

Jan |Feb | Mar |
----|----|-----|
123 |234 |345  |

End after this rotate rows and columns? I think this's stupid, but I don't know another way... Help me pls =)

score 0 · Accepted Answer · answered Dec 24 '15 at 13:21

Something like this could give you monthly "ending" inventory snapshots. The trick is your data may omit certain months for certain parts, but that part will still have a balance (ie 50 received in January, nothing happened in February, but you still want to show February with a running total of 50).

One way to handle this is to come up with all possible combinations part/dates. I assumed 1/1/14 + 24 months in this example, but that's easily changed in the all_months subquery. For example, you may only want to start with the minimum date from the stock_move table.

with all_months as (
  select '2014-01-01'::date + interval '1 month' * generate_series(0, 23) as month_begin
),
stock_calc as (
  select
    product_id, date_expected,
    date_trunc ('month', date_expected)::date as month_expected,
    case
      when location_id = 80 then -product_qty * price_unit
      when location_dest_id = 80 then product_qty * price_unit
      else 0
    end as qty
  from stock_move
  union all
  select distinct
    s.product_id, m.month_begin::date, m.month_begin::date, 0
  from
    stock_move s
    cross join all_months m
),
running_totals as (
  select
    product_id, date_expected, month_expected,
    sum (qty) over (partition by product_id order by date_expected) as end_qty,
    row_number() over (partition by product_id, month_expected
      order by date_expected desc) as rn
  from stock_calc
)
select
  product_id, month_expected, end_qty
from running_totals
where
  rn = 1

oh my god... I need to try it =) after this, I say about result) — Yuri Shtrikker, Dec 24 '15 at 13:34
I realy don't understand how, but it's work. Big tnx for this, tomorrow I will sort out with this) — Yuri Shtrikker, Dec 24 '15 at 14:52
My recommendation is to run each subquery at a time. For example, after `stock_calc as ( ... )` just put `select * from stock_calc` to see what it yields. Then you can move down. I think if you see it run a block at a time it will make more sense. If you have questions, feel free to ask. — Hambone, Dec 24 '15 at 14:54
yeah, I do it =) on this time I see two new for me command (generate_series, case), realy tnx for this — Yuri Shtrikker, Dec 24 '15 at 15:05

SQL calculating stock per month

1 Answers1