title: CASE statement with Absolute Values
body: <p>The CASE statement doesn't seem to be catching a record with a negative value even though I am using the ABS() function on the expression. The record in question is the one with the value of -1.52 which should fall in the range of the case statement " </p>
  
  <pre><code>WHEN ABS(DifferenceHours - @Mean_Diff) &gt;1*@SD
                       AND ABS(DifferenceHours - @Mean_Diff) &lt;=2*@SD
                       THEN 'Recommend Review'
  </code></pre>
  
  <p>"</p>
  
  <p>Can anyone shed some light on this? Thanks in advance! (sorry about the formatting on the expected results)</p>
  
  <p>Script to create table containing sample data:</p>
  
  <pre><code>CREATE TABLE [dbo].[Sample_Data](
      [ID] [char](6) NOT NULL,
      [Actual_Hours] [decimal](38, 2) NULL,
      [Standard_Hours] [decimal](10, 2) NULL,
      [DifferenceHours] [decimal](38, 2) NULL
  ) ON [PRIMARY]
  GO
  SET ANSI_PADDING OFF
  GO
  INSERT [dbo].[Sample_Data] ([ID], [Actual_Hours], [Standard_Hours], [DifferenceHours]) VALUES (N'490412', CAST(1.22 AS Decimal(38, 2)), CAST(0.92 AS Decimal(10, 2)), CAST(0.30 AS Decimal(38, 2)))
  INSERT [dbo].[Sample_Data] ([ID], [Actual_Hours], [Standard_Hours], [DifferenceHours]) VALUES (N'491712', CAST(1.36 AS Decimal(38, 2)), CAST(0.92 AS Decimal(10, 2)), CAST(0.44 AS Decimal(38, 2)))
  INSERT [dbo].[Sample_Data] ([ID], [Actual_Hours], [Standard_Hours], [DifferenceHours]) VALUES (N'493822', CAST(1.96 AS Decimal(38, 2)), CAST(0.92 AS Decimal(10, 2)), CAST(1.04 AS Decimal(38, 2)))
  INSERT [dbo].[Sample_Data] ([ID], [Actual_Hours], [Standard_Hours], [DifferenceHours]) VALUES (N'496762', CAST(1.51 AS Decimal(38, 2)), CAST(1.84 AS Decimal(10, 2)), CAST(-0.33 AS Decimal(38, 2)))
  INSERT [dbo].[Sample_Data] ([ID], [Actual_Hours], [Standard_Hours], [DifferenceHours]) VALUES (N'497082', CAST(2.72 AS Decimal(38, 2)), CAST(0.92 AS Decimal(10, 2)), CAST(1.80 AS Decimal(38, 2)))
  INSERT [dbo].[Sample_Data] ([ID], [Actual_Hours], [Standard_Hours], [DifferenceHours]) VALUES (N'497092', CAST(1.45 AS Decimal(38, 2)), CAST(0.92 AS Decimal(10, 2)), CAST(0.53 AS Decimal(38, 2)))
  INSERT [dbo].[Sample_Data] ([ID], [Actual_Hours], [Standard_Hours], [DifferenceHours]) VALUES (N'497162', CAST(2.06 AS Decimal(38, 2)), CAST(0.92 AS Decimal(10, 2)), CAST(1.14 AS Decimal(38, 2)))
  INSERT [dbo].[Sample_Data] ([ID], [Actual_Hours], [Standard_Hours], [DifferenceHours]) VALUES (N'498002', CAST(2.03 AS Decimal(38, 2)), CAST(0.92 AS Decimal(10, 2)), CAST(1.11 AS Decimal(38, 2)))
  INSERT [dbo].[Sample_Data] ([ID], [Actual_Hours], [Standard_Hours], [DifferenceHours]) VALUES (N'498632', CAST(0.89 AS Decimal(38, 2)), CAST(0.92 AS Decimal(10, 2)), CAST(-0.03 AS Decimal(38, 2)))
  INSERT [dbo].[Sample_Data] ([ID], [Actual_Hours], [Standard_Hours], [DifferenceHours]) VALUES (N'499162', CAST(0.79 AS Decimal(38, 2)), CAST(1.84 AS Decimal(10, 2)), CAST(-1.05 AS Decimal(38, 2)))
  INSERT [dbo].[Sample_Data] ([ID], [Actual_Hours], [Standard_Hours], [DifferenceHours]) VALUES (N'499582', CAST(1.19 AS Decimal(38, 2)), CAST(0.92 AS Decimal(10, 2)), CAST(0.27 AS Decimal(38, 2)))
  </code></pre>
  
  <p><strong>Code in question:</strong> </p>
  
  <p>I am struggling with the formatting on stack exchange. When I paste my code in it jacks up line breaks and strips out 'some' asterisks. I have no idea why. I posted the code here while I try to sort it out. Interestingly enough it seemed to have no trouble with the code to create the table containing the sample data.</p>
  
  <p><a href="https://codedump.io/share/7ISZ7p8qrXvu/1" rel="nofollow noreferrer">https://codedump.io/share/7ISZ7p8qrXvu/1</a></p>
  
  <hr>
  
  <p>FROM Sample_Data  </p>
  
  <p>My Expected result:   </p>
  
  <pre><code>ID     | Actual_Hours | Standard_Hours | DifferenceHours | Deviation | Recommendation
  490412 | 1.22        | 0.92            | 0.30           | -0.17 |
  491712 | 1.36        | 0.92            | 0.44           | -0.03 |
  493822 | 1.96        | 0.92            | 1.04           | 0.57  | 
  496762 | 1.51        | 1.84            |-0.33 | -0.80   |
  497082 | 2.72        | 0.92            | 1.80 | 1.33    |   Recommend Review
  497092 | 1.45        | 0.92            | 0.53 | 0.06    |
  497162 | 2.06        | 0.92            | 1.14 | 0.67    | 
  498002 | 2.03        | 0.92            | 1.11 | 0.64    |
  498632 | 0.89        | 0.92            |-0.03 | -0.50   | 
  499162 | 0.79        | 1.84            | -1.05| -1.52   | RecommendReview &lt;- 
  499582 | 1.19        | 0.92            | 0.27 | -0.20   | 
  </code></pre>