January 7, 2016 at 2:04 pm
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
January 7, 2016 at 2:13 pm
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
January 7, 2016 at 2:36 pm
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.
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]
January 10, 2016 at 7:33 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply