May 16, 2017 at 9:49 am
***EDIT: Updated with sample data and code to create the sample tables.***
***EDIT #2: Added missing ELSE Statements which got rid of the NULL values but the logic still doesn't pick up the record where the absolute value falls in the range of >1*SD and <= 2*SD.
I have a CASE statement that returns a different text string that returns a based on comparing two expressions. It seems to work fine except for cases where value for 'Deviation' is negative and the absolute value falls in the range of >1*SD and <= 2*SD (See 'Recommend Review' below). I shouldn't get any NULL values. Can anyone provide some insight here? Thanks in advance for looking at it!
Code to create table containing sample data:
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 I am having trouble with:
DECLARE @Mean_Diff DECIMAL(10,2) --================================================
SET @Mean_Diff = ( -- Get the Mean Difference hours to
SELECT AVG(DifferenceHours) -- Calculate Variance
FROM Sample_Data --================================================
)
--------------------------------------
DECLARE @Variance DECIMAL(10,2)
SET @Variance = ( --================
SELECT CAST(SUM(POWER(DifferenceHours - @Mean_Diff,2))/COUNT(*) AS DECIMAL (10,2)) --Get Variance
FROM Sample_Data --================
)
--------------------------------------
--=================================================
DECLARE @SD DECIMAL(10,2) -- Get Standard Deviation (Square Root of Variance)
SET @SD = SQRT(@Variance) --=================================================
SELECT *
, DifferenceHours - @Mean_Diff AS 'Deviation'
, CASE
WHEN ((Actual_Hours - Standard_Hours)/Standard_Hours)*100>25
THEN
CASE
WHEN ABS(DifferenceHours - @Mean_Diff)<=1*@SD
THEN ''
WHEN ABS(DifferenceHours - @Mean_Diff) >1*@SD
AND ABS(DifferenceHours - @Mean_Diff) <=2*@SD
THEN 'Recommend Review'
WHEN ABS(DifferenceHours - @Mean_Diff) > 2*@SD
THEN 'Strongly Recommend Review'
ELSE ''
END
ELSE ''
END AS 'Recommendation'
FROM Sample_Data
My expected results:
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 | Recommend Review
499582 | 1.19 | 0.92 | 0.27 | -0.20 |
May 16, 2017 at 9:54 am
Without seeing the full original data, this is a guess, but I would imagine the problem is your first CASE statement, not your second, with regards to the expression:((Actual_Hours - Standard_Hours)/Standard_Hours)*100 > 25
If this results in a value of 25 or less, then the field Recommendation will return the value NULL as there is no ELSE statement.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 16, 2017 at 9:56 am
Can you please post the DDL (create table) script, sample data as an insert statement and the desired resuls
😎
May 16, 2017 at 11:48 am
Eirikur Eiriksson - Tuesday, May 16, 2017 9:56 AMCan you please post the DDL (create table) script, sample data as an insert statement and the desired resuls
😎
Done. Thanks again for taking the time to look at it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply