hello, i was trying to do a report that gets you the item name, item code and quantity sold from sales order.
this is my query , but the numbers i’m getting seems wrong to be honest too much high numbers for the qty.
in anyone knowledge will this query works as intended, this is the one i did.
SELECT `tabSales Order Item`.item_code as itemcode,
`tabSales Order Item`.item_name as itemname,
sum(`tabSales Order Item`.qty) as quantity
from `tabSales Order Item`,`tabSales Order`,`tabItem`
where `tabSales Order`.`status` != 'Cancelled' and `tabSales Order Item`.item_name=`tabItem`.item_name and `tabSales Order Item`.creation BETWEEN %(from_date)s and %(to_date)s
GROUP BY `tabItem`.item_name Order BY `tabItem`.item_name)
SELECT
`tabSales Order Item`.item_code as itemcode,
`tabSales Order Item`.item_name as itemname,
sum(`tabSales Order Item`.qty) as quantity
from `tabSales Order Item` /* ,`tabSales Order`,`tabItem` */
left join `tabSales Order` on `tabSales Order`.`name` = `tabSales Order Item`.`parent`
where
`tabSales Order`.`docstatus` = 1
and `tabSales Order`.`transaction_date` BETWEEN %(from_date)s AND %(to_date)s
GROUP BY `tabSales Order Item`.item_name
Order BY `tabSales Order Item`.item_name ASC
Thank you for your reply, now i understand what i’ve done wrong. Your query fixed it.
i’ve only changed tabSales Order.docstatus = 1
to tabSales Order.status != ‘Cancelled’
because i need the draft and the submitted except the cancelled one
i tried tabSales Order.docstatus = 1 or 0 (maybe i wrote it wrong as far as ik , 0=draft, 1=submitted and 2=cancelled.
but it gave me 33 instead of 9 for qty for a particular item which ik it’s only 9
when i used back this one tabSales Order.status != ‘Cancelled’ it returned 9 for qty.