I NEED PROFESSIONAL ONE TO SOLVE THIS PROBLEM
WHY THE RESULT OF QTY INCORECT ?
res = frappe.db.sql(f"""SELECT
w.custom_location_id as locationID,
sii.warehouse as warehouse,
sii.item_code as item_code,
sii.qty,
sli.actual_qty
FROM (
SELECT
parent,
item_group,
warehouse as warehouse,
item_code as item_code,
SUM(qty) AS qty
from `tabSales Invoice Item`
group by item_code,warehouse
) sii
LEFT JOIN (
SELECT
warehouse as warehouse,
item_code as item_code,
SUM(actual_qty) AS actual_qty
FROM `tabStock Ledger Entry`
group by item_code,warehouse
) sli
on sli.item_code = sii.item_code
INNER JOIN (
SELECT
name,
status,
posting_date,
custom_business_type
from `tabSales Invoice`
) si
on sii.parent = si.name
inner join `tabWarehouse` w on w.name = sii.warehouse
where
si.status != 'Cancelled'
AND si.status != 'Draft'
{sql_date_cond}
group by sii.warehouse,sii.item_code
""", as_dict=True)
result = res