Last active
March 31, 2018 05:30
-
-
Save somratcste/fa24332fb93c9a12d351b1e2a9a53b7d to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
select pfo.id as order_id, oli.id, oli.qty as required_qty, count(assigned_si.id) as assigned_qty, sum(olivs.confirmed_qty) as confirmed_qty | |
from ( | |
select * | |
from orders | |
where status = 5 | |
) as pfo | |
inner join order_line_items as oli on pfo.id = oli.order_id | |
left join ( | |
select * | |
from stock_items | |
where status = 3 | |
) as assigned_si on pfo.id = assigned_si.order_id and oli.stock_record_id = assigned_si.stock_record_id | |
left join ( | |
select order_line_item_id, confirmed_qty | |
from order_line_items_virtual_stocks olivs | |
left join purchase_orders as po on olivs.purchase_order_id = po.id | |
where olivs.purchase_order_id is null or po.status < 15 | |
) as olivs on oli.id = olivs.order_line_item_id | |
group by pfo.id, oli.id, oli.qty |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment