So, I've done all I could have done to no avail.
The Query is huge but its just Input and Inner join.
I just cant figure out how I can edit the input (branch code, Range1 and Range 2) to be able to be edited in a slicer.
Any ideas?
Heres my SQL Query
DECLARE @BranchCode VARCHAR(500) = '01001,01023,01025,01027,01031'
DECLARE @Range1 VARCHAR(255) = '202101,202102,202103,202104,202105,202106,202107,202108,202109,202110,202111'
DECLARE @Range2 VARCHAR(255) = '202201,202202,202203,202204,202205,202206,202207,202208,202209,202210,202211'
SELECT
ROW_NUMBER() OVER(ORDER BY sub1.ValueGap ASC,ItemID) AS RN_VG,
ROW_NUMBER() OVER(ORDER BY sub1.R2_ItemPrice DESC,ItemID) AS RN_R2IP,
sub1.*
FROM
(
SELECT
--- Item Info ---
ISNULL(r1.ItemID, r2.ItemID) AS ItemID,
ISNULL(im1.item_id, im2.item_id) AS ItemSKU,
ISNULL(pg1.Product_group_id, pg2.Product_group_id) AS ProductGroupCode,
ISNULL(pg1.Product_group_desc, pg2.Product_group_desc) AS ProductGroupDescription,
'All' AS BranchCode,
--- 2nd Time Range ---
r2.TotalItemPrice as R2_ItemPrice,
r2.TotalItemCost as R2_ItemCOGS,
r2.TotalItemQty as R2_ItemQty,
ISNULL(r2.TotalItemPrice,0) - ISNULL(r2.TotalItemCost,0) AS R2_ItemMargin,
CASE
WHEN ISNULL(r2.TotalItemPrice,0) <> 0
THEN ((ISNULL(r2.TotalItemPrice,0) - ISNULL(r2.TotalItemCost,0))/r2.TotalItemPrice)-- * 100
ELSE 0.00
END AS R2_MarginPercent,
CASE
WHEN ISNULL(r2.TotalItemQty,0) <> 0
THEN ISNULL(r2.TotalItemPrice,0) / r2.TotalItemQty
ELSE
0.00
END AS R2_PricePerUnit,
CASE
WHEN ISNULL(r2.TotalItemQty,0) <> 0
THEN ISNULL(r2.TotalItemCost,0) / r2.TotalItemQty
ELSE
0.00
END AS R2_CostPerUnit,
--- Value Gap Calculations ---
CASE
WHEN ISNULL(r1.TotalItemQty,0) <> 0 AND ISNULL(r2.TotalItemQty,0) <> 0
THEN ( (ISNULL(r2.TotalItemPrice,0) / r2.TotalItemQty) - (ISNULL(r1.TotalItemPrice,0) / r1.TotalItemQty) ) * r2.TotalItemQty
ELSE
0.00
END AS ExtPrice,
CASE
WHEN ISNULL(r1.TotalItemQty,0) <> 0 AND ISNULL(r2.TotalItemQty,0) <> 0
THEN ( (ISNULL(r2.TotalItemCost,0) / r2.TotalItemQty) - (ISNULL(r1.TotalItemCost,0) / r1.TotalItemQty) ) * r2.TotalItemQty
ELSE
0.00
END AS ExtCost,
CASE
WHEN ISNULL(r1.TotalItemQty,0) <> 0 AND ISNULL(r2.TotalItemQty,0) <> 0
THEN (( (ISNULL(r2.TotalItemPrice,0) / r2.TotalItemQty) - (ISNULL(r1.TotalItemPrice,0) / r1.TotalItemQty) ) * r2.TotalItemQty)
- (( (ISNULL(r2.TotalItemCost,0) / r2.TotalItemQty) - (ISNULL(r1.TotalItemCost,0) / r1.TotalItemQty) ) * r2.TotalItemQty)
ELSE
0.00
END AS ValueGap,
CASE
WHEN ISNULL(r1.TotalItemQty,0) <> 0 AND ISNULL(r2.TotalItemQty,0) <> 0 AND ISNULL(r1.TotalItemPrice,0) <> 0
THEN ( ( (ISNULL(r2.TotalItemPrice,0) / r2.TotalItemQty) - (ISNULL(r1.TotalItemPrice,0) / r1.TotalItemQty) ) / (ISNULL(r1.TotalItemPrice,0) / r1.TotalItemQty) )-- * 100
ELSE
0.00
END AS PricePercentVsR1,
CASE
WHEN ISNULL(r1.TotalItemQty,0) <> 0 AND ISNULL(r2.TotalItemQty,0) <> 0 AND ISNULL(r1.TotalItemCost,0) <> 0
THEN ( ( (ISNULL(r2.TotalItemCost,0) / r2.TotalItemQty) - (ISNULL(r1.TotalItemCost,0) / r1.TotalItemQty) ) / (ISNULL(r1.TotalItemCost,0) / r1.TotalItemQty) )-- * 100
ELSE
0.00
END AS CostPercentVsR1,
--- 1st Time Range ---
r1.TotalItemPrice AS R1_ItemPrice,
r1.TotalItemCost AS R1_ItemCOGS,
r1.TotalItemQty AS R1_ItemQty,
ISNULL(r1.TotalItemPrice,0) - ISNULL(r1.TotalItemCost,0) AS R1_ItemMargin,
CASE
WHEN ISNULL(r1.TotalItemPrice,0) <> 0
THEN ((ISNULL(r1.TotalItemPrice,0) - ISNULL(r1.TotalItemCost,0))/r1.TotalItemPrice)-- * 100
ELSE 0.00
END AS R1_MarginPercent,
CASE
WHEN ISNULL(r1.TotalItemQty,0) <> 0
THEN ISNULL(r1.TotalItemPrice,0) / r1.TotalItemQty
ELSE
0.00
END AS R1_PricePerUnit,
CASE
WHEN ISNULL(r1.TotalItemQty,0) <> 0
THEN ISNULL(r1.TotalItemCost,0) / r1.TotalItemQty
ELSE
0.00
END AS R1_CostPerUnit
FROM
(
SELECT
ItemID,
SUM(TotalItemPrice) AS TotalItemPrice,
SUM(TotalItemCost) AS TotalItemCost,
SUM(TotalItemQty) AS TotalItemQty
FROM
DM.MonthlyItemCogs
WHERE
0 = 0
AND InvoiceYearMonth IN (SELECT VALUE FROM dbo.SplitString(@Range1,','))
AND BranchCode IN (SELECT ITEM FROM dbo.DelimitedSplit8K(@BranchCode,N','))
GROUP BY
ItemID
) r1
INNER JOIN
(
SELECT
ItemID,
SUM(TotalItemPrice) AS TotalItemPrice,
SUM(TotalItemCost) AS TotalItemCost,
SUM(TotalItemQty) AS TotalItemQty
FROM
DM.MonthlyItemCogs
WHERE
0 = 0
AND InvoiceYearMonth IN (SELECT VALUE FROM dbo.SplitString(@Range2,','))
AND BranchCode IN (SELECT ITEM FROM dbo.DelimitedSplit8K(@BranchCode,N','))
GROUP BY
ItemID
) r2
ON r1.ItemID = r2.ItemID
--AND r1.ProductGroupCode = r2.ProductGroupCode
LEFT JOIN SQLBI01.P21.dbo.inv_mast im1
ON r1.ItemID = im1.inv_mast_uid
LEFT JOIN SQLBI01.P21.dbo.Product_Group pg1
ON im1.default_product_group = pg1.Product_Group_ID
AND pg1.company_id = 'KEN'
LEFT JOIN SQLBI01.P21.dbo.inv_mast im2
ON r2.ItemID = im2.inv_mast_uid
LEFT JOIN SQLBI01.P21.dbo.Product_Group pg2
ON im2.default_product_group = pg2.Product_Group_ID
AND pg2.company_id = 'KEN'
WHERE
0 = 0
AND r1.ItemID NOT IN (SELECT ItemID FROM DM.CostAnalysisItemException)
AND r2.ItemID NOT IN (SELECT ItemID FROM DM.CostAnalysisItemException)
) sub1
