SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Timetable query, using CASE


Timetable query, using CASE

Author
Message
madsovenielsen
madsovenielsen
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 20
Hello

I know this is a total newb question, but i have the following SQL:

SELECT FagNavn, adgangskrav, Ugedag, holdId, fagKode, CONVERT(CHAR(10), StartDato, 105) AS startDate, CONVERT(CHAR(10), slutDato, 105) AS endDate, afdeling, link, navn, deltagerBetaling, bt, niveau,
Case When Ugedag = 'Mandag'
Then frakl + ' - ' + tilkl
Else '' End AS startSlutMandag,
Case When Ugedag = 'Tirsdag'
Then frakl + ' - ' + tilkl
Else '' End AS startSlutTirsdag,
Case When Ugedag = 'Onsdag'
Then frakl + ' - ' + tilkl
Else '' End AS startSlutOnsdag,
Case When Ugedag = 'Torsdag'
Then frakl + ' - ' + tilkl
Else '' End AS startSlutTorsdag,
Case When Ugedag = 'Fredag'
Then frakl + ' - ' + tilkl
Else '' End AS startSlutFredag FROM [VisWebHoldSkema] ORDER BY afdeling



This is giving me records for each day, because of the End statement in the Switch Case, but i would like to have all the days in the same record.

Example:


+----------+-----------------+-----------------+----------------+-----------------+----------------+
| holdId | startSlutMandag | startSlutTirsdag | startSlutOnsdag | startSlutTorsdag | startSlutFredag |
+----------+-----------------+----------------+-----------------+----------------+
| 58f42ab | 09:00 - 15:30 | | | | |
+----------+-----------------+----------------+-----------------+----------------+
| 58f42ab | | | 08:00 - 15:00 | | |
+----------+-----------------+----------------+-----------------+----------------+
| 58f42ab | | | | 08:00 - 15:00 | |
+----------+-----------------+----------------+-----------------+----------------+
| 58f42ab | | | | | 08:00 - 14:30 |
+----------+-----------------+----------------+-----------------+----------------+



But this is what i want:

+----------+-----------------+-----------------+----------------+-----------------+----------------+
| holdId | startSlutMandag | startSlutTirsdag | startSlutOnsdag | startSlutTorsdag | startSlutFredag |
+----------+-----------------+-----------------+----------------+-----------------+----------------+
| 58f42ab | 09:00 - 15:30 | | 08:00 - 15:00 | 08:00 - 15:00 | 08:00 - 14:30 |
+----------+-----------------+----------------+-----------------+----------------+



DDL and INSERT's

CREATE TABLE example (
UserId nvarchar(200),
Ugedag nvarchar(200),
Frakl nvarchar(200),
Tilkl nvarchar(200)
)

INSERT INTO example (UserId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Mandag', '08:00', '16:30')
INSERT INTO example (UserId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Tirsdag', '10:00', '15:00')
INSERT INTO example (UserId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Onsdag', '09:00', '16:00')
INSERT INTO example (UserId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Torsdag', '08:00', '14:00')
INSERT INTO example (UserId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Fredag', '08:00', '14:00')



Any help is greately appriciated.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63396 Visits: 17966
Hi and welcome to the forums. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
madsovenielsen
madsovenielsen
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 20
Hey, i have updated the post, with the code and data you requested.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63396 Visits: 17966
Excellent job posting the details. I just couldn't quite see what you were trying to do before.

Something like this should get what you want.


select UserId,
MAX(Case when Ugedag = 'Mandag' then Frakl + ' - ' + Tilkl else null end) as startSlutMandag,
MAX(Case when Ugedag = 'Tirsdag' then Frakl + ' - ' + Tilkl else null end) as startSlutTirsdag,
MAX(Case when Ugedag = 'Onsdag' then Frakl + ' - ' + Tilkl else null end) as startSlutOnsdag,
MAX(Case when Ugedag = 'Torsdag' then Frakl + ' - ' + Tilkl else null end) as startSlutTorsdag,
MAX(Case when Ugedag = 'Fredag' then Frakl + ' - ' + Tilkl else null end) as startSlutFredag
from example
group by UserId



_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
parulprabu
parulprabu
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1608 Visits: 136
Use Group clause

Here is the code, this might helps you

CREATE TABLE example (
holdId nvarchar(200),
Ugedag nvarchar(200),
Frakl nvarchar(200),
Tilkl nvarchar(200)
)

INSERT INTO example (holdId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Mandag', '08:00', '16:30')
INSERT INTO example (holdId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Tirsdag', '10:00', '15:00')
INSERT INTO example (holdId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Onsdag', '09:00', '16:00')
INSERT INTO example (holdId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Torsdag', '08:00', '14:00')
INSERT INTO example (holdId, Ugedag, Frakl, Tilkl) VALUES ('58f42ab', 'Fredag', '08:00', '14:00')



SELECT holdId,--FagNavn, adgangskrav, Ugedag, holdId, fagKode, CONVERT(CHAR(10), StartDato, 105) AS startDate, CONVERT(CHAR(10), slutDato, 105) AS endDate, afdeling, link, navn, deltagerBetaling, bt, niveau,
max(Case When Ugedag = 'Mandag'
Then frakl + ' - ' + tilkl
Else '' End) AS startSlutMandag,
max(Case When Ugedag = 'Tirsdag'
Then frakl + ' - ' + tilkl
Else '' End) AS startSlutTirsdag,
max(Case When Ugedag = 'Onsdag'
Then frakl + ' - ' + tilkl
Else '' End) AS startSlutOnsdag,
max(Case When Ugedag = 'Torsdag'
Then frakl + ' - ' + tilkl
Else '' End) AS startSlutTorsdag,
max(Case When Ugedag = 'Fredag'
Then frakl + ' - ' + tilkl
Else '' End) AS startSlutFredag FROM example
group by holdId
madsovenielsen
madsovenielsen
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 20
Thanks for the quick reply.

Im getting the following error when using the query:

Msg 8120, Level 16, State 1, Line 1
Column 'VisWebHoldSkema.FagNavn' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.



Query:

SELECT FagNavn, adgangskrav, frakl, tilkl, Ugedag, holdId, fagKode, CONVERT(CHAR(10), StartDato, 105) AS startDate, CONVERT(CHAR(10), slutDato, 105) AS endDate, afdeling, link, navn, deltagerBetaling, bt, niveau,

MAX(Case when Ugedag = 'Mandag' then frakl + ' - ' + tilkl else null end) as startSlutMandag,
MAX(Case when Ugedag = 'Tirsdag' then frakl + ' - ' + tilkl else null end) as startSlutTirsdag,
MAX(Case when Ugedag = 'Onsdag' then frakl + ' - ' + tilkl else null end) as startSlutOnsdag,
MAX(Case when Ugedag = 'Torsdag' then frakl + ' - ' + tilkl else null end) as startSlutTorsdag,
MAX(Case when Ugedag = 'Fredag' then frakl + ' - ' + tilkl else null end) as startSlutFredag

FROM [VisWebHoldSkema] ORDER BY afdeling


Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63396 Visits: 17966
madsovenielsen (8/1/2013)
Thanks for the quick reply.

Im getting the following error when using the query:

Msg 8120, Level 16, State 1, Line 1
Column 'VisWebHoldSkema.FagNavn' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.



Query:

SELECT FagNavn, adgangskrav, frakl, tilkl, Ugedag, holdId, fagKode, CONVERT(CHAR(10), StartDato, 105) AS startDate, CONVERT(CHAR(10), slutDato, 105) AS endDate, afdeling, link, navn, deltagerBetaling, bt, niveau,

MAX(Case when Ugedag = 'Mandag' then frakl + ' - ' + tilkl else null end) as startSlutMandag,
MAX(Case when Ugedag = 'Tirsdag' then frakl + ' - ' + tilkl else null end) as startSlutTirsdag,
MAX(Case when Ugedag = 'Onsdag' then frakl + ' - ' + tilkl else null end) as startSlutOnsdag,
MAX(Case when Ugedag = 'Torsdag' then frakl + ' - ' + tilkl else null end) as startSlutTorsdag,
MAX(Case when Ugedag = 'Fredag' then frakl + ' - ' + tilkl else null end) as startSlutFredag

FROM [VisWebHoldSkema] ORDER BY afdeling



This is because you don't have a group by but you do have aggregate data. It is a bit difficult to say what you need to do here but as a guess you might try putting the aggregate columns and the primary key into a cte then you can join to the cte from your query. Something like this maybe?


with AggregateData as
(
select UserID,
MAX(Case when Ugedag = 'Mandag' then frakl + ' - ' + tilkl else null end) as startSlutMandag,
MAX(Case when Ugedag = 'Tirsdag' then frakl + ' - ' + tilkl else null end) as startSlutTirsdag,
MAX(Case when Ugedag = 'Onsdag' then frakl + ' - ' + tilkl else null end) as startSlutOnsdag,
MAX(Case when Ugedag = 'Torsdag' then frakl + ' - ' + tilkl else null end) as startSlutTorsdag,
MAX(Case when Ugedag = 'Fredag' then frakl + ' - ' + tilkl else null end) as startSlutFredag
from example
group by UserId
)

SELECT FagNavn, adgangskrav, frakl, tilkl, Ugedag, holdId, fagKode, CONVERT(CHAR(10), StartDato, 105) AS startDate,
CONVERT(CHAR(10), slutDato, 105) AS endDate, afdeling, link, navn, deltagerBetaling, bt, niveau,
ad.startSlutMandag, ad.startSlutTirsdag, ad.startSlutOnsdag, ad.startSlutTorsdag, ad.startSlutFredag
from VisWebHoldSkema vwhs
join AggregateData ad on ad.UserId = vwhs.FagNavn --or whatever you would use to join here
ORDER BY afdeling



_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
madsovenielsen
madsovenielsen
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 20
That query looks overly complex, is it not possible to use IF ELSE instead of the switch case?
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63396 Visits: 17966
madsovenielsen (8/4/2013)
That query looks overly complex, is it not possible to use IF ELSE instead of the switch case?


It really isn't that complex. You could instead choose to not use the cte but then you will need to group by all of the non-aggregate columns.

You can't use IF ELSE for data. The IF ELSE construct is used to control the flow of statements, that is why we use the case expression here.

What this query is doing is known as a cross tab. You can read more about the techniques that you could use by following the links in my signature.

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
madsovenielsen
madsovenielsen
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 20
Okay, thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search