Share via

SUM and JOIN 2 columns with different tables but did not SUM Qty

glennyboy 121 Reputation points
2020-09-10T09:38:35.75+00:00

Good day!

Need help re SUM and JOIN 2 columns with different tables but did not SUM qty and duplicates row result

here's the sample data tables;

23831-image.png

23796-image.png

23638-image.png

here's my updated sql query code;

;WITH SumarisedTransfers AS  
	(  
		SELECT  
		[BBI$Transfer Shipment Line].[Transfer-to Code] AS [Location]  
		,[BBI$Transfer Shipment Line].[Item No_] AS [Item No]  
		,MAX([BBI$Transfer Shipment Line].[Description]) AS [Description]  
		,MAX([BBI$Transfer Shipment Line].[Description 2]) AS [Description 2]  
		,[BBI$Transfer Shipment Line].[Variant Code]  
		,SUM([BBI$Transfer Shipment Line].[Quantity]) AS [Delivery]  
		,MAX([BBI$Transfer Shipment Line].[Unit of Measure Code]) AS [Unit]  
		,MAX([BBI$Transfer Shipment Line].[Division]) AS [Brand]   
		,MAX([BBI$Transfer Shipment Line].[Item Category Code]) AS [Item Cat]  
		,MAX([BBI$Transfer Shipment Line].[Product Group Code]) AS [Product Group]  
		FROM   
		[BBI$Transfer Shipment Line]  
		GROUP BY  
		[BBI$Transfer Shipment Line].[Transfer-to Code]  
		,[BBI$Transfer Shipment Line].[Item No_]  
		,[BBI$Transfer Shipment Line].[Variant Code]  
	),  
	Transfersfrom AS  
		(  
		SELECT  
		[BBI$Transfer Shipment Line].[Transfer-from Code] AS [Locationfrom]  
		,[BBI$Transfer Shipment Line].[Item No_]  
		,MAX([BBI$Transfer Shipment Line].[Description]) AS [Description]  
		,MAX([BBI$Transfer Shipment Line].[Description 2]) AS [Description 2]  
		,[BBI$Transfer Shipment Line].[Variant Code]  
		,SUM([BBI$Transfer Shipment Line].[Quantity]) AS [Pull-out]  
		,MAX([BBI$Transfer Shipment Line].[Unit of Measure Code]) AS [Unit]  
		,MAX([BBI$Transfer Shipment Line].[Division]) AS [Brand]   
		,MAX([BBI$Transfer Shipment Line].[Item Category Code]) AS [Item Cat]  
		,MAX([BBI$Transfer Shipment Line].[Product Group Code]) AS [Product Group]  
		FROM   
		[BBI$Transfer Shipment Line]  
		GROUP BY  
		[BBI$Transfer Shipment Line].[Transfer-from Code]  
		,[BBI$Transfer Shipment Line].[Item No_]  
		,[BBI$Transfer Shipment Line].[Variant Code]  
	),  
	SumarisedSales AS  
	(  
		SELECT  
		[BBI$Trans_ Sales Entry].[Store No_]  
		,[BBI$Trans_ Sales Entry].[Item No_]  
		,[BBI$Trans_ Sales Entry].[Variant Code]  
		,SUM([BBI$Trans_ Sales Entry].[Quantity]) AS [Sales Entry]  
		FROM  
		[BBI$Trans_ Sales Entry]  
		GROUP BY  
		[BBI$Trans_ Sales Entry].[Store No_]  
		,[BBI$Trans_ Sales Entry].[Item No_]  
		,[BBI$Trans_ Sales Entry].[Variant Code]  
	),  
	Location AS  
    (  
		SELECT  
		[BBI$Location].Name AS [Name]  
		FROM [BBI$Location]  
	),  
	ItemLedgerMSales AS  
	(  
		SELECT  
		[BBI$Item Ledger Entry].[Location Code]  
		,[BBI$Item Ledger Entry].[Item No_]  
		,[BBI$Item Ledger Entry].[Variant Code]  
		,SUM([BBI$Item Ledger Entry].[Quantity]) AS [MSales]  
		FROM  
		[BBI$Item Ledger Entry]  
		GROUP BY  
		[BBI$Item Ledger Entry].[Location Code]  
		,[BBI$Item Ledger Entry].[Item No_]  
		,[BBI$Item Ledger Entry].[Variant Code]  
	),  
		ItemLedgerPAdj AS  
	(  
		SELECT  
		[BBI$Item Ledger Entry].[Location Code]  
		,[BBI$Item Ledger Entry].[Item No_]  
		,[BBI$Item Ledger Entry].[Variant Code]  
		,SUM([BBI$Item Ledger Entry].[Quantity]) AS [PAdj]  
		FROM  
		[BBI$Item Ledger Entry]  
		GROUP BY  
		[BBI$Item Ledger Entry].[Location Code]  
		,[BBI$Item Ledger Entry].[Item No_]  
		,[BBI$Item Ledger Entry].[Variant Code]  
	),  
			ItemLedgerNAdj AS  
	(  
		SELECT  
		[BBI$Item Ledger Entry].[Location Code]  
		,[BBI$Item Ledger Entry].[Item No_]  
		,[BBI$Item Ledger Entry].[Variant Code]  
		,SUM([BBI$Item Ledger Entry].[Quantity]) AS [NAdj]  
		FROM  
		[BBI$Item Ledger Entry]  
		GROUP BY  
		[BBI$Item Ledger Entry].[Location Code]  
		,[BBI$Item Ledger Entry].[Item No_]  
		,[BBI$Item Ledger Entry].[Variant Code]  
	)  
  
     ,CTE AS (    
      SELECT  DISTINCT ST.[Location],  
               ST.[Item No],  
               ST.[Description],  
               ST.[Description 2],  
              ST.[Variant Code],  
              ST.[Delivery],  
              ISNULL(PADJ.[Quantity],0) AS [PAdj],  
              ISNULL(TF.[Pull-out],0) AS [Pull-out],  
              ISNULL(SS.[Sales Entry],0) AS [PSales],  
              ISNULL(MS.[Quantity],0) AS [MSales]  
              
      FROM [SumarisedTransfers] ST  
              
      LEFT JOIN [Transfersfrom] TF  
      ON [ST].[Location] = [TF].[Locationfrom]  
      AND [ST].[Item No] = [TF].[Item No_]  
      AND [ST].[Variant Code] = [TF].[Variant Code]  
              
      LEFT JOIN [SumarisedSales] SS  
      ON [SS].[Store No_] = [ST].[Location]  
      AND [SS].[Item No_] = [ST].[Item No]  
      AND [SS].[Variant Code] = [ST].[Variant Code]  
              
      LEFT JOIN [BBI$Item Ledger Entry] MS  
      ON [ST].[Location] = [MS].[Location Code]  
      AND [ST].[Item No] = [MS].[Item No_]  
      AND [ST].[Variant Code] = [MS].[Variant Code]  
              
      LEFT JOIN [BBI$Item Ledger Entry] PADJ  
      ON [ST].[Location] = [PADJ].[Location Code]  
      AND [ST].[Item No] = [PADJ].[Item No_]  
      AND [ST].[Variant Code] = [PADJ].[Variant Code]  
  
	  LEFT JOIN [BBI$Item Ledger Entry] NADJ  
      ON [ST].[Location] = [NADJ].[Location Code]  
      AND [ST].[Item No] = [NADJ].[Item No_]  
      AND [ST].[Variant Code] = [NADJ].[Variant Code]  
              
      WHERE  
      ST.[Location]='LOCATION1' AND  
     ST.[Item No]='ITEM01' AND  
     ST.[Variant Code]='VARIANT01' AND  
      MS.[Entry Type]='1' AND  
      MS.[Document No_] LIKE 'MSales%' AND  
      PADJ.[Entry Type]='2')  
          
      ,CTE1 AS(  
      SELECT Location,[Item No],Description,[Description 2],[Variant Code],Delivery,  
      SUM(PAdj) PAdj,[Pull-out],PSales,MSales     
      FROM CTE  
      GROUP BY Location,[Item No],Description,[Description 2],[Variant Code],Delivery,[Pull-out],PSales,MSales )  
          
      SELECT *,[Delivery]+[PAdj]-[Pull-out]+PSales+[MSales] AS Total   
      FROM CTE1

but the sql query did not sum qty of MSales column;

24037-image.png

query issues;

  • MSales column;
    • did not sum qty, but did not duplicate result

if i remove the WHERE filter ST.[Item No], ST.[Variant Code] to show all the ITEMS and VARIANT from LOCATION01

the query results shows only all ITEMS and VARIANT with MSales entry and PAdj entry, it should show all ITEMS and VARIANT even if there is no MSales entry and PAdj entry

Thank you

Developer technologies Transact-SQL
0 comments
Accepted answer
  1. MelissaMa-MSFT 24,221 Reputation points
    2020-09-11T02:46:20.603+00:00

    Hi @glennyboy ,

    Please have a try to replace last part with below: 

    ,CTE AS (    
 SELECT  DISTINCT   ST.[Location],  
          ST.[Item No],  
          ST.[Description],  
          ST.[Description 2],  
         ST.[Variant Code],  
         ST.[Delivery],  
         ISNULL(PADJ.[Quantity],0) AS [PAdj],  
         ISNULL(TF.[Pull-out],0) AS [Pull-out],  
         ISNULL(SS.[Sales Entry],0) AS [PSales],  
         ISNULL(MS.[Quantity],0) AS [MSales]  
      
 FROM [SumarisedTransfers] ST  
      
 LEFT JOIN [Transfersfrom] TF  
 ON [ST].[Location] = [TF].[Locationfrom]  
 AND [ST].[Item No] = [TF].[Item No_]  
 AND [ST].[Variant Code] = [TF].[Variant Code]  
      
 LEFT JOIN [SumarisedSales] SS  
 ON [SS].[Store No_] = [ST].[Location]  
 AND [SS].[Item No_] = [ST].[Item No]  
 AND [SS].[Variant Code] = [ST].[Variant Code]  
      
 LEFT JOIN [BBI$Item Ledger Entry] MS  
 ON [ST].[Location] = [MS].[Location Code]  
 AND [ST].[Item No] = [MS].[Item No_]  
 AND [ST].[Variant Code] = [MS].[Variant Code]  
      
 LEFT JOIN [BBI$Item Ledger Entry] PADJ  
 ON [ST].[Location] = [PADJ].[Location Code]  
 AND [ST].[Item No] = [PADJ].[Item No_]  
 AND [ST].[Variant Code] = [PADJ].[Variant Code]  
      
 WHERE  
 ST.[Location]='LOCATION1' AND  
 ST.[Item No]='ITEM01' AND  
 ST.[Variant Code]='VARIANT01' AND  
 MS.[Entry Type]='1' AND  
 MS.[Document No_] LIKE 'MSales%' AND  
 PADJ.[Entry Type]='2')  
  
 ,CTE1 AS(  
 SELECT Location,[Item No],Description,[Description 2],[Variant Code],Delivery,  
 SUM(PAdj) PAdj,[Pull-out],PSales,MSales     
 FROM CTE  
 GROUP BY Location,[Item No],Description,[Description 2],[Variant Code],Delivery,[Pull-out],PSales,MSales )  
  
 SELECT *,[Delivery]+[PAdj]-[Pull-out]+PSales+[MSales] AS Total   
 FROM CTE1

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 47,436 Reputation points
    2020-09-10T10:08:57.557+00:00

    but the sql query did not sum qty of PAdj column and duplicates result;

    Not a big surprise, you have to aggregate the data using GROUP BY clause and a SUM function

    0 comments
  2. MelissaMa-MSFT 24,221 Reputation points
    2020-09-11T09:38:11.913+00:00

    Hi @glennyboy ,

    Please refer below updated query and check whether it is working: 

    ;WITH SumarisedTransfers AS  
     (  
         SELECT  
         [BBI$Transfer Shipment Line].[Transfer-to Code] AS [Location]  
         ,[BBI$Transfer Shipment Line].[Item No_] AS [Item No]  
         ,MAX([BBI$Transfer Shipment Line].[Description]) AS [Description]  
         ,MAX([BBI$Transfer Shipment Line].[Description 2]) AS [Description 2]  
         ,[BBI$Transfer Shipment Line].[Variant Code]  
         ,SUM([BBI$Transfer Shipment Line].[Quantity]) AS [Delivery]  
         ,MAX([BBI$Transfer Shipment Line].[Unit of Measure Code]) AS [Unit]  
         ,MAX([BBI$Transfer Shipment Line].[Division]) AS [Brand]   
         ,MAX([BBI$Transfer Shipment Line].[Item Category Code]) AS [Item Cat]  
         ,MAX([BBI$Transfer Shipment Line].[Product Group Code]) AS [Product Group]  
         FROM   
         [BBI$Transfer Shipment Line]  
         GROUP BY  
         [BBI$Transfer Shipment Line].[Transfer-to Code]  
         ,[BBI$Transfer Shipment Line].[Item No_]  
         ,[BBI$Transfer Shipment Line].[Variant Code]  
     ),  
     Transfersfrom AS  
         (  
         SELECT  
         [BBI$Transfer Shipment Line].[Transfer-from Code] AS [Locationfrom]  
         ,[BBI$Transfer Shipment Line].[Item No_]  
         ,MAX([BBI$Transfer Shipment Line].[Description]) AS [Description]  
         ,MAX([BBI$Transfer Shipment Line].[Description 2]) AS [Description 2]  
         ,[BBI$Transfer Shipment Line].[Variant Code]  
         ,SUM([BBI$Transfer Shipment Line].[Quantity]) AS [Pull-out]  
         ,MAX([BBI$Transfer Shipment Line].[Unit of Measure Code]) AS [Unit]  
         ,MAX([BBI$Transfer Shipment Line].[Division]) AS [Brand]   
         ,MAX([BBI$Transfer Shipment Line].[Item Category Code]) AS [Item Cat]  
         ,MAX([BBI$Transfer Shipment Line].[Product Group Code]) AS [Product Group]  
         FROM   
         [BBI$Transfer Shipment Line]  
         GROUP BY  
         [BBI$Transfer Shipment Line].[Transfer-from Code]  
         ,[BBI$Transfer Shipment Line].[Item No_]  
         ,[BBI$Transfer Shipment Line].[Variant Code]  
     ),  
     SumarisedSales AS  
     (  
         SELECT  
         [BBI$Trans_ Sales Entry].[Store No_]  
         ,[BBI$Trans_ Sales Entry].[Item No_]  
         ,[BBI$Trans_ Sales Entry].[Variant Code]  
         ,SUM(DISTINCT [BBI$Trans_ Sales Entry].[Quantity]) AS [Sales Entry]  
         FROM  
         [BBI$Trans_ Sales Entry]  
         GROUP BY  
         [BBI$Trans_ Sales Entry].[Store No_]  
         ,[BBI$Trans_ Sales Entry].[Item No_]  
         ,[BBI$Trans_ Sales Entry].[Variant Code]  
     ),  
     Location AS  
     (  
         SELECT  
         [BBI$Location].Name AS [Name]  
         FROM [BBI$Location]  
     ),  
     ItemLedgerMSales AS  
     (  
         SELECT  
         [BBI$Item Ledger Entry].[Location Code]  
         ,[BBI$Item Ledger Entry].[Item No_]  
         ,[BBI$Item Ledger Entry].[Variant Code]  
         ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [MSales]  
         FROM  
         [BBI$Item Ledger Entry]  
         GROUP BY  
         [BBI$Item Ledger Entry].[Location Code]  
         ,[BBI$Item Ledger Entry].[Item No_]  
         ,[BBI$Item Ledger Entry].[Variant Code]  
     ),  
         ItemLedgerPAdj AS  
     (  
         SELECT  
         [BBI$Item Ledger Entry].[Location Code]  
         ,[BBI$Item Ledger Entry].[Item No_]  
         ,[BBI$Item Ledger Entry].[Variant Code]  
         ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [PAdj]  
         FROM  
         [BBI$Item Ledger Entry]  
         GROUP BY  
         [BBI$Item Ledger Entry].[Location Code]  
         ,[BBI$Item Ledger Entry].[Item No_]  
         ,[BBI$Item Ledger Entry].[Variant Code]  
     ),  
             ItemLedgerNAdj AS  
     (  
         SELECT  
         [BBI$Item Ledger Entry].[Location Code]  
         ,[BBI$Item Ledger Entry].[Item No_]  
         ,[BBI$Item Ledger Entry].[Variant Code]  
         ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [NAdj]  
         FROM  
         [BBI$Item Ledger Entry]  
         GROUP BY  
         [BBI$Item Ledger Entry].[Location Code]  
         ,[BBI$Item Ledger Entry].[Item No_]  
         ,[BBI$Item Ledger Entry].[Variant Code]  
     )  
      
      ,CTE AS (    
       SELECT  DISTINCT ST.[Location],  
                ST.[Item No],  
                ST.[Description],  
                ST.[Description 2],  
               ST.[Variant Code],  
               ST.[Delivery],  
               ISNULL(PADJ.[Quantity],0) AS [PAdj],  
               ISNULL(TF.[Pull-out],0) AS [Pull-out],  
               ISNULL(SS.[Sales Entry],0) AS [PSales],  
               ISNULL(MS.[Quantity],0) AS [MSales]  
                  
       FROM [SumarisedTransfers] ST  
                  
       LEFT JOIN [Transfersfrom] TF  
       ON [ST].[Location] = [TF].[Locationfrom]  
       AND [ST].[Item No] = [TF].[Item No_]  
       AND [ST].[Variant Code] = [TF].[Variant Code]  
                  
       LEFT JOIN [SumarisedSales] SS  
       ON [SS].[Store No_] = [ST].[Location]  
       AND [SS].[Item No_] = [ST].[Item No]  
       AND [SS].[Variant Code] = [ST].[Variant Code]  
                  
       LEFT JOIN [BBI$Item Ledger Entry] MS  
       ON [ST].[Location] = [MS].[Location Code]  
       AND [ST].[Item No] = [MS].[Item No_]  
       AND [ST].[Variant Code] = [MS].[Variant Code]  
                  
       LEFT JOIN [BBI$Item Ledger Entry] PADJ  
       ON [ST].[Location] = [PADJ].[Location Code]  
       AND [ST].[Item No] = [PADJ].[Item No_]  
       AND [ST].[Variant Code] = [PADJ].[Variant Code]  
      
       LEFT JOIN [BBI$Item Ledger Entry] NADJ  
       ON [ST].[Location] = [NADJ].[Location Code]  
       AND [ST].[Item No] = [NADJ].[Item No_]  
       AND [ST].[Variant Code] = [NADJ].[Variant Code]  
                  
       WHERE  
       ST.[Location]='LOCATION1' AND  
      ST.[Item No]='ITEM01' AND  
      ST.[Variant Code]='VARIANT01' AND  
       MS.[Entry Type]='1' AND  
       MS.[Document No_] LIKE 'MSales%' AND  
       PADJ.[Entry Type]='2')  
              
       ,CTE1 AS(  
       SELECT Location,[Item No],Description,[Description 2],[Variant Code],Delivery,  
       SUM(PAdj) PAdj,[Pull-out],SUM(PSales) PSales,SUM(MSales) MSales    
       FROM CTE  
       GROUP BY Location,[Item No],Description,[Description 2],[Variant Code],Delivery,[Pull-out] )  
          
       SELECT *,[Delivery]+[PAdj]-[Pull-out]+PSales+[MSales] AS Total   
       FROM CTE1

    Output: 

    Location	Item No	Description	Description 2	Variant Code	Delivery	PAdj	Pull-out	PSales	MSales	Total  
Location1	Item01	Active	Black	Variant01	10	5	3	-2	-2	8

    If you also want to add column, please post a new one with all details. Thanks.

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  3. MelissaMa-MSFT 24,221 Reputation points
    2020-09-11T09:42:31.42+00:00

    Hi @glennyboy ,

    If above query is not working , please try with below query: 

    ;WITH SumarisedTransfers AS  
     (  
         SELECT  
         [BBI$Transfer Shipment Line].[Transfer-to Code] AS [Location]  
         ,[BBI$Transfer Shipment Line].[Item No_] AS [Item No]  
         ,MAX([BBI$Transfer Shipment Line].[Description]) AS [Description]  
         ,MAX([BBI$Transfer Shipment Line].[Description 2]) AS [Description 2]  
         ,[BBI$Transfer Shipment Line].[Variant Code]  
         ,SUM([BBI$Transfer Shipment Line].[Quantity]) AS [Delivery]  
         ,MAX([BBI$Transfer Shipment Line].[Unit of Measure Code]) AS [Unit]  
         ,MAX([BBI$Transfer Shipment Line].[Division]) AS [Brand]   
         ,MAX([BBI$Transfer Shipment Line].[Item Category Code]) AS [Item Cat]  
         ,MAX([BBI$Transfer Shipment Line].[Product Group Code]) AS [Product Group]  
         FROM   
         [BBI$Transfer Shipment Line]  
         GROUP BY  
         [BBI$Transfer Shipment Line].[Transfer-to Code]  
         ,[BBI$Transfer Shipment Line].[Item No_]  
         ,[BBI$Transfer Shipment Line].[Variant Code]  
     ),  
     Transfersfrom AS  
         (  
         SELECT  
         [BBI$Transfer Shipment Line].[Transfer-from Code] AS [Locationfrom]  
         ,[BBI$Transfer Shipment Line].[Item No_]  
         ,MAX([BBI$Transfer Shipment Line].[Description]) AS [Description]  
         ,MAX([BBI$Transfer Shipment Line].[Description 2]) AS [Description 2]  
         ,[BBI$Transfer Shipment Line].[Variant Code]  
         ,SUM([BBI$Transfer Shipment Line].[Quantity]) AS [Pull-out]  
         ,MAX([BBI$Transfer Shipment Line].[Unit of Measure Code]) AS [Unit]  
         ,MAX([BBI$Transfer Shipment Line].[Division]) AS [Brand]   
         ,MAX([BBI$Transfer Shipment Line].[Item Category Code]) AS [Item Cat]  
         ,MAX([BBI$Transfer Shipment Line].[Product Group Code]) AS [Product Group]  
         FROM   
         [BBI$Transfer Shipment Line]  
         GROUP BY  
         [BBI$Transfer Shipment Line].[Transfer-from Code]  
         ,[BBI$Transfer Shipment Line].[Item No_]  
         ,[BBI$Transfer Shipment Line].[Variant Code]  
     ),  
     SumarisedSales AS  
     (  
         SELECT  
         [BBI$Trans_ Sales Entry].[Store No_]  
         ,[BBI$Trans_ Sales Entry].[Item No_]  
         ,[BBI$Trans_ Sales Entry].[Variant Code]  
         ,SUM([BBI$Trans_ Sales Entry].[Quantity]) AS [Sales Entry]  
         FROM  
         [BBI$Trans_ Sales Entry]  
         GROUP BY  
         [BBI$Trans_ Sales Entry].[Store No_]  
         ,[BBI$Trans_ Sales Entry].[Item No_]  
         ,[BBI$Trans_ Sales Entry].[Variant Code]  
     ),  
     Location AS  
     (  
         SELECT  
         [BBI$Location].Name AS [Name]  
         FROM [BBI$Location]  
     ),  
     ItemLedgerMSales AS  
     (  
         SELECT  
         [BBI$Item Ledger Entry].[Location Code]  
         ,[BBI$Item Ledger Entry].[Item No_]  
         ,[BBI$Item Ledger Entry].[Variant Code]  
         ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [MSales]  
         FROM  
         [BBI$Item Ledger Entry]  
         GROUP BY  
         [BBI$Item Ledger Entry].[Location Code]  
         ,[BBI$Item Ledger Entry].[Item No_]  
         ,[BBI$Item Ledger Entry].[Variant Code]  
     ),  
         ItemLedgerPAdj AS  
     (  
         SELECT  
         [BBI$Item Ledger Entry].[Location Code]  
         ,[BBI$Item Ledger Entry].[Item No_]  
         ,[BBI$Item Ledger Entry].[Variant Code]  
         ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [PAdj]  
         FROM  
         [BBI$Item Ledger Entry]  
         GROUP BY  
         [BBI$Item Ledger Entry].[Location Code]  
         ,[BBI$Item Ledger Entry].[Item No_]  
         ,[BBI$Item Ledger Entry].[Variant Code]  
     ),  
             ItemLedgerNAdj AS  
     (  
         SELECT  
         [BBI$Item Ledger Entry].[Location Code]  
         ,[BBI$Item Ledger Entry].[Item No_]  
         ,[BBI$Item Ledger Entry].[Variant Code]  
         ,SUM([BBI$Item Ledger Entry].[Quantity]) AS [NAdj]  
         FROM  
         [BBI$Item Ledger Entry]  
         GROUP BY  
         [BBI$Item Ledger Entry].[Location Code]  
         ,[BBI$Item Ledger Entry].[Item No_]  
         ,[BBI$Item Ledger Entry].[Variant Code]  
     )  
      
      ,CTE AS (    
       SELECT  DISTINCT ST.[Location],  
                ST.[Item No],  
                ST.[Description],  
                ST.[Description 2],  
               ST.[Variant Code],  
               ST.[Delivery],  
               ISNULL(PADJ.[Quantity],0) AS [PAdj],  
               ISNULL(TF.[Pull-out],0) AS [Pull-out],  
               ISNULL(SS.[Sales Entry],0) AS [PSales],  
               ISNULL(MS.[Quantity],0) AS [MSales]  
                  
       FROM [SumarisedTransfers] ST  
                  
       LEFT JOIN [Transfersfrom] TF  
       ON [ST].[Location] = [TF].[Locationfrom]  
       AND [ST].[Item No] = [TF].[Item No_]  
       AND [ST].[Variant Code] = [TF].[Variant Code]  
                  
       LEFT JOIN [SumarisedSales] SS  
       ON [SS].[Store No_] = [ST].[Location]  
       AND [SS].[Item No_] = [ST].[Item No]  
       AND [SS].[Variant Code] = [ST].[Variant Code]  
                  
       LEFT JOIN [BBI$Item Ledger Entry] MS  
       ON [ST].[Location] = [MS].[Location Code]  
       AND [ST].[Item No] = [MS].[Item No_]  
       AND [ST].[Variant Code] = [MS].[Variant Code]  
                  
       LEFT JOIN [BBI$Item Ledger Entry] PADJ  
       ON [ST].[Location] = [PADJ].[Location Code]  
       AND [ST].[Item No] = [PADJ].[Item No_]  
       AND [ST].[Variant Code] = [PADJ].[Variant Code]  
      
       LEFT JOIN [BBI$Item Ledger Entry] NADJ  
       ON [ST].[Location] = [NADJ].[Location Code]  
       AND [ST].[Item No] = [NADJ].[Item No_]  
       AND [ST].[Variant Code] = [NADJ].[Variant Code]  
                  
       WHERE  
       ST.[Location]='LOCATION1' AND  
      ST.[Item No]='ITEM01' AND  
      ST.[Variant Code]='VARIANT01' AND  
       MS.[Entry Type]='1' AND  
       MS.[Document No_] LIKE 'MSales%' AND  
       PADJ.[Entry Type]='2')  
              
       ,CTE1 AS(  
       SELECT Location,[Item No],Description,[Description 2],[Variant Code],Delivery,  
       SUM(PAdj) PAdj,[Pull-out],  PSales ,SUM(MSales) MSales    
       FROM CTE  
       GROUP BY Location,[Item No],Description,[Description 2],[Variant Code],Delivery,[Pull-out],PSales )  
          
       SELECT *,[Delivery]+[PAdj]-[Pull-out]+PSales+[MSales] AS Total   
       FROM CTE1

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.