Nested Case statements Help

Question

Nested Case statements Help

amar_kaur16

SSC Eights!

Points: 984
More actions
January 7, 2016 at 2:05 pm

#306758

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

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

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

First, in SQL Server CASE is not a statement but an expression. That means that "when section=reading and value =start then time as start_Time" in itself will already throw a syntax error in any context.

Second, your requirement is completely unclear to me. Can you please post the following:

1. CREATE TABLE statements for all tables involved in the problem (okay to anonimyze and to leave out irrelevant columns)

2. INSERT statements with representative test data to illustrate the issue

3. Expected results (along with an explanation - what's obvious to you may not bo to others).

Please do copy/paste/execute the script for steps 1 and 2 in an empty test database before posting.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Hugo Kornelis (1/7/2016)
First, in SQL Server CASE is not a statement but an expression. That means that "when section=reading and value =start then time as start_Time" in itself will already throw a syntax error in any context.
Second, your requirement is completely unclear to me. Can you please post the following:
1. CREATE TABLE statements for all tables involved in the problem (okay to anonimyze and to leave out irrelevant columns)
2. INSERT statements with representative test data to illustrate the issue
3. Expected results (along with an explanation - what's obvious to you may not bo to others).
Please do copy/paste/execute the script for steps 1 and 2 in an empty test database before posting.

Please see the article at the first link in my signature line below for more detail on what Hugo wrote above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

Echoing Jeff and Hugo, we need more information in order to help with this problem.

😎

My thought is that this nested case approach may not be the correct one for this kind of problem, here is a quick sample data set and a query that possibly could fit at least some of it, should be enough to get you started.

USE tempdb;

GO

SET NOCOUNT ON;

/*

SAMPLE "WILD GUESS" DATA

*/

IF OBJECT_ID(N'dbo.TBL_GUESS_SAMPLE') IS NOT NULL DROP TABLE dbo.TBL_GUESS_SAMPLE;

CREATE TABLE dbo.TBL_GUESS_SAMPLE

(

GS_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_GUESS_SAMPLE_GS_ID PRIMARY KEY CLUSTERED

,GS_SUBJECT VARCHAR(50) NOT NULL

,GS_VALUE VARCHAR(50) NOT NULL

,GS_TIME TIME(0) NULL

);

INSERT INTO dbo.TBL_GUESS_SAMPLE

(GS_SUBJECT,GS_VALUE,GS_TIME)

VALUES ('English' ,'start' ,'08:05')

,('Reading' ,'start' ,'08:15')

,('Writing' ,'start' ,'08:25')

,('English' ,'continue','09:05')

,('Writing' ,'continue','09:05')

,('English' ,'continue','10:05')

,('Reading' ,'stop' ,'10:05')

,('Writing' ,'continue','10:05')

,('English' ,'continue','11:05')

,('Writing' ,'continue','11:05')

,('Writing' ,'stop' ,'12:35')

,('English' ,'stop' ,'12:55')

;

SELECT

GS.GS_SUBJECT

,MIN(GS_TIME) AS START_TIME

,MAX(GS_TIME) AS STOP_TIME

FROM dbo.TBL_GUESS_SAMPLE GS

WHERE GS.GS_VALUE IN ('start','stop')

GROUP BY GS.GS_SUBJECT;

Results

GS_SUBJECT START_TIME STOP_TIME

------------ ---------------- ----------------

English 08:05:00 12:55:00

Reading 08:15:00 10:05:00

Writing 08:25:00 12:35:00

MMartin1 One Orange Chip Points: 27795 More actions · Answer 4

Borrowing from Eirikur's code a little bit,

Maybe you are looking for something like this ?

create table #sampleScheduleData

(

scheduledClass VARCHAR(50)

,scheduledTime TIME(0)

,scheduledEvent VARCHAR(50)

);

INSERT INTO #sampleScheduleData

VALUES ('English' ,'08:30', 'start' )

,('Reading' ,'08:30', 'start' )

,('Writing' ,'08:30', 'start' )

,('English' ,'09:00', 'continue')

,('Reading' ,'09:00', 'continue')

,('Writing' ,'09:00', 'continue')

,('English' ,'12:35', 'stop')

,('Reading' ,'12:35', 'stop')

,('Writing' ,'12:35', 'continue')

;

SELECT

scheduledClass, max(case when scheduledTime = '08:30' then scheduledEvent else '' end) as _830Time

, max(case when scheduledTime = '09:00' then scheduledEvent else '' end) as _0900Time

, max(case when scheduledTime = '12:35' then scheduledEvent else '' end) as _1235Time

from #sampleScheduleData

group by scheduledClass

----------------------------------------------------