Post reply

Cursor or special select statement?

  • December 24, 2015 at 2:28 pm

    #314469

    I have a table that holds all my Customers with details such as Cust#,name, address, city, DeliveryTimes and so on.

    I have a second table that has Cust#, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday. Each of the Monday-Sunday fields are set to Boolean

    (I didn't create this database)

    In any event, I need to build a view that would show the customers information and the delivery dates that they are serviced.

    So I have the complete view created and running with one exception, the delivery dates. I don't need the True of False values, I only need to use a Mo for Monday, Tu for Tuesday, We, Th, Fr, Sa and Su respectively but ONLY where the value is set to True.

    I was thinking of a cursor but I am confused on how I can get this accomplished. I am not just looking for the answer, I really want to understand the solution so that I know how it's done when I need it again.

  • December 24, 2015 at 6:05 pm

    #1848004

    Wouldn't a CASE statement do the trick here?

    --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)

  • December 25, 2015 at 12:55 pm

    #1848059

    SELECT c.Cust#, c.Name, c.Address, --c....

    STUFF(

    CASE WHEN cd.Monday = 1 THEN ',Mo' ELSE '' END +

    CASE WHEN cd.Tuesday = 1 THEN ',Tu' ELSE '' END +

    CASE WHEN cd.Wednesday = 1 THEN ',We' ELSE '' END +

    CASE WHEN cd.Thursday = 1 THEN ',Th' ELSE '' END +

    CASE WHEN cd.Friday = 1 THEN ',Fr' ELSE '' END +

    CASE WHEN cd.Saturday = 1 THEN ',Sa' ELSE '' END +

    CASE WHEN cd.Sunday = 1 THEN ',Su' ELSE '' END

    , 1, 1, '') AS Deliv_Days

    FROM Customers c

    LEFT OUTER JOIN CustomerDays cd ON cd.Cust# = c.Cust#

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • December 28, 2015 at 9:31 am

    #1848294

    Thank you, I even understood it

  • December 28, 2015 at 10:30 am

    #1848312

    Hyabusact (12/28/2015)

    Thank you, I even understood it

    Scott's code works a treat for what you've done but storing Boolean markers horizontally in a table doesn't lend itself well to other things or even the thing that you intended. I also don't believe that you actually need the second table at all (the one with the Boolean flags in it).

    Can you post the code for the view and a query that selects from it? We might be able to do something a bit more efficient especially since you won't actually need to maintain the second table anymore.

    --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)

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

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