Accuracy per waves

Description

Calculates the number of validated facings and identifies SKU and product/brand errors by Wave, based on predefined date ranges and specific client, country, validation status, and image type criteria.


Query :

WITH xCTE AS (
    SELECT 
        CAST(AP.[CreatedOn] AS Date) AS [CreatedOn_Date],
        APP.[IsSKUError],
        APP.[IsBrandError],
        CASE WHEN APP.[ProductID] = 18 AND APP.[IRManualProductID] = 18 THEN 1 ELSE 0 END AS [IRnGT_isEmpty],
        CASE WHEN APP.[IsSKUError] = 1 THEN 1 ELSE 0 END AS xSKUError,
        CASE WHEN APP.[IsBrandError] = 1 THEN 1 ELSE 0 END AS xProductError,
        CASE 
            WHEN CAST(AP.[CreatedOn] AS Date) BETWEEN '2024-01-01' AND '2024-01-26' THEN 'Wave 1'
            WHEN CAST(AP.[CreatedOn] AS Date) BETWEEN '2024-01-27' AND '2024-02-23' THEN 'Wave 2'
            WHEN CAST(AP.[CreatedOn] AS Date) BETWEEN '2024-02-24' AND '2024-03-29' THEN 'Wave 3'
			WHEN CAST(AP.[CreatedOn] AS Date) BETWEEN '2024-03-30' AND '2024-04-26' THEN 'Wave 4'
			WHEN CAST(AP.[CreatedOn] AS Date) BETWEEN '2024-04-27' AND '2024-05-24' THEN 'Wave 5'
			WHEN CAST(AP.[CreatedOn] AS Date) BETWEEN '2024-05-25' AND '2024-06-28' THEN 'Wave 6'
			WHEN CAST(AP.[CreatedOn] AS Date) BETWEEN '2024-06-29' AND '2024-07-26' THEN 'Wave 7'
			WHEN CAST(AP.[CreatedOn] AS Date) BETWEEN '2024-07-27' AND '2024-08-23' THEN 'Wave 8'
			WHEN CAST(AP.[CreatedOn] AS Date) BETWEEN '2024-08-24' AND '2024-09-27' THEN 'Wave 9'
			WHEN CAST(AP.[CreatedOn] AS Date) BETWEEN '2024-09-28' AND '2024-10-25' THEN 'Wave 10'
			WHEN CAST(AP.[CreatedOn] AS Date) BETWEEN '2024-10-26' AND '2024-11-22' THEN 'Wave 11'
			WHEN CAST(AP.[CreatedOn] AS Date) BETWEEN '2024-11-23' AND '2024-12-31' THEN 'Wave 12'
            ELSE 'Other'
        END AS DateRange
    FROM 
        AssetPurityProduct APP
        INNER JOIN AssetPurity AP WITH (NOLOCK) 
            ON APP.AssetPurityId = AP.AssetPurityId 
            AND AP.IsDeleted = 0 
            AND AP.[ImageTypeId] = 1
        LEFT OUTER JOIN vwProductList PGT 
            ON APP.IRManualProductID = PGT.ProductId
        LEFT OUTER JOIN dbo.[SceneType] SceneType 
            ON SceneType.SceneTypeCode = AP.SceneTypeCode 
            AND SceneType.SceneSubTypeCode = AP.SceneSubTypeCode 
            AND SceneType.IsDeleted = 0 
            AND AP.ClientId = SceneType.ClientId
    WHERE 
        CAST(AP.[CreatedOn] AS Date) BETWEEN '2024-01-01' AND '2024-12-31'
        AND AP.ClientId = 213
        AND AP.CountryId = 19
        AND AP.[SelfCheckedDate] > 0
        AND AP.[StatusId] = 13
        AND AP.[ImageTypeId] = 1
        AND PGT.Type NOT IN ('2')
        AND (NOT SceneType.RESceneType IN (3, 95))
        AND (CASE WHEN APP.[ProductID] = 18 AND APP.[IRManualProductID] = 18 THEN 1 ELSE 0 END) = 0
)
SELECT
    DateRange,                   
    COUNT(1) AS ValidatedFacings, 
    SUM(xCTE.xSKUError) AS xSKUError, 
    SUM(xCTE.xProductError) AS xProductError 
FROM 
    xCTE
GROUP BY 
    DateRange

--------------------------------------------------