WITH version_comparison AS (
SELECT
v.PriceProfileId,
v.Name,
i.ItemNumber,
i.ServiceType,
i.WorkType,
i.RevenueStream,
i.Multiplier,
i.SGSItemDescription,
i.ClientDescription,
i.ItemAlias,
i.ModificationStatus,
c.Cost,
c.Code,
v.VersionOrder
FROM prod_pricematrix.bronze.price_profile_version v
LEFT JOIN prod_pricematrix.bronze.price_profile_item i ON v.Id = i.VersionId
LEFT JOIN prod_pricematrix.bronze.price_profile_item_currency c ON i.Id = c.PriceProfileItemId
WHERE v.PriceProfileId = :ppi AND v.VersionOrder IN (:iv, :cv)
)
SELECT
COALESCE(v1.PriceProfileId, v2.PriceProfileId) AS PriceProfileId,
COALESCE(v1.Name, v2.Name) AS Name,
COALESCE(v1.ItemNumber, v2.ItemNumber) AS ItemNumber,
v1.ServiceType AS ServiceType_v1,
v1.WorkType AS WorkType_v1,
v1.RevenueStream AS RevenueStream_v1,
v1.Multiplier AS Multiplier_v1,
v1.SGSItemDescription AS SGSItemDescription_v1,
v1.ClientDescription AS ClientDescription_v1,
v1.ItemAlias AS ItemAlias_v1,
v2.ServiceType AS ServiceType_v2,
v2.WorkType AS WorkType_v2,
v2.RevenueStream AS RevenueStream_v2,
v2.Multiplier AS Multiplier_v2,
v2.SGSItemDescription AS SGSItemDescription_v2,
v2.ClientDescription AS ClientDescription_v2,
v2.ItemAlias AS ItemAlias_v2,
v1.ModificationStatus AS ModificationStatus_v1,
v2.ModificationStatus AS ModificationStatus_v2,
COALESCE(v1.Code, v2.Code) AS CurrencyType,
v1.Cost AS Cost_v1,
v2.Cost AS Cost_v2,
CASE
WHEN Cost_v1 IS NULL OR Cost_v2 IS NULL THEN 'N/A'
WHEN Cost_v1 = 0 AND Cost_v2 = 0 THEN '0.00%'
WHEN Cost_v1 = Cost_v2 THEN '0.00%'
WHEN Cost_v1 = 0 THEN '100.00%'
WHEN Cost_v2 = 0 THEN '-100.00%'
ELSE CONCAT(
CAST(ROUND((Cost_v2 - Cost_v1) / Cost_v1 * 100, 2) AS DECIMAL(10,2)),
'%'
)
END AS CostChangePercentage,
CASE
WHEN v1.Cost IS NULL OR v2.Cost IS NULL THEN 'N/A'
WHEN v1.Cost = v2.Cost THEN 'No Change'
WHEN v2.Cost > v1.Cost THEN 'Increased'
WHEN v2.Cost < v1.Cost THEN 'Decreased'
END AS CostChangeStatus
FROM (
SELECT * FROM version_comparison WHERE VersionOrder = :iv
) v1
FULL OUTER JOIN (
SELECT * FROM version_comparison WHERE VersionOrder = :cv
) v2 ON v1.ItemNumber = v2.ItemNumber
ORDER BY COALESCE(v1.ItemNumber, v2.ItemNumber);
