Index: dbo/Stored Procedures/spCorporatePerformance.sql =================================================================== diff -u -r6ab81f0f6d43c10733b6cf464372b9511eb52f1a -r022b45e50704424c78460a0102323a03b1920e61 --- dbo/Stored Procedures/spCorporatePerformance.sql (.../spCorporatePerformance.sql) (revision 6ab81f0f6d43c10733b6cf464372b9511eb52f1a) +++ dbo/Stored Procedures/spCorporatePerformance.sql (.../spCorporatePerformance.sql) (revision 022b45e50704424c78460a0102323a03b1920e61) @@ -1,8 +1,9 @@  CREATE PROCEDURE [dbo].[spCorporatePerformance] - @Buyer VARCHAR(MAX) AS - + @Buyer VARCHAR(MAX), + @ItemStatus VARCHAR(10) AS +BEGIN SET NOCOUNT ON -- 19,512 EXEC spCorporatePerformance '0,10,11,12,14,15,17,18,19,2,20,21,22,3,5,6,7,9,A,B,D,E,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,W,X,Y,Z' @@ -13,177 +14,178 @@ , @sales_start_date_id INT -- Query runs quicker with this as a starting point - SELECT @inventory_date_id = CONVERT(CHAR(8), DATEADD(mm, -1, DATEADD(dd, 1, MAX(CalendarDate))), 112) - , @sales_start_date_id = MIN(DATEID) - FROM dbo.dimDate - WHERE IsRolling24 = 1 + SELECT @inventory_date_id = CONVERT(CHAR(8), DATEADD(mm, -1, DATEADD(dd, 1, MAX(CalendarDate))), 112) + , @sales_start_date_id = MIN(DATEID) + FROM dbo.dimDate + WHERE IsRolling24 = 1 - SET NOCOUNT ON + --SET NOCOUNT ON --DROP TABLE #buyer CREATE TABLE #buyer - ( BuyerManager VARCHAR(30) - , BuyerCode CHAR(25) - , BuyerName VARCHAR(160) - , PrimaryVendorCode VARCHAR(20) - , PrimaryVendorName VARCHAR(160) - , PrimaryVendorStatusCode CHAR(1) - , PrimaryVendorNote1 VARCHAR(30) - , PrimaryVendorNote2 VARCHAR(30) - , PrimaryVendorNote3 VARCHAR(30) - , ParentVendorCode CHAR(20) - , ParentVendorName VARCHAR(160) - , CommodityCode CHAR(20) - , CommodityName VARCHAR(254) - , SubCommodityCode CHAR(20) - , SubCommodityName VARCHAR(254) - , ProductCode CHAR(20) - , ProductDesc VARCHAR(254) - , BrandCode CHAR(20) - , BrandName VARCHAR(254) - , InStockCat INT DEFAULT 0 - , StockOutCat INT DEFAULT 0 - , StockOutOnOrderCat INT DEFAULT 0 - , InStockRip INT DEFAULT 0 - , StockOutRip INT DEFAULT 0 - , StockOutOnOrderRip INT DEFAULT 0 - , InStock INT DEFAULT 0 - , StockOut INT DEFAULT 0 - , StockOutOnOrder INT DEFAULT 0 - , OnHand NUMERIC(11,2) DEFAULT 0 - , OnOrder NUMERIC(11,2) DEFAULT 0 - , PartCount INT DEFAULT 0 - , OnHandAvg NUMERIC(11,2) DEFAULT 0 + ( BuyerManager VARCHAR(30) + , BuyerCode CHAR(25) + , BuyerName VARCHAR(160) + , PrimaryVendorCode VARCHAR(20) + , PrimaryVendorName VARCHAR(160) + , PrimaryVendorStatusCode CHAR(1) + , PrimaryVendorNote1 VARCHAR(30) + , PrimaryVendorNote2 VARCHAR(30) + , PrimaryVendorNote3 VARCHAR(30) + , ParentVendorCode CHAR(20) + , ParentVendorName VARCHAR(160) + , CommodityCode CHAR(20) + , CommodityName VARCHAR(254) + , SubCommodityCode CHAR(20) + , SubCommodityName VARCHAR(254) + , ProductCode CHAR(20) + , ProductDesc VARCHAR(254) + , BrandCode CHAR(20) + , BrandName VARCHAR(254) + , InStockCat INT DEFAULT 0 + , StockOutCat INT DEFAULT 0 + , StockOutOnOrderCat INT DEFAULT 0 + , InStockRip INT DEFAULT 0 + , StockOutRip INT DEFAULT 0 + , StockOutOnOrderRip INT DEFAULT 0 + , InStock INT DEFAULT 0 + , StockOut INT DEFAULT 0 + , StockOutOnOrder INT DEFAULT 0 + , OnHand NUMERIC(11,2) DEFAULT 0 + , OnOrder NUMERIC(11,2) DEFAULT 0 + , PartCount INT DEFAULT 0 + , OnHandAvg NUMERIC(11,2) DEFAULT 0 ) -- Seed temp table with non-aggregate data INSERT INTO #buyer - ( BuyerManager, BuyerCode, BuyerName - , PrimaryVendorCode, PrimaryVendorName, PrimaryVendorStatusCode - , PrimaryVendorNote1, PrimaryVendorNote2, PrimaryVendorNote3 - , ParentVendorCode, ParentVendorName - , CommodityCode, CommodityName - , SubCommodityCode, SubCommodityName - , ProductCode, ProductDesc - , BrandCode, BrandName + ( + BuyerManager, BuyerCode, BuyerName + , PrimaryVendorCode, PrimaryVendorName, PrimaryVendorStatusCode + , PrimaryVendorNote1, PrimaryVendorNote2, PrimaryVendorNote3 + , ParentVendorCode, ParentVendorName + , CommodityCode, CommodityName + , SubCommodityCode, SubCommodityName + , ProductCode, ProductDesc + , BrandCode, BrandName ) - SELECT DISTINCT BuyerManager, BuyerCode, BuyerName - , PrimaryVendorCode, PrimaryVendorName, PrimaryVendorStatusCode - , PrimaryVendorNote1, PrimaryVendorNote2, PrimaryVendorNote3 - , ParentSupplierCode AS ParentVendorCode - , CASE WHEN CHARINDEX('(', ParentSupplierName) = 0 THEN ParentSupplierName - ELSE LEFT(ParentSupplierName, CHARINDEX('(', ParentSupplierName) - 1) - END AS ParentVendorName - , CommodityCode, CommodityName - , SubCommodityCode, SubCommodityName - , ProductCode, ProductDesc - , BrandCode, BrandName - FROM dbo.dimItem - WHERE BuyerCode IN (SELECT [Value] AS Buyer FROM STRING_SPLIT(@Buyer, ',')) + SELECT DISTINCT BuyerManager, BuyerCode, BuyerName + , PrimaryVendorCode, PrimaryVendorName, PrimaryVendorStatusCode + , PrimaryVendorNote1, PrimaryVendorNote2, PrimaryVendorNote3 + , ParentSupplierCode AS ParentVendorCode + , CASE WHEN CHARINDEX('(', ParentSupplierName) = 0 THEN ParentSupplierName + ELSE LEFT(ParentSupplierName, CHARINDEX('(', ParentSupplierName) - 1) + END AS ParentVendorName + , CommodityCode, CommodityName + , SubCommodityCode, SubCommodityName + , ProductCode, ProductDesc + , BrandCode, BrandName + FROM dbo.dimItem + WHERE BuyerCode IN (SELECT [Value] AS Buyer FROM STRING_SPLIT(@Buyer, ',')) CREATE UNIQUE INDEX #byr_idx ON #buyer (BuyerCode, PrimaryVendorCode, SubCommodityCode, ProductCode, BrandCode) -- Add StockOut values, only care about Stocked parts - UPDATE b - SET InStockCat = inv.InStockCat - , StockOutCat = inv.StockOutCat - , StockOutOnOrderCat = inv.StockOutOnOrderCat - , InStockRip = inv.InStockRip - , StockOutRip = inv.StockOutRip - , StockOutOnOrderRip = inv.StockOutOnOrderRip - , InStock = inv.InStock - , StockOut = inv.StockOut - , StockOutOnOrder = inv.StockOutOnOrder + UPDATE b + SET InStockCat = inv.InStockCat + , StockOutCat = inv.StockOutCat + , StockOutOnOrderCat = inv.StockOutOnOrderCat + , InStockRip = inv.InStockRip + , StockOutRip = inv.StockOutRip + , StockOutOnOrderRip = inv.StockOutOnOrderRip + , InStock = inv.InStock + , StockOut = inv.StockOut + , StockOutOnOrder = inv.StockOutOnOrder FROM ( SELECT b.BuyerCode - , b.PrimaryVendorCode - , b.SubCommodityCode - , b.ProductCode - , b.BrandCode - -- StkCat - , SUM(CASE WHEN itm.ItemCatalog <> '' AND i.OnHandQty - i.ReservedQty > 0 THEN 1 ELSE 0 END) AS InStockCat - , SUM(CASE WHEN itm.ItemCatalog <> '' AND i.OnHandQty - i.ReservedQty < 1 THEN 1 ELSE 0 END) AS StockOutCat - , SUM(CASE WHEN itm.ItemCatalog <> '' AND i.OnHandQty - i.ReservedQty < 1 AND i.OnOrderQty > 0 THEN 1 ELSE 0 END) AS StockOutOnOrderCat - -- StkRip - , SUM(CASE WHEN (i.FirstReceiptDate > '1900-01-01' OR i.LastReceiptDate > '1900-01-01') AND i.OnHandQty - i.ReservedQty > 0 THEN 1 ELSE 0 END) AS InStockRip - , SUM(CASE WHEN (i.FirstReceiptDate > '1900-01-01' OR i.LastReceiptDate > '1900-01-01') AND i.OnHandQty - i.ReservedQty < 1 THEN 1 ELSE 0 END) AS StockOutRip - , SUM(CASE WHEN (i.FirstReceiptDate > '1900-01-01' OR i.LastReceiptDate > '1900-01-01') AND i.OnHandQty - i.ReservedQty < 1 AND i.OnOrderQty > 0 THEN 1 ELSE 0 END) AS StockOutOnOrderRip - -- StkOut - , SUM(CASE WHEN i.OnHandQty - i.ReservedQty > 0 THEN 1 ELSE 0 END) AS InStock - , SUM(CASE WHEN i.OnHandQty - i.ReservedQty < 1 THEN 1 ELSE 0 END) AS StockOut - , SUM(CASE WHEN i.OnHandQty - i.ReservedQty < 1 AND i.OnOrderQty > 0 THEN 1 ELSE 0 END) AS StockOutOnOrder + , b.PrimaryVendorCode + , b.SubCommodityCode + , b.ProductCode + , b.BrandCode + -- StkCat + , SUM(CASE WHEN itm.ItemCatalog <> '' AND i.OnHandQty - i.ReservedQty > 0 THEN 1 ELSE 0 END) AS InStockCat + , SUM(CASE WHEN itm.ItemCatalog <> '' AND i.OnHandQty - i.ReservedQty < 1 THEN 1 ELSE 0 END) AS StockOutCat + , SUM(CASE WHEN itm.ItemCatalog <> '' AND i.OnHandQty - i.ReservedQty < 1 AND i.OnOrderQty > 0 THEN 1 ELSE 0 END) AS StockOutOnOrderCat + -- StkRip + , SUM(CASE WHEN (i.FirstReceiptDate > '1900-01-01' OR i.LastReceiptDate > '1900-01-01') AND i.OnHandQty - i.ReservedQty > 0 THEN 1 ELSE 0 END) AS InStockRip + , SUM(CASE WHEN (i.FirstReceiptDate > '1900-01-01' OR i.LastReceiptDate > '1900-01-01') AND i.OnHandQty - i.ReservedQty < 1 THEN 1 ELSE 0 END) AS StockOutRip + , SUM(CASE WHEN (i.FirstReceiptDate > '1900-01-01' OR i.LastReceiptDate > '1900-01-01') AND i.OnHandQty - i.ReservedQty < 1 AND i.OnOrderQty > 0 THEN 1 ELSE 0 END) AS StockOutOnOrderRip + -- StkOut + , SUM(CASE WHEN i.OnHandQty - i.ReservedQty > 0 THEN 1 ELSE 0 END) AS InStock + , SUM(CASE WHEN i.OnHandQty - i.ReservedQty < 1 THEN 1 ELSE 0 END) AS StockOut + , SUM(CASE WHEN i.OnHandQty - i.ReservedQty < 1 AND i.OnOrderQty > 0 THEN 1 ELSE 0 END) AS StockOutOnOrder FROM ( -- 160,075 SELECT inv.ItemID - , MAX(inv.FirstReceiptDate) AS FirstReceiptDate - , MAX(inv.LastReceiptDate) AS LastReceiptDate - , SUM(inv.OnHandQty) AS OnHandQty - , SUM(inv.OnOrderQty) AS OnOrderQty - , SUM(inv.ReservedQty) AS ReservedQty - , SUM(inv.OnHandCost) AS OnHandCost - , SUM(inv.OnOrderCost) AS OnOrderCost - FROM dbo.factInventory inv - WHERE inv.WarehouseCode IN ('02', '03', '05', '11', '14') - AND inv.WarehouseStatusCode = 'S' + , MAX(inv.FirstReceiptDate) AS FirstReceiptDate + , MAX(inv.LastReceiptDate) AS LastReceiptDate + , SUM(inv.OnHandQty) AS OnHandQty + , SUM(inv.OnOrderQty) AS OnOrderQty + , SUM(inv.ReservedQty) AS ReservedQty + , SUM(inv.OnHandCost) AS OnHandCost + , SUM(inv.OnOrderCost) AS OnOrderCost + FROM dbo.factInventory inv + WHERE inv.WarehouseCode IN ('02', '03', '05', '11', '14') + AND inv.WarehouseStatusCode = 'S' GROUP BY inv.ItemID ) i - JOIN dbo.dimItem itm - ON i.ItemID = itm.ItemID - JOIN #buyer b - ON b.BuyerCode = itm.BuyerCode - AND b.PrimaryVendorCode = itm.PrimaryVendorCode - AND b.SubCommodityCode = itm.SubCommodityCode - AND b.ProductCode = itm.ProductCode - AND b.BrandCode = itm.BrandCode + JOIN dbo.dimItem itm + ON i.ItemID = itm.ItemID AND itm.ItemStatusCode IN (SELECT [Value] AS ItemStatusCode FROM STRING_SPLIT(@ItemStatus, ',')) + JOIN #buyer b + ON b.BuyerCode = itm.BuyerCode + AND b.PrimaryVendorCode = itm.PrimaryVendorCode + AND b.SubCommodityCode = itm.SubCommodityCode + AND b.ProductCode = itm.ProductCode + AND b.BrandCode = itm.BrandCode GROUP BY b.BuyerCode - , b.PrimaryVendorCode - , b.SubCommodityCode - , b.ProductCode - , b.BrandCode + , b.PrimaryVendorCode + , b.SubCommodityCode + , b.ProductCode + , b.BrandCode ) inv JOIN #buyer b - ON b.BuyerCode = inv.BuyerCode - AND b.PrimaryVendorCode = inv.PrimaryVendorCode - AND b.SubCommodityCode = inv.SubCommodityCode - AND b.ProductCode = inv.ProductCode - AND b.BrandCode = inv.BrandCode + ON b.BuyerCode = inv.BuyerCode + AND b.PrimaryVendorCode = inv.PrimaryVendorCode + AND b.SubCommodityCode = inv.SubCommodityCode + AND b.ProductCode = inv.ProductCode + AND b.BrandCode = inv.BrandCode -- Add OnHand and OnOrder values, all parts, exclude discontinued vendors UPDATE b - SET OnHand = inv.OnHand - , OnOrder = inv.OnOrder - , PartCount = inv.PartCount + SET OnHand = inv.OnHand + , OnOrder = inv.OnOrder + , PartCount = inv.PartCount FROM ( - SELECT itm.BuyerCode - , itm.PrimaryVendorCode - , itm.SubCommodityCode - , itm.ProductCode - , itm.BrandCode - , SUM(inv.OnHandCost) AS OnHand - , SUM(inv.OnOrderCost) AS OnOrder - , COUNT(DISTINCT CASE WHEN itm.ItemStatusCode = 'S' THEN inv.ItemID ELSE NULL END) AS PartCount - FROM [$(DB_PROJ_PDW_INVENTORY)].dbo.factInventoryMonthly inv - JOIN dbo.dimItem itm - ON inv.ItemID = itm.ItemID - JOIN #buyer b - ON b.BuyerCode = itm.BuyerCode - AND b.PrimaryVendorCode = itm.PrimaryVendorCode - AND b.SubCommodityCode = itm.SubCommodityCode - AND b.ProductCode = itm.ProductCode - AND b.BrandCode = itm.BrandCode - WHERE inv.InventoryDateID = @inventory_date_id - AND inv.WarehouseCode IN ( '02', '03', '05', '11', '14', '20' ) - AND itm.PrimaryVendorStatusCode <> 'I' + SELECT itm.BuyerCode + , itm.PrimaryVendorCode + , itm.SubCommodityCode + , itm.ProductCode + , itm.BrandCode + , SUM(inv.OnHandCost) AS OnHand + , SUM(inv.OnOrderCost) AS OnOrder + , COUNT(DISTINCT CASE WHEN itm.ItemStatusCode = 'S' THEN inv.ItemID ELSE NULL END) AS PartCount + FROM PDW_Inventory.dbo.factInventoryMonthly inv + JOIN dbo.dimItem itm + ON inv.ItemID = itm.ItemID AND itm.ItemStatusCode IN (SELECT [Value] AS ItemStatusCode FROM STRING_SPLIT(@ItemStatus, ',')) + JOIN #buyer b + ON b.BuyerCode = itm.BuyerCode + AND b.PrimaryVendorCode = itm.PrimaryVendorCode + AND b.SubCommodityCode = itm.SubCommodityCode + AND b.ProductCode = itm.ProductCode + AND b.BrandCode = itm.BrandCode + WHERE inv.InventoryDateID = @inventory_date_id + AND inv.WarehouseCode IN ( '02', '03', '05', '11', '14', '20' ) + AND itm.PrimaryVendorStatusCode <> 'I' GROUP BY itm.BuyerCode - , itm.PrimaryVendorCode - , itm.SubCommodityCode - , itm.ProductCode - , itm.BrandCode + , itm.PrimaryVendorCode + , itm.SubCommodityCode + , itm.ProductCode + , itm.BrandCode ) inv JOIN #buyer b - ON b.BuyerCode = inv.BuyerCode - AND b.PrimaryVendorCode = inv.PrimaryVendorCode - AND b.SubCommodityCode = inv.SubCommodityCode - AND b.ProductCode = inv.ProductCode - AND b.BrandCode = inv.BrandCode + ON b.BuyerCode = inv.BuyerCode + AND b.PrimaryVendorCode = inv.PrimaryVendorCode + AND b.SubCommodityCode = inv.SubCommodityCode + AND b.ProductCode = inv.ProductCode + AND b.BrandCode = inv.BrandCode -- Add OnHandAvg, needs two months of history to Calculate Average OnHand Inventory @@ -200,9 +202,9 @@ , itm.SubCommodityCode, itm.ProductCode, itm.BrandCode , SUM(inv.OnHandCost) AS OnHand , SUM(inv.OnHandCost) / 2 AS OnHandAvg - FROM [$(DB_PROJ_PDW_INVENTORY)].dbo.factInventoryMonthly inv - JOIN dbo.dimItem itm - ON inv.ItemID = itm.ItemID + FROM PDW_Inventory.dbo.factInventoryMonthly inv + JOIN PDW_Reporting.dbo.dimItem itm + ON inv.ItemID = itm.ItemID AND itm.ItemStatusCode IN (SELECT [Value] AS ItemStatusCode FROM STRING_SPLIT(@ItemStatus, ',')) WHERE InventoryDateID IN (@this_mth_id, @last_mth_id) AND inv.WarehouseCode IN ('02','03','05','11','14','20') GROUP BY itm.BuyerCode, itm.PrimaryVendorCode @@ -290,7 +292,7 @@ JOIN dbo.dimMisc m ON sls.MiscID = m.MiscID JOIN dbo.dimItem itm - ON sls.ItemID = itm.ItemID + ON sls.ItemID = itm.ItemID AND itm.ItemStatusCode IN (SELECT [Value] AS ItemStatusCode FROM STRING_SPLIT(@ItemStatus, ',')) JOIN #buyer b ON b.BuyerCode = itm.BuyerCode AND b.PrimaryVendorCode = itm.PrimaryVendorCode @@ -325,7 +327,7 @@ JOIN dbo.dimDate sd ON ex.ShipDateID = sd.DATEID JOIN dbo.dimItem itm - ON ex.ItemNumber = itm.ItemNumber + ON ex.ItemNumber = itm.ItemNumber AND itm.ItemStatusCode IN (SELECT [Value] AS ItemStatusCode FROM STRING_SPLIT(@ItemStatus, ',')) JOIN #buyer b ON b.BuyerCode = itm.BuyerCode AND b.PrimaryVendorCode = itm.PrimaryVendorCode @@ -362,6 +364,7 @@ , b.InStock, b.StockOut, b.StockOutOnOrder , b.OnHand, b.OnOrder, b.PartCount, b.OnHandAvg ORDER BY 3, 5 +END GO GRANT EXECUTE ON OBJECT::[dbo].[spCorporatePerformance] TO [PDW_Reporting_Read_Only]