CODEWITHSUNDEEP
mysqldatabase
Christine
Christine

Reputation: 96

MS SQL getting the sum of given date

Hi guys im having a hard time on how to get the sum of a given date in TotalSold column. I have here PartNumber, TotalOrder, TotalSold and POUnitCost. How can i filter the date of TotalSold,since the date of it is located in another table named SO_SalesOrder. Thanks!

MyQuery:

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 (
    select purchaseorderid 
    from PO_purchaseorder as PO 
    where orderdate >= '5/1/2015')
group by POL.prodid

Upvotes: 0

Views: 123

Answers (3)

Christine
Christine

Reputation: 96

I revised it and got the correct result.

SELECT DISTINCT 
BASE_Product.NAME, 
BASE_Product.DESCRIPTION, 

TotalOrders = (Select(Select sum(quantity) from PO_PurchaseOrder_Line  as POL 
LEFT JOIN PO_PurchaseOrder PO ON POL.PurchaseOrderId = PO.PurchaseOrderId
        where POL.ProdId = BASE_Product.ProdId AND orderdate >= '20140101')),

PoPrice = (Select max(UnitPrice) from PO_PurchaseOrder_Line as POL where POL.ProdId = BASE_Product.ProdId ), 

PoExRate = (Select ExchangeRate from PO_PurchaseOrder where PO_PurchaseOrder.PurchaseOrderId = POL.PurchaseOrderId) ,

UnitCost = (POL.UnitPrice) * (Select Round(ExchangeRate, 4) from PO_PurchaseOrder where PO_PurchaseOrder.PurchaseOrderId = POL.PurchaseOrderId) ,

TotalSold = (SELECT SUM(SOL.quantity) FROM so_salesorder_line AS SOL 
   LEFT JOIN so_salesorder SO ON SO.salesorderid = SOL.salesorderid
   WHERE SOL.ProdId = BASE_Product.prodid AND SO.orderdate >= '20150101') 

FROM BASE_Product

FULL JOIN PO_PurchaseOrder_Line as POL ON BASE_Product.ProdId = POL.ProdId

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Join so_salesorder_line with 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'
),

Upvotes: 1

Djacks007
Djacks007

Reputation: 11

I think working with joins is much easier and clearer. Try this one:

    select po.orderdate, POL.description, 
    prod.name as PartNumber 
    sum(POL.quantity) as TotalOrder, 
    sum(SOL.quantity) as TotalSold, 
    max(POL.UnitPrice) as PoUnitCost                     
    from PO_Purchaseorder_line as POL
    left join so_salesorder_line as SOL on SOL.ProdId = POL.prodid
    left join PO_purchaseorder as PO on POL.purchaseorderid=PO.purchaseorderid
    left join base_product as PROD on prod.prodid = POL.prodid
    where po.orderdate >= '5/1/2015'
    group by po.orderdate, POL.prodid,prod.name

Upvotes: 1

Related Questions