Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Jul 17th, 2012  |  syntax: None  |  size: 2.97 KB  |  hits: 12  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Split a Max/Min score into 3 bands with fixed threshold values in TSQL
  2. DECLARE @GreenLower INT
  3. DECLARE @AmberUpper INT
  4. DECLARE @AmberLower INT
  5. DECLARE @RedUpper INT
  6.  
  7. SET @GreenLower = 80
  8. SET @AmberUpper = 80
  9. SET @AmberLower = 60
  10. SET @RedUpper = 60
  11.  
  12. DECLARE @Scores TABLE
  13.   (
  14.    GroupedBy VARCHAR(50) ,
  15.    PCTMax INT ,
  16.    PCTAvg INT ,
  17.    PCTMin INT ,
  18.    ALLAvg INT ,
  19.    AllMax INT ,
  20.    AllMin INT
  21.   )
  22.  
  23. INSERT  INTO @Scores
  24. VALUES  ( 'Prov1', 80, 75, 63, 50, 90, 22 )
  25. INSERT  INTO @Scores
  26. VALUES  ( 'Prov2', 100, 96, 70, 80, 100, 55 )
  27. INSERT  INTO @Scores
  28. VALUES  ( 'Prov3', 72, 58, 44, 62, 85, 41 )
  29. INSERT  INTO @Scores
  30. VALUES  ( 'Prov4', 90, 78, 58, 59, 93, 30 )
  31. INSERT  INTO @Scores
  32. VALUES  ( 'Prov5', 63, 25, 21, 30, 40, 18 )
  33.  
  34. SELECT  GroupedBy ,
  35.             PCTMax ,
  36.     PCTAvg ,
  37.     PCTMin ,
  38.     AllAvg ,
  39.     CASE WHEN AllMax > 79 THEN AllMax ELSE NULL END AS GreenUpper ,
  40.     CASE WHEN AllMin > 79 THEN AllMin ELSE @GreenLower END AS GreenLower ,
  41.  
  42.     CASE WHEN AllMax < 79 THEN AllMax ELSE
  43.         CASE WHEN AllMax > 79 THEN @AmberUpper ELSE  NULL END
  44.     END AS AmberUpper ,
  45.     @AmberLower AS AmberLower ,
  46.  
  47.     CASE WHEN AllMin > 59 THEN NULL ELSE @RedUpper END AS RedUpper ,
  48.     CASE WHEN AllMin > 59 THEN NULL ELSE AllMin END AS RedLower
  49.  
  50. FROM    @Scores
  51.        
  52. SELECT
  53.   GroupedBy,
  54.   PCTMax,
  55.   PCTAvg,
  56.   PCTMin,
  57.   AllAvg,
  58.   GreenUpper = MAX(CASE BandName WHEN 'Green' THEN Upper END),
  59.   GreenLower = MAX(CASE BandName WHEN 'Green' THEN Lower END),
  60.   AmberUpper = MAX(CASE BandName WHEN 'Amber' THEN Upper END),
  61.   AmberLower = MAX(CASE BandName WHEN 'Amber' THEN Lower END),
  62.   RedUpper   = MAX(CASE BandName WHEN 'Red'   THEN Upper END),
  63.   RedLower   = MAX(CASE BandName WHEN 'Red'   THEN Lower END)
  64. FROM (
  65.   SELECT
  66.     s.GroupedBy,
  67.     s.PCTMax,
  68.     s.PCTAvg,
  69.     s.PCTMin,
  70.     s.AllAvg,
  71.     b.BandName,
  72.     Upper = CASE
  73.       WHEN s.AllMax >= b.BandMin AND s.AllMin <= b.BandMax THEN
  74.         CASE
  75.           WHEN b.BandMax > s.AllMax THEN s.AllMax
  76.           ELSE b.BandMax
  77.         END
  78.     END,
  79.     Lower = CASE
  80.       WHEN s.AllMax >= b.BandMin AND s.AllMin <= b.BandMax THEN
  81.         CASE
  82.           WHEN b.BandMin < s.AllMin THEN s.AllMin
  83.           ELSE b.BandMin
  84.         END
  85.     END
  86.   FROM Scores s
  87.   CROSS JOIN (
  88.     SELECT 'Green', 80, 2147483647 UNION ALL
  89.     SELECT 'Amber', 60, 79         UNION ALL
  90.     SELECT 'Red'  ,  0, 59
  91.   ) b (BandName, BandMin, BandMax)
  92. ) s
  93. GROUP BY
  94.   GroupedBy,
  95.   PCTMax,
  96.   PCTAvg,
  97.   PCTMin,
  98.   AllAvg
  99.        
  100. SELECT  GroupedBy
  101.       , AllMax
  102.       , AllMin
  103.       , CASE WHEN AllMax > 79 THEN AllMax
  104.              ELSE NULL
  105.         END AS GreenUpper
  106.       , CASE WHEN AllMin > 79 THEN AllMin
  107.              ELSE @GreenLower
  108.         END AS GreenLower
  109.       , CASE WHEN AllMax BETWEEN 61 AND 80 THEN AllMax
  110.              WHEN AllMax > 79
  111.                   AND AllMin < 80 THEN @AmberUpper
  112.              ELSE NULL
  113.         END AS AmberUpper
  114.       , @AmberLower AS AmberLower
  115.       , @RedUpper AS RedUpper
  116.       , CASE WHEN AllMin > 59 THEN NULL
  117.              ELSE AllMin
  118.         END AS RedLower
  119. FROM    @Scores
create a new version of this paste RAW Paste Data