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);