Multiple Conditions on a Case Statement

  • 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

  • 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
  • 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

  • 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/

  • Thank you all the solution helped me a lot!

    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