Post reply

Returning records with matching days of the week

  • September 27, 2009 at 2:04 pm

    #136311

    Hi all,

    Got an interesting problem here. I have the following data as a basic example:

    if exists (select * from sysobjects where name = 'testTable' and xtype = 'U')

    drop table testTable

    go

    create table testTable(

    IDx int identity (1,1)

    ,acce_monday nchar(1)

    ,acce_tuesday nchar(1)

    ,acce_wednesday nchar(1)

    ,acce_thursday nchar(1)

    ,acce_friday nchar(1)

    ,acce_saturday nchar(1)

    ,acce_sunday nchar(1))

    insert into testTable (

    acce_monday

    ,acce_tuesday

    ,acce_wednesday

    ,acce_thursday

    ,acce_friday

    ,acce_saturday

    ,acce_sunday)

    select 'Y', null, 'Y',null,null,'Y','Y'

    union all

    select null, null, null,'Y','Y','Y',null

    union all

    select 'Y', 'Y', 'Y','Y',null,null,null

    union all

    select 'Y', 'Y', null,'Y',null,'Y',null

    Now for my challenge - I'm using a vendors application so I'm a bit limited in how I can approach this but I need to return any records where there is a weekday field with a 'Y' in TODAY's column. For example: if today (e.g. getdate()) is Monday then I want to return records 1, 3 and 4. Equally if today were Tuesday I'd like to see records 3 and 4 etc etc.

    Now because I'm trying to utilise the functionality within the application I'm limited to doing this with a 'whereclause' string that I pass to an internal escalation engine. As such I need to try and do this using one single statement that could slot in after 'select * from testTable where...' This also needs to be dynamic e.g. not one statement for every day.

    Welcome to my hell... :crazy:

    Your thoughts would be much appreciated.

    Thanks in advance.

    Brett

  • September 27, 2009 at 4:34 pm

    #1058875

    See if this works for you

    Select * from testTable

    Where ISNULL(

    Case DATEPART ( dw , getdate())

    When 1 -- Sunday

    Then acce_sunday

    When 2 -- Monday

    Then acce_monday

    When 3 -- Tuesday

    Then acce_tuesday

    When 4 -- Wednesday

    Then acce_wednesday

    When 5 -- Thursday

    Then acce_thursday

    When 6 -- Friday

    Then acce_friday

    Else -- 7 Saturday

    acce_saturday

    END , 'N') = 'Y'

  • September 27, 2009 at 8:04 pm

    #1058892

    I've got to ask... what do you do next week since this original table posted gives no clue as to date? I don't know if you can change from that denormalized table you posted but, if you can, I would really advise a change. As it currently stands, it will continue to be a plague through the life of the software.

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

  • September 28, 2009 at 3:46 am

    #1058934

    @awood02:

    That works perfectly thanks - I knew it had to be something simple like that case statement - ?I think I had just been staring at it for too long.

    Jeff Moden (9/27/2009)

    I've got to ask... what do you do next week since this original table posted gives no clue as to date? I don't know if you can change from that denormalized table you posted but, if you can, I would really advise a change. As it currently stands, it will continue to be a plague through the life of the software.

    Jeff,

    I'm not certain that we will have the kinds of problems you are refering to - but correct me if I'm wrong. Basically this is part of a site access control system used by a security desk. The records we have been looking at determine which days of week a visitor has access on - there are other attributes like time of day and start and end dates too, but those werent posted for the sake of brevity.

    So in answer to your question - if the visit period is still valid then the days they have access next week will be exactly the same as the days they have access this week etc etc.

    Thanks again for all your help folks!

    Brett

  • September 28, 2009 at 4:44 am

    #1058942

    brett.davis1 (9/28/2009)

    Jeff,

    I'm not certain that we will have the kinds of problems you are refering to - but correct me if I'm wrong.

    Now that I know what the table is actually being used for, I'll retract my question of what will you do next week, but let's stop and think about this... you're already having some of the kinds of problems that I'm referring to. If the table were properly normalized, you wouldn't have had to post your question because the solution would have been easy. Guess what the code did that answered your question? It first put the data into a normalized form and then worked on it. That's what I really mean and it will continue to be a problem in the future.

    --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 5 (of 5 total)

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