Multiple Conditions on a Case Statement

Question

Post reply

Multiple Conditions on a Case Statement

Stylez

SSCrazy

Points: 2195
More actions
December 12, 2014 at 9:57 am

#104249

Good Evening Guys,
I would like to ask if is it possible or is my syntax correct for the Select Case Statement below? what I need to achieve is to satisfy 2 conditions before executing a select query for this report
See my Select Case When Statement below
select
CASE WHEN PT.datatype = 7 And MVA.DateTimeValue is not null
then MVA.DateTimeValue
CASE WHEN PT.datatype = 5 And MVA.StringValue is not null
then MVA.StringValue
CASE WHEN PT.datatype = 5 And MVA.NumericValue is not null
then MVA.NumericValue
Else Null End as StoredValue
from ctc_v_processtags pt
inner join dbo.ctc_mde_valueaudit as mva on mva.UtagId = pt.utagid
inner join dbo.ctc_mde_header as mh on mh.id = mva.mdeheaderid
where mh.StartTime >= convert(datetime,@StartTime)
and mh.StartTime <= Convert(datetime,@EndTime)
and mva.AuditTime = mva.AuditTime
and pt.dssourceid in (193,19)
order by utagname desc
I would gladly appreciate Any help/critique that will come from you
Best Regards,
Noel

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You just need a single CASE.

SELECT CASE

WHEN PT.datatype = 7 AND MVA.DateTimeValue IS NOT NULL

THEN MVA.DateTimeValue

WHEN PT.datatype = 5 AND MVA.StringValue IS NOT NULL

THEN MVA.StringValue

WHEN PT.datatype = 5 AND MVA.NumericValue IS NOT NULL

THEN MVA.NumericValue

ELSE NULL

END AS StoredValue

FROM ctc_v_processtags pt

INNER JOIN dbo.ctc_mde_valueaudit AS mva ON mva.UtagId = pt.utagid

INNER JOIN dbo.ctc_mde_header AS mh ON mh.id = mva.mdeheaderid

WHERE mh.StartTime >= convert(DATETIME, @StartTime)

AND mh.StartTime <= Convert(DATETIME, @EndTime)

AND mva.AuditTime = mva.AuditTime

AND pt.dssourceid IN (193,19)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Stylez SSCrazy Points: 2195 More actions · Answer 2

Much Thanks!

would it be possible if I could also do this?

Case When Condition 1 = true then When Condition 2 = true then Execute Anything....

I would like to know what is the syntax for the case when condition I stated above if its possible

Best Regards

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Stylez (12/12/2014)
Much Thanks!
would it be possible if I could also do this?
Case When Condition 1 = true then When Condition 2 = true then Execute Anything....
I would like to know what is the syntax for the case when condition I stated above if its possible
Best Regards

Case is an expression which means it is used to control the results in a single column. It cannot be used to control flow and execute some other code when a row meets a certain condition.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Stylez SSCrazy Points: 2195 More actions · Answer 4

Thank you all the solution helped me a lot!

Best Regards,

Noel