Trying to get total sales for product within Purchase Order

Transact-SQL
1

I'm trying to return total YTD sales invoiced of product listed on a Purchase order . The values that are being returned are not YTD and i'm not sure im going around it the write way any help greatly appreciated

>   SalesInvoiceItems.Quantity AS totalunitsSold ,
>    Products.ProductId,
>     Products.ProductDescription,
> 	 PurchaseOrders.PurchaseOrderNumber,
> 	  PurchaseOrderItems.Quantity AS PurchasedQty
> FROM            SalesInvoiceItems INNER JOIN
>                          Products ON SalesInvoiceItems.Product = Products.Product INNER JOIN
>                          PurchaseOrderItems ON Products.Product = PurchaseOrderItems.Product INNER JOIN
>                          PurchaseOrders ON PurchaseOrderItems.PurchaseOrder = PurchaseOrders.PurchaseOrder
> 						  WHERE PurchaseOrderNumber = '0000006547'
3

You'll probably need to join SalesInvoice (if you have such at table) where you'll most likely find a salesdate to filter on.

4

Hi ,Many thanks got that working now I'm stuck on a grouping . there are 2 of the same products on the PO so it returns 2 times YTD sales

SELECT    

 SUM (SalesInvoiceItems.Quantity) AS totalunitsSold ,
 MAX (  Products.ProductId) AS ID,
   MAX( Products.ProductDescription) AS Descr,
	
	  PurchaseOrderItems.Quantity AS PurchasedQty

FROM            SalesInvoiceItems INNER JOIN
                         Products ON SalesInvoiceItems.Product = Products.Product INNER JOIN
                         PurchaseOrderItems ON Products.Product = PurchaseOrderItems.Product INNER JOIN
                         PurchaseOrders ON PurchaseOrderItems.PurchaseOrder = PurchaseOrders.PurchaseOrder
						  WHERE PurchaseOrderNumber = '0000006472'


						 Group By   
  
	PurchaseOrderItems.Quantity

I just dont seem to be able to group down UAS3UKO50008 - Probably me being Stupid

totalunitsSold ID Descr PurchasedQty
499.00000 UAS3UKO50008 Watch Tree 29.00000
8.00000 CAH3IKO20402 Campbell World Traveller clock - DELIVERY MID JUNE 200.00000
1.00000 CAH3GKO20403 Campbell Metropolitan clock - DELIVERY MID JUNE 300.00000
499.00000 UAS3UKO50008 Watch Tree 371.00000
5

Without ddl and sample data, I'm guessing here: shouldn't you group by productid?

6

You would of thought so but it just sticks there

7

Are you writing that this:

SELECT SUM(SalesInvoiceItems.Quantity) AS totalunitsSold
      ,Products.ProductId AS ID
      ,MAX(Products.ProductDescription) AS Descr
      ,SUM(PurchaseOrderItems.Quantity) AS PurchasedQty
  FROM SalesInvoiceItems
       INNER JOIN Products
               ON Products.Product=SalesInvoiceItems.Product
       INNER JOIN PurchaseOrderItems
               ON PurchaseOrderItems.Product=Products.Product
       INNER JOIN PurchaseOrders
               ON PurchaseOrders.PurchaseOrder=PurchaseOrderItems.PurchaseOrder
 WHERE PurchaseOrderNumber='0000006472'
 GROUP BY Products.ProductId
;

results in 2 rows with UAS3UKO50008?

8

HI

Yes , another permutations , It currently gives me this result

totalunitsSold ID Descr PurchasedQty
499.00000 UAS3UKO50008 Watch Tree 29.00000
8.00000 CAH3IKO20402 Campbell World Traveller clock - DELIVERY MID JUNE 200.00000
1.00000 CAH3GKO20403 Campbell Metropolitan clock - DELIVERY MID JUNE 300.00000
499.00000 UAS3UKO50008 Watch Tree 371.00000

But I want it to give

499.00000 UAS3UKO50008 Watch Tree 400

9

When you group by productid only, you cannot get multiple rows with the same productid, so it must be you "permutation" that causes this.