#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    1
    Rep Power
    0

    Lastname, Firstname switch


    Hi,

    Please help, im not at all good with SQL but I have created the query below. It pulls information from a room booking system to display on a digital signage product.

    At present the 'Name' field outputs as lastname, firstname. I really need it to display as Firstname Lastname but still under the one column.

    Can someone take a look please? It would be greatly appreciated.

    SELECT HOST9006.DESCRIPTION, HOST0110.ROOMNAME, HOST0140.NAME, dateadd(mi, HOST0120.STARTMINS, HOST0120.MTGDATE) AS ASTART,

    dateadd(mi, HOST0120.ENDMINS, HOST0120.MTGDATE) AS AFINISH, CONVERT (VARCHAR(5), dateadd(mi, HOST0120.STARTMINS,

    HOST0120.MTGDATE) ,108) AS START, CONVERT (VARCHAR(5), dateadd(mi, HOST0120.ENDMINS, HOST0120.MTGDATE) ,108) AS FINISH,

    HOST0120.MTGKEY, HOST0120.HIPTYPE, HOST0120.ROOMKEY

    FROM HOST0140
    INNER JOIN HOST0120
    ON HOST0120.OWNERKEY=HOST0140.PERSONKEY
    INNER JOIN HOST9006
    ON HOST9006.KEYVALUE=HOST0120.MTGSTATE
    INNER JOIN HOST0110
    ON HOST0110.ROOMKEY=HOST0120.ROOMKEY

    WHERE CANCELSTATE='0'

    AND MTGDATE >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
    AND MTGDATE < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1)
    AND MTGSTATE <> '11'
    ORDER BY START

    Thanks
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    335
    Rep Power
    246
    If the column 'NAME' has the data stored in that fashion you need to apply logic to it via a procedure to split it and rearrange it.

    have a look here : http://stackoverflow.com/questions/5...-column-values

    That should help with getting the column outputted as you prefer.
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    if anyone else wants to tackle this problem, make sure you read this thread first, so you're not duplicating your effort needlessly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    335
    Rep Power
    246
    ah , I see you had already provided a charindex method!

    Cheers r937!
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    that wasn't me, and i'm guessing it wasn't the original poster either

    thus illustrating the evil of cross-posting, imagine if you had sat down and tried to work out that solution yourself for the guy, and yet someone else already did

    i hate cross-posts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2009
    Posts
    335
    Rep Power
    246
    I understand your sentiment for cross-posting, but sometimes, I do it myself.

    Especially when I am after input/advice regarding a situation rather than a specific solution to a problem.

    The more input and advice from as many people as possible the better.

    The other issue can be the timezone difference, I might be after a quick answer due to a serious problem, and cannot wait for USA to wake up!

    However, If I cross post forums, I will always come back and tell the forum without an answer, that I have found one and what it was, even if it means linking to a competitors forum!
    Free MP3 Dance Music Downloads

    To err is human; To really balls things up you need Microsoft!

IMN logo majestic logo threadwatch logo seochat tools logo