Hi community, I’m creating a report for Supplier who sell item for me but I get stuck when I query all item from a Supplier was sold by POS, this is my script:
SELECT
`tabPOS Invoice Item`.item_code as itemcode,
`tabPOS Invoice Item`.item_name as itemname,
`tabPOS Invoice Item`.price_list_rate as price,
sum(`tabPOS Invoice Item`.qty) as quantity,
sum( `tabPOS Invoice Item`.base_amount) as total_price_item,
`tabPOS Invoice`.`posting_date` as date_sale,
`tabItem Supplier`.supplier as supplier_test,
if(`tabItem Supplier`.supplier is not null, `tabItem Supplier`.supplier , " " ) as supplier
from `tabPOS Invoice Item` /* ,`tabPOS Invoice`,`tabItem` */
left join `tabPOS Invoice` on `tabPOS Invoice`.`name` = `tabPOS Invoice Item`.`parent`
left join `tabItem Supplier` on `tabPOS Invoice Item`.item_name = `tabItem Supplier`.parent
where
`tabPOS Invoice`.status= "Paid"
and `tabPOS Invoice`.`posting_date` BETWEEN '%s' AND '%s'
GROUP BY `tabPOS Invoice Item`.item_name
Order BY `tabPOS Invoice Item`.item_name ASC
Some days I not found where I wrong, It’s working on my test but when I run it on live, empty result. Do anyone have experience for this report?