Nested Case Statements - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nested Case Statements

Author

Topic

snakedavid
Starting Member

2 Posts

Posted - 2008-05-27 : 05:24:15

Hi i am having some trouble with a nested case statement, what i want to do is set the value of a new column called Result depending on a series of case statements. Basically i want to check Test.Webstatus = 'Rd' and FinalResult = 'true' if this is true i want it to set the value in the Results field to ReportableResult + '~' + ReportableUnitDisplay then go through all the limits fields adding either the value of the field or 'blank' onto the end of the value in the Results field, depending on if the limits field has Null or a value in it. Producing a value in the Results field similiar to: 10~kg:10:5:2:1 or 10~kg:blank:5:blank:1 etc


select ClientRef, Sample.WebStatus as SampleStatus, Analysis, FinalResult, Test.WebStatus,
'Result' = Case
	when Test.WebStatus = 'Rd' and FinalResult = 'true' then 
		Case
			Case
				when UpperCriticalLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
				else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperCriticalLimit
			end
			Case
				when UpperWarningLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
				else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperWarningLimit
			end
			Case
				when LowerWarningLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
				else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperWarningLimit
			end
			Case
				when LowerCriticalLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
				else ReportableResult + '~' + ReportableUnitDisplay + ':' + LowerCriticalLimit
			end
		end
		when FinalResult = 'false' then Null
		else Test.WebStatus
from Job 
inner join sample on Job.JobID = Sample.JobID
inner join Test on Sample.SampleID = Test.SampleID
left join Result on Test.TestID = Result.TestID

Any Advice Would Be Great
Thanks
David

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 05:32:22

[code]SELECT ClientRef,
Sample.WebStatus as SampleStatus,
Analysis,
FinalResult,
Test.WebStatus,
CASE
WHEN FinalResult = 'False' THEN NULL
WHEN Test.WebStatus = 'rd' AND FinalResult = 'True' THEN ReportableResult + '~' + ReportableUnitDisplay + ':' + COALESCE(CAST(UpperCriticalLimit AS VARCHAR(12)), CAST(UpperWarningLimit AS VARCHAR(12)), CAST(LowerWarningLimit AS VARCHAR(12)), CAST(LowerCriticalLimit AS VARCHAR(12)), 'blank')
ELSE Test.WebStatus
END AS [Result]
FROM Job
INNER JOIN Sample ON Sample.JobID = Job.JobID
INNER JOIN Test ON Test.SampleID = Sample.SampleID
LEFT JOIN Result ON Result.TestID = Test.TestID[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-27 : 05:36:39

quote:

Originally posted by snakedavid

select ClientRef, Sample.WebStatus as SampleStatus, Analysis, FinalResult, Test.WebStatus,
'Result' = Case
	when Test.WebStatus = 'Rd' and FinalResult = 'true' then 
		Case
			Case
				when UpperCriticalLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
				else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperCriticalLimit
			end
			Case
				when UpperWarningLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
				else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperWarningLimit
			end
			Case
				when LowerWarningLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
				else ReportableResult + '~' + ReportableUnitDisplay + ':' + UpperWarningLimit
			end
			Case
				when LowerCriticalLimit is null then ReportableResult + '~' + ReportableUnitDisplay + ':blank'
				else ReportableResult + '~' + ReportableUnitDisplay + ':' + LowerCriticalLimit
			end
		end
		when FinalResult = 'false' then Null
		else Test.WebStatus
from Job 
inner join sample on Job.JobID = Sample.JobID
inner join Test on Sample.SampleID = Test.SampleID
left join Result on Test.TestID = Result.TestID

E 12°55'05.25"
N 56°04'39.16"

snakedavid
Starting Member

2 Posts

Posted - 2008-05-27 : 06:07:29

Thank You Very Much, Works Perfectly
David

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources