October 29, 2015 at 2:52 am
Can someone help me with the following. The below would work if all of the values in A.Value were numbers but they are not. So I need to restrict the view to only look at the following measures but still show all the other row.
WHERE [Measure] IN ('RTT-01','RTT-04','RTT-07')
SELECT
M.[Description]
,A.*
,M.Threshold
,M.[Threshold Direction]
,CASE
WHEN M.[Threshold Direction] = '>' AND A.Value > M.Threshold THEN 'GREEN'
WHEN M.[Threshold Direction] = '>' AND A.Value < M.Threshold THEN 'RED'
ELSE NULL END
FROM [tbl_qd_audit] A
JOIN [tbl_qd_measures] AS M
ON A.[Measure] = M.[Measure]
ORDER BY [Month], [Measure]
So I need to restrict the view to only look at the following measures.
WHERE [Measure] IN ('RTT-01','RTT-04','RTT-07')
SELECT [Value]
FROM [tbl_qd_audit]
WHERE [Measure] IN ('RTT-01','RTT-04','RTT-07')
Is there any way that I can create a select statement in the case when to only look for them measures that I know contain numbers?
I appreciate any help.
October 29, 2015 at 3:02 am
Is it not just a case of adding the where clause into the script you already have
SELECT
M.[Description]
,A.*
,M.Threshold
,M.[Threshold Direction]
,CASE
WHEN M.[Threshold Direction] = '>' AND A.Value > M.Threshold THEN 'GREEN'
WHEN M.[Threshold Direction] = '>' AND A.Value < M.Threshold THEN 'RED'
ELSE NULL END
FROM [tbl_qd_audit] A
JOIN [tbl_qd_measures] AS M
ON A.[Measure] = M.[Measure]
WHERE A.[Measure] IN ('RTT-01','RTT-04','RTT-07')
ORDER BY [Month], [Measure]
October 29, 2015 at 3:05 am
No because this will only bring back 3 measures. I want all of the measures to come back but only the 3 I have mentioned to calculate the case when because the values of them contain numbers.
Does that make sense?
October 29, 2015 at 3:10 am
I have been able to get around it with a UNION ALL.
Can anyone think of any other way?
October 29, 2015 at 3:13 am
So you need to restrict the case statement to 3 measures not the view?
Can you post the definitions of [tbl_qd_audit] and [tbl_qd_measures] with some sample data and an expected outcome
October 29, 2015 at 3:32 am
I have got the attached outcome I expected with the following code. I was just wondering if it could be more dynamic?
-- Greater than '>' thresholds.
SELECT
M.[Description]
,A.*
,M.Threshold
,M.[Threshold Direction]
,CASE
WHEN A.Value > M.Threshold THEN 'LimeGreen'
ELSE 'Red' END AS 'Colour'
FROM [tbl_qd_audit] A
JOIN [tbl_qd_measures] AS M
ON A.[Measure] = M.[Measure]
WHERE [CCG] = '01A'
AND A.[Measure] IN ('RTT-01','RTT-04','RTT-07')
UNION ALL
-- Less than '<' thresholds.
SELECT
M.[Description]
,A.*
,M.Threshold
,M.[Threshold Direction]
,CASE
WHEN A.Value < M.Threshold THEN 'LimeGreen'
ELSE 'Red' END AS 'Colour'
FROM [tbl_qd_audit] A
JOIN [tbl_qd_measures] AS M
ON A.[Measure] = M.[Measure]
WHERE [CCG] = '01A'
AND A.[Measure] IN ('N2F-03')
UNION ALL
-- Non threshold measures.
SELECT
M.[Description]
,A.*
,M.Threshold
,M.[Threshold Direction]
,'White' AS 'Colour'
FROM [tbl_qd_audit] A
JOIN [tbl_qd_measures] AS M
ON A.[Measure] = M.[Measure]
WHERE [CCG] = '01A'
AND A.[Measure] NOT IN ('RTT-01','RTT-04','RTT-07','N2F-03')
ORDER BY [Month], [Measure]
October 29, 2015 at 3:47 am
Should do it with a derived select
SELECT
M.[Description]
,A.*
,M.Threshold
,M.[Threshold Direction]
,dev1.[Colour]
ELSE NULL END
FROM [tbl_qd_audit] A
JOIN [tbl_qd_measures] AS M
ON A.[Measure] = M.[Measure]
LEFT JOIN (
SELECT A.Measure,
CASE
WHEN M.[Threshold Direction] = '>' AND A.Value > M.Threshold THEN 'GREEN'
WHEN M.[Threshold Direction] = '>' AND A.Value < M.Threshold THEN 'RED'
ELSE NULL END AS [Colour]
FROM [tbl_qd_audit] A
JOIN [tbl_qd_measures] AS M
ON A.[Measure] = M.[Measure]
WHERE A.[Measure] IN ('RTT-01','RTT-04','RTT-07')
) dev1 on a.measure = dev1.measure
ORDER BY [Month], [Measure]
Might be an even nicer way of doing it compared with 3 unions.
If you post table definitions (CREATE TABLE) and some sample data sure could come up with a better solution.
October 29, 2015 at 5:09 am
Is this what you mean?
SELECT
M.[Description]
,A.*
,M.Threshold
,M.[Threshold Direction]
,CASE
WHEN A.[Measure] IN ('RTT-01','RTT-04','RTT-07') AND A.Value > M.Threshold THEN 'LimeGreen'
WHEN A.[Measure] IN ('RTT-01','RTT-04','RTT-07') THEN 'Red'
WHEN A.[Measure] IN ('N2F-03') AND A.Value < M.Threshold THEN 'LimeGreen'
WHEN A.[Measure] IN ('N2F-03') THEN 'Red'
WHEN A.[Measure] NOT IN ('RTT-01','RTT-04','RTT-07','N2F-03') THEN 'White'
ELSE '' END AS [Colour]
FROM [tbl_qd_audit] A
JOIN [tbl_qd_measures] AS M
ON A.[Measure] = M.[Measure]
WHERE [CCG] = '01A'
ORDER BY [Month], [Measure]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 29, 2015 at 5:56 am
Hi ChrisM@Work.
I am still getting the error
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
The data in A.Value is a VARCHAR and contains Y, 100%, 98.01 etc so a mix of data.
Thanks again.
October 29, 2015 at 6:26 am
dramaqueen (10/29/2015)
Hi ChrisM@Work.I am still getting the error
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
The data in A.Value is a VARCHAR and contains Y, 100%, 98.01 etc so a mix of data.
Thanks again.
So this expression will have to change: A.Value < M.Threshold
Without data to play with I'm reluctant to make guesses. Can you tell us some more about the data? It might help folks to suggest how this comparison might be adjusted.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 29, 2015 at 8:18 am
ChrisM's solution is correct.
The error will be due to inconsistances between your Measures and numeric/nonnumeric Values
You will need to add extra WHEN conditions to avoid implicit cast errors
i.e
WHEN A.[Measure] IN ('RTT-01','RTT-04','RTT-07') AND A.Value NOT LIKE '%[0-9.]%' THEN ''
Also it is good practice to explicitly cast Value to the Threshold datatype
Far away is close at hand in the images of elsewhere.
Anon.
October 29, 2015 at 8:38 am
Hi David,
I have added that extra where clause. I agree with what you are saying but it still come back with an error.
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
SELECT
M.[Description]
,A.*
,M.Threshold
,M.[Threshold Direction]
,CASE
WHEN A.[Measure] IN ('RTT-01','RTT-04','RTT-07') AND A.Value > M.Threshold THEN 'LimeGreen'
WHEN A.[Measure] IN ('RTT-01','RTT-04','RTT-07') THEN 'Red'
WHEN A.[Measure] IN ('N2F-03') AND A.Value < M.Threshold THEN 'LimeGreen'
WHEN A.[Measure] IN ('N2F-03') THEN 'Red'
WHEN A.[Measure] NOT IN ('RTT-01','RTT-04','RTT-07','N2F-03') THEN 'White'
WHEN A.[Measure] IN ('RTT-01','RTT-04','RTT-07') AND A.Value NOT LIKE '%[0-9.]%' THEN ''
ELSE '' END AS [Colour]
FROM [tbl_qd_audit] A
JOIN [tbl_qd_measures] AS M
ON A.[Measure] = M.[Measure]
WHERE [CCG] = '01A'
ORDER BY [Month], [Measure]
Thanks again,
Paul
October 29, 2015 at 8:41 am
WHEN A.[Measure] IN ('N2F-03') AND A.Value < M.Threshold THEN 'LimeGreen'
Are all the value fields for this measure numbers or not?
October 29, 2015 at 8:57 am
dramaqueen (10/29/2015)
I have added that extra where clause. I agree with what you are saying but it still come back with an error.
Sorry forgot to mention that you will have to put an extra WHEN for each Measure that has a mix or numeric/nonnumeric data for Value, I only posted an entry for the first WHEN.
Far away is close at hand in the images of elsewhere.
Anon.
October 29, 2015 at 9:01 am
Also you need to put the WHENs in the order you want them tested
ie
WHEN A.[Measure] IN ('RTT-01','RTT-04','RTT-07') AND A.Value NOT LIKE '%[0-9.]%' THEN ''
WHEN A.[Measure] IN ('RTT-01','RTT-04','RTT-07') AND A.Value > M.Threshold THEN 'LimeGreen'
WHEN A.[Measure] IN ('RTT-01','RTT-04','RTT-07') THEN 'Red'
WHEN A.[Measure] IN ('N2F-03') AND A.Value NOT LIKE '%[0-9.]%' THEN ''
WHEN A.[Measure] IN ('N2F-03') AND A.Value < M.Threshold THEN 'LimeGreen'
WHEN A.[Measure] IN ('N2F-03') THEN 'Red'
WHEN A.[Measure] NOT IN ('RTT-01','RTT-04','RTT-07','N2F-03') THEN 'White'
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply