ALTER VIEW [dbo].[ColorScheme] AS
WITH ColorCT AS (

SELECT
      C.Deployment
    , C.[Platform Name]                                                                                                             AS Platform
    , C.Instance
    , C.ClientCode
    , C.[Lev1 Suspect]                                                                                                              AS Suspects
    , COUNT(*) OVER (PARTITION BY C.[Lev1 Suspect])                                                                                 AS CountSuspects
    , SUM(CASE WHEN C.[Platform Name] = 'Epic' THEN 0 ELSE 1 END) OVER (PARTITION BY C.[Lev1 Suspect])                              AS NonEpicMatches
    , SUM(CASE WHEN C.[Platform Name] = 'Epic' THEN 1 ELSE 0 END) OVER (PARTITION BY C.[Lev1 Suspect])                              AS EpicMatches
    , (SELECT COUNT(DISTINCT Deployment) FROM Client R WHERE R.[Lev1 Suspect] = C.[Lev1 Suspect] AND R.Deployment != 'ALL')         AS DeploymentCount
    , (SELECT COUNT(DISTINCT Instance) FROM Client R WHERE R.[Lev1 Suspect] = C.[Lev1 Suspect] AND R.Deployment != 'ALL')           AS InstanceCount
FROM
    Client C
)

SELECT
    Deployment
    , Platform
    , Instance
    , ClientCode
    , Suspects
    , CASE -- Yellow should match one deployment, one instance, nothing in Epic, multiple suspects
        WHEN
            DeploymentCount = 1
        AND InstanceCount = 1
        AND EpicMatches = 0
        AND CountSuspects > 1
        THEN 'Yellow'
        ELSE NULL END                                                                                 AS Yellow
    , CASE -- Red should match one deployment, multiple instances, nothing in Epic, multiple suspects
        WHEN
            InstanceCount > 1
        AND DeploymentCount = 1
        AND EpicMatches = 0
        AND CountSuspects > 1
        THEN 'Red'
        ELSE NULL END                                                                                 AS Red
    , CASE -- Blue should match multiple deployments, nothing in Epic, multiple suspects
        WHEN
            DeploymentCount > 1
        AND NonEpicMatches > 1
        AND EpicMatches = 0
        AND CountSuspects > 1
        THEN 'Blue'
        ELSE NULL END                                                                                 AS Blue
    , CASE -- Green should have a match in Epic and a Deployment
        WHEN
            CountSuspects = EpicMatches + NonEpicMatches
        AND EpicMatches > 0
        AND NonEpicMatches > 0
        AND CountSuspects > 1
        THEN 'Green' ELSE NULL
        END                                                                                         AS Green
    , DeploymentCount
    , InstanceCount
    , CountSuspects
    , NonEpicMatches
    , EpicMatches
FROM
    ColorCT