Split a Max/Min score into 3 bands with fixed threshold values in TSQL
DECLARE @GreenLower INT
DECLARE @AmberUpper INT
DECLARE @AmberLower INT
DECLARE @RedUpper INT

SET @GreenLower = 80
SET @AmberUpper = 80
SET @AmberLower = 60
SET @RedUpper = 60

DECLARE @Scores TABLE
  (
   GroupedBy VARCHAR(50) ,
   PCTMax INT ,
   PCTAvg INT ,
   PCTMin INT ,
   ALLAvg INT ,
   AllMax INT ,
   AllMin INT
  )

INSERT  INTO @Scores
VALUES  ( 'Prov1', 80, 75, 63, 50, 90, 22 )
INSERT  INTO @Scores
VALUES  ( 'Prov2', 100, 96, 70, 80, 100, 55 )
INSERT  INTO @Scores
VALUES  ( 'Prov3', 72, 58, 44, 62, 85, 41 )
INSERT  INTO @Scores
VALUES  ( 'Prov4', 90, 78, 58, 59, 93, 30 )
INSERT  INTO @Scores
VALUES  ( 'Prov5', 63, 25, 21, 30, 40, 18 )

SELECT  GroupedBy ,
            PCTMax ,
    PCTAvg ,
    PCTMin ,
    AllAvg ,
    CASE WHEN AllMax > 79 THEN AllMax ELSE NULL END AS GreenUpper ,
    CASE WHEN AllMin > 79 THEN AllMin ELSE @GreenLower END AS GreenLower ,

    CASE WHEN AllMax < 79 THEN AllMax ELSE
        CASE WHEN AllMax > 79 THEN @AmberUpper ELSE  NULL END
    END AS AmberUpper ,
    @AmberLower AS AmberLower ,

    CASE WHEN AllMin > 59 THEN NULL ELSE @RedUpper END AS RedUpper ,
    CASE WHEN AllMin > 59 THEN NULL ELSE AllMin END AS RedLower

FROM    @Scores

SELECT
  GroupedBy,
  PCTMax,
  PCTAvg,
  PCTMin,
  AllAvg,
  GreenUpper = MAX(CASE BandName WHEN 'Green' THEN Upper END),
  GreenLower = MAX(CASE BandName WHEN 'Green' THEN Lower END),
  AmberUpper = MAX(CASE BandName WHEN 'Amber' THEN Upper END),
  AmberLower = MAX(CASE BandName WHEN 'Amber' THEN Lower END),
  RedUpper   = MAX(CASE BandName WHEN 'Red'   THEN Upper END),
  RedLower   = MAX(CASE BandName WHEN 'Red'   THEN Lower END)
FROM (
  SELECT
    s.GroupedBy,
    s.PCTMax,
    s.PCTAvg,
    s.PCTMin,
    s.AllAvg,
    b.BandName,
    Upper = CASE
      WHEN s.AllMax >= b.BandMin AND s.AllMin <= b.BandMax THEN
        CASE
          WHEN b.BandMax > s.AllMax THEN s.AllMax
          ELSE b.BandMax
        END
    END,
    Lower = CASE
      WHEN s.AllMax >= b.BandMin AND s.AllMin <= b.BandMax THEN
        CASE
          WHEN b.BandMin < s.AllMin THEN s.AllMin
          ELSE b.BandMin
        END
    END
  FROM Scores s
  CROSS JOIN (
    SELECT 'Green', 80, 2147483647 UNION ALL
    SELECT 'Amber', 60, 79         UNION ALL
    SELECT 'Red'  ,  0, 59
  ) b (BandName, BandMin, BandMax)
) s
GROUP BY
  GroupedBy,
  PCTMax,
  PCTAvg,
  PCTMin,
  AllAvg

SELECT  GroupedBy
      , AllMax
      , AllMin
      , CASE WHEN AllMax > 79 THEN AllMax
             ELSE NULL
        END AS GreenUpper
      , CASE WHEN AllMin > 79 THEN AllMin
             ELSE @GreenLower
        END AS GreenLower
      , CASE WHEN AllMax BETWEEN 61 AND 80 THEN AllMax
             WHEN AllMax > 79
                  AND AllMin < 80 THEN @AmberUpper
             ELSE NULL
        END AS AmberUpper
      , @AmberLower AS AmberLower
      , @RedUpper AS RedUpper
      , CASE WHEN AllMin > 59 THEN NULL
             ELSE AllMin
        END AS RedLower
FROM    @Scores