Timetable query, using CASE

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

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

  • Hey, i have updated the post, with the code and data you requested.

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

  • 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

  • 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

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

  • That query looks overly complex, is it not possible to use IF ELSE instead of the switch case?

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

  • Okay, thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

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