Select 
 base.[Item No_] as [Item No]
,base.[Active Number] AS [Active No]
,base.Brand
,base.[Source No_] AS [Source No]
,base.[Product Group Code]
,base.[Product group description]
,base.[Tariff No_] AS [Tarrif No]
,base.[Country_Region of Origin Code]
,base.[Net Weight]
,base.[Gross Weight]
,base.[Entry Type]
,base.[Document No_]
       
        
FROM (
SELECT 
cast(-il.Quantity as decimal(10,2)) as Quantity
,il.[Item No_]
,item.[No_ 2] as [Active Number]
,convert(date,il.[Posting Date]) as [Item Ledger Posting Date]
,item.[Product Group Code]
,IL.[Source No_]
,tProdGroup.[Description] as [Product group description]
,item.[Net Weight]
,item.[Gross Weight]
,item.[Tariff No_]
,CASE WHEN IL.[Entry Type] = 0 THEN 'Purchase' WHEN Il.[Entry Type] = 1 THEN 'Sale' END AS [Entry Type]
,Il.[Document No_]
,Item.[Country_Region of Origin Code]
,MT.Brand
        
FROM [MDR].[dbo].[nav_BG$Item_Ledger_Entry] AS IL (nolock)

--LEFT JOIN [MDR].[dbo].[nav_BG$Item_Update] AS IU (NOLOCK) ON IL.[Item No_]=IU.[Item No_] 
LEFT JOIN [MDR].[dbo].[nav_BG$Item] AS item (nolock) ON IL.[Item No_] = item.No_
LEFT JOIN [BGMDR].[dbo].[Y_ItemLedger_Manufacturers] AS MT (NOLOCK) ON item.[Manufacturer Code]=MT.KHCODE COLLATE Polish_100_CS_AS
LEFT JOIN [MDR].[dbo]. [nav_BG$Product_Group] AS tProdGroup (nolock) on item.[Product Group Code] = tProdGroup.[Code]
LEFT JOIN [MDR].[dbo].[nav_BG$Vendor] AS VEND (NOLOCK) ON Il.[Source No_]=VEND.No_

WHERE il.[Entry Type] = 0 and Il.[Posting Date] >= dateadd(day, -40, getdate()) AND VEND.[Vendor Posting Group]  IN ('INTEU','INTGR')
) base

GROUP BY 
         base.[Item No_]
        ,base.[Active Number]
        ,base.[Net Weight]
        ,base.[Gross Weight]
        ,base.[Product group description]
        ,base.[Product Group Code]
		,base.Brand
		,base.[Source No_]
		,base.[Tariff No_]
		,base.[Country_Region of Origin Code]
		,base.[Entry Type]
		,base.[Document No_]


UNION ALL 

Select 
 base.[Item No_] as [Item No]
,base.[Active Number] AS [Active No]
,base.Brand
,base.[Source No_] AS [Source No]
,base.[Product Group Code]
,base.[Product group description]
,base.[Tariff No_] AS [Tarrif No]
,base.[Country_Region of Origin Code]
,base.[Net Weight]
,base.[Gross Weight]
,base.[Entry Type]
,base.[Document No_]
       
        
FROM (
SELECT 
cast(-il.Quantity as decimal(10,2)) as Quantity
,il.[Item No_]
,item.[No_ 2] as [Active Number]
,convert(date,il.[Posting Date]) as [Item Ledger Posting Date]
,item.[Product Group Code]
,IL.[Source No_]
,tProdGroup.[Description] as [Product group description]
,item.[Net Weight]
,item.[Gross Weight]
,item.[Tariff No_]
,CASE WHEN IL.[Entry Type] = 0 THEN 'Purchase' WHEN Il.[Entry Type] = 1 THEN 'Sale' END AS [Entry Type]
,Il.[Document No_]
,Item.[Country_Region of Origin Code]
,MT.Brand
        
FROM [MDR].[dbo].[nav_BG$Item_Ledger_Entry] AS IL (nolock)

--LEFT JOIN [MDR].[dbo].[nav_BG$Item_Update] AS IU (NOLOCK) ON IL.[Item No_]=IU.[Item No_] 
LEFT JOIN [MDR].[dbo].[nav_BG$Item] AS item (nolock) ON IL.[Item No_] = item.No_
LEFT JOIN [BGMDR].[dbo].[Y_ItemLedger_Manufacturers] AS MT (NOLOCK) ON item.[Manufacturer Code]=MT.KHCODE COLLATE Polish_100_CS_AS
LEFT JOIN [MDR].[dbo]. [nav_BG$Product_Group] AS tProdGroup (nolock) on item.[Product Group Code] = tProdGroup.[Code]
LEFT JOIN [MDR].[dbo].[nav_BG$Customer] AS CUST (NOLOCK) ON Il.[Source No_]=Cust.No_

WHERE il.[Entry Type] = 1 and Il.[Posting Date] >= dateadd(day, -40, getdate()) AND Cust.[Customer Posting Group] IN ('INTEU','INTGR')
) base

GROUP BY 
         base.[Item No_]
        ,base.[Active Number]
        ,base.[Net Weight]
        ,base.[Gross Weight]
        ,base.[Product group description]
        ,base.[Product Group Code]
		,base.Brand
		,base.[Source No_]
		,base.[Tariff No_]
		,base.[Country_Region of Origin Code]
		,base.[Entry Type]
		,base.[Document No_]