Nested Case statements Help

  • I have a table of tests where I have data in the tabular format:

    English 8:30 9:00 9:30

    Reading start stop

    Writing start continue stop

    Compre start stop

    I need to get start and stop time for all sections as when the value for each section is stop then the test is complete

    So the start time for the test is 8:30 and finish time is 9:30

    I was thinking something like this:

    Case when subject=english

    when section=reading and value =start then time as start_Time

    when section=reading and value =stop then time as end_Time

    when section=writing and value =start then time as start_Time

    when section=writing and value =stop then time as end_Time

    when section=compre and value =start then time as start_Time

    when section=compre and value =stop then time as end_Time

    How can I do that?

    Thanks,

    Blyzz

  • Please provide DDL, sample data and desired results as per the link in my signature in order for people to be able to help you with this.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • amar_kaur16 (1/7/2016)


    I have a table of tests where I have data in the tabular format:

    English 8:30 9:00 9:30

    Reading start stop

    Writing start continue stop

    Compre start stop

    I need to get start and stop time for all sections as when the value for each section is stop then the test is complete

    So the start time for the test is 8:30 and finish time is 9:30

    I was thinking something like this:

    Case when subject=english

    when section=reading and value =start then time as start_Time

    when section=reading and value =stop then time as end_Time

    when section=writing and value =start then time as start_Time

    when section=writing and value =stop then time as end_Time

    when section=compre and value =start then time as start_Time

    when section=compre and value =stop then time as end_Time

    How can I do that?

    Thanks,

    Blyzz

    If I understand this correctly, you need to separate your start_Time and end_Time using separate CASE statements. I don't know why so many people don't understand what CASE returns, except maybe that people don't bother to look it up.

    Start with something like the code below:

    start_Time =

    Case when subject='english'

    CASE

    WHEN section='reading' and value ='start' then time

    WHEN section='writing' and value ='start' then time

    WHEN section='compre' and value ='start' then time

    END

    end

    ,end_Time=

    Case when subject='english'

    CASE

    WHEN section='reading' and value ='stop' then time

    WHEN section='writing' and value ='stop' then time

    WHEN section='compre' and value ='stop' then time

    END

    end

    I see potential issues:

    1 - You didn't include any default conditions (using ELSE) in your query.

    2 - You're checking for subject = 'english' but not for any others.

    3 - You were missing quotation marks around your text values.

    4 - Who know what else?

    I think you now have a better starting point, but the work is not over yet.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Duplicate post!

    😎

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

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