Hi guys im having a hard time on how to get the sum of a given date in TotalSold column. I have here 5 columns PartNumber, Desc, TotalOrder, TotalSold and POUnitPrice. How can i filter the date of TotalSold. Thanks!
select PartNumber = (select name from base_product as PROD where prod.prodid = POL.prodid),
TotalOrder = sum(POL.quantity),
TotalSold = (select sum(SOL.quantity) from so_salesorder_line as SOL where SOL.ProdId = POL.prodid),
PoUnitCost = max(POL.UnitPrice)
from PO_Purchaseorder_line as POL
where POL.purchaseorderid in (
from PO_purchaseorder as PO
where orderdate >= '5/1/2015')
group by POL.description, POL.prodid
so_salesorder so as to be able to access the date.
By the way, use ISO date literals instead of ambiguous dates strings. ('5/1/2015' could mean May 1 or January 5 depending on the current setting in your DBMS.)
Here is the part to get the TotalSold (for the 1st of May - change this to '20150105' if you want the 5th of January instead.)
TotalSold = ( select sum(SOL.quantity) from so_salesorder_line as SOL join so_salesorder SO on SO.salesorderid = SOL.salesorderid where SOL.ProdId = POL.prodid and SO.orderdate >= '20150501' ),