ERPNext.com Frappe Cloud Support Partners Foundation Frappe School

Report for Item Selling of Supplier

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?