Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Sep 2013
    Posts
    1

    Question Unanswered: Help with day of week comparison for contact availability

    Hey all, I have a problem I'm trying to figure out here. I am trying to figure out the availability of a contact by comparing their available flag to the current day of the week. IE, the contact has 7 BIT fields in the table,1 for each day of the week, being T or F depending on if they are available. I'm trying to figure out how to read the correct field based on the day of the week to see if their available (T) that day for a notification. Each field name in the table is as such: mon, tue, wed, etc. I can get the current DOW from sql and trim it to the same length and case of the field names to try and figure out which one I need to check whether that fields contents are true or not, but I'm stumped on where to go from here. I can basically get around sql server MOST of the time, but I am by no means a pro, and this one has me stumped. I'm sure I have to be missing something obvious here. Any help is greatly appreciated!

    Kevin

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have a play with this and ask any questions you might have
    Code:
    DECLARE @contacts_availability table (
       contact_name varchar(10) NOT NULL
     , mon          bit         NOT NULL DEFAULT 0
     , tue          bit         NOT NULL DEFAULT 0
     , wed          bit         NOT NULL DEFAULT 0
     , thu          bit         NOT NULL DEFAULT 0
     , fri          bit         NOT NULL DEFAULT 0
     , sat          bit         NOT NULL DEFAULT 0
     , sun          bit         NOT NULL DEFAULT 0
    );
    
    INSERT INTO @contacts_availability (contact_name, mon, tue, wed, thu, fri, sat, sun)
      VALUES ('John'  , 1, 0, 0, 1, 1, 1, 1)
           , ('Paul'  , 1, 0, 1, 0, 1, 0, 1)
           , ('George', 0, 1, 1, 1, 1, 0, 0)
           , ('Ringo' , 0, 0, 0, 1, 0, 1, 0);
    
    DECLARE @today date = Current_Timestamp;
    
    ; WITH availability AS (
      SELECT contact_name
           , available
           , day_of_week
           , CASE DateDiff(dd, 0, @today) % 7
               WHEN 0 THEN 'mon'
               WHEN 1 THEN 'tue'
               WHEN 2 THEN 'wed'
               WHEN 3 THEN 'thu'
               WHEN 4 THEN 'fri'
               WHEN 5 THEN 'sat'
               WHEN 6 THEN 'sun'
             END as current_day_of_week
      FROM   @contacts_availability As contacts_availability
      UNPIVOT (
               available FOR day_of_week
               IN (mon, tue, wed, thu, fri, sat, sun)
              ) As upvt
    )
    SELECT contact_name
         , available
         , day_of_week
         , current_day_of_week
    FROM   availability
    WHERE  day_of_week = current_day_of_week[
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Bitmasks:
    Code:
    DECLARE @contacts_availability table (
       contact_name varchar(10) NOT NULL
     , mon          bit         NOT NULL DEFAULT 0
     , tue          bit         NOT NULL DEFAULT 0
     , wed          bit         NOT NULL DEFAULT 0
     , thu          bit         NOT NULL DEFAULT 0
     , fri          bit         NOT NULL DEFAULT 0
     , sat          bit         NOT NULL DEFAULT 0
     , sun          bit         NOT NULL DEFAULT 0
    );
    
    INSERT INTO @contacts_availability (contact_name, mon, tue, wed, thu, fri, sat, sun)
      VALUES ('John'  , 1, 0, 0, 1, 1, 1, 1)
           , ('Paul'  , 1, 0, 1, 0, 1, 0, 1)
           , ('George', 0, 1, 1, 1, 1, 0, 0)
           , ('Ringo' , 0, 0, 0, 1, 0, 1, 0);
    
    select	contact_name,
    	case when (sun + (mon * 2) + (tue * 4) + (wed * 8) + (thu * 16) + (fri * 32) + (sat * 64)) & power(cast(2 as bigint), DATEPART(weekday, getdate())-1) =power(cast(2 as bigint), DATEPART(weekday, getdate())-1) then 1 else 0 end as IsAvailableToday
    from	@contacts_availability
    ...and if you stored the data as a bitmask, or added a calculated bitmask column to the table, this would be trivial.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I see the problem from a whole different angle...

    Kevin is asking how to write queries against his data that perform decently. The problem is that in the bit based designs some of his data is stored as metadata. With separate columns the "days worked" is in the table metadata. With the bit mask idea, then the data is stored within the application metadata... knowledge of what days a person works is known to the application but only the bitmap which contains the derivative data is stored in the database.

    I'd prefer to see data treated as data, so I'd use a schema like:
    Code:
    CREATE TABLE Contacts (
       Name         VARCHAR(30)     NOT NULL
       PRIMARY KEY (Name)
       )
    
    CREATE TABLE Days (
       Name         CHAR(3)        NOT NULL
       PRIMARY KEY (Name)
       )
    
    CREATE TABLE ContactWorkingDays(
       Id           INT             IDENTITY
       PRIMARY KEY (id)
    ,  ContactName  VARCHAR(30)     NOT NULL
       FOREIGN KEY (ContactName)
          REFERENCES Contacts (Name)
    ,  WorkingDay   CHAR(3)         NOT NULL
       FOREIGN KEY (WorkingDay)
          REFERENCES Days (Name)
       )
    
    INSERT INTO Days (Name)
       VALUES ('Sun'), ('Mon'), ('Tue'), ('Wed'), ('Thu'), ('Fri'), ('Sat')
    
    INSERT INTO Contacts (Name)
       VALUES ('John'), ('Paul'), ('George'), ('Ringo')
    
    INSERT INTO ContactWorkingDays (ContactName, WorkingDay)
       VALUES
          ('John', 'Mon'), ('Paul', 'Mon'), ('George', 'Tue'), ('Paul', 'Wed')
    ,     ('George', 'Wed'), ('John', 'Thu'), ('George', 'Thu'), ('Ringo',  'Thu')
    ,     ('John', 'Fri'), ('Paul', 'Fri'), ('George', 'Fri'), ('John', 'Sat')
    ,     ('Ringo',  'Sat'), ('John', 'Sun'), ('Paul', 'Sun')
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Last I understood how these computer-thingies worked, all the data was stored as 1s and 0s, and needed something to interpret it.

    The bitmask solution is MUCH more robust, being able to answer a variety of questions that would require complex coding with a normalized table.

    If you want transparency to the user, create a view that materializes the bitmask.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman View Post
    Last I understood how these computer-thingies worked, all the data was stored as 1s and 0s, and needed something to interpret it.
    Agreed, and I once programmed a PDP-8 and an Altair using paddle switches... I don't do that anymore.

    I personally find:
    Code:
    SELECT ContactName
       FROM ContactWorkingDays
       WHERE  WorkingDay = Left(DateName(dw, GetDate()), 3)
    to be much easier to read than:
    Code:
    SELECT	contact_name,
    	CASE
    	   WHEN (sun + (mon * 2) + (tue * 4) 
    +         (wed * 8) + (thu * 16) + (fri * 32) + (sat * 64)) 
    &         Power(Cast(2 as bigint), DATEPART(weekday, getdate())-1) 
    =         Power(Cast(2 as bigint), DATEPART(weekday, getdate())-1)
              THEN 1
          ELSE 0 END AS IsAvailableToday
       FROM @contacts_available
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    >> I am trying to figure out the availability of a contact by comparing their available flag to the current day of the week. <<

    Flags are how we wrote in assembly language. This is not a good way to use SQL. We also do not do display formatting in the database; that is what a presentation layer does.

    >> IE, the contact has 7 BIT fields [sic] [sic] in the table,1 for each day of the week, being T or F depending on if they are available. I'm trying to figure out how to read the correct field [sic] based on the day of the week to see if their available (T) that day for a notification. <<

    The proprietary bit data type is a numeric in T-SQL. The 'T' and 'F' make no sense.

    >> Each field [sic] name in the table is as such: mon, tue, wed, etc. I can get the current DOW from SQL and trim it to the same length and case of the field [sic] names to try and figure out which one I need to check whether that fields [sic] contents are true or not, but I'm stumped on where to go from here. <<

    I would build a calendar table with a ISO-8601 week-within-year format column in addition to the usual Common Era date. This format is 'yyyyWww-d' where yyyy is the year, W is a separator token, ww is (01-53) week number and d is (1-7) day of the week.

    There are several websites with calendars you can cut & paste, but you can start your search with: Week number

    Now just keep the dates for each contact.


    CREATE TABLE Contact_Availability
    (contact_name VARCHAR(35) NOT NULL,
    available_date DATE NOT NULL,
    PRIMARY KEY (contact_name, available_date));

    Get the current ISO week number with a simple subquery, then find the calendar dates for that week (untested)

    SELECT contact_name, available_date
    FROM Contact_Availability
    WHERE available_date
    IN (SELECT C2.cal_date
    FROM Calendar AS C1, Calendar AS C2
    WHERE C1.cal_date = (CURRENT_TIMESTAMP AS DATE)
    AND SUBSTRING (C1.week_date, 1, 7)
    = SUBSTRING (C2.week_date, 1, 7));

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If the data was stored as a bitmask, the query would be much simpler:

    Code:
    SELECT	contact_name
       FROM @contacts_available
    where	AvailabilityMask & Power(Cast(2 as bigint), DATEPART(weekday, getdate())-1) = Power(Cast(2 as bigint), DATEPART(weekday, getdate())-1)
    ...but my assertion was that this solution is more ROBUST. This same query can be used find availability profiles matching any combination of days. That would be much more cumbersome to write with the dates normalized.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    This same query can be used find availability profiles matching any combination of days. That would be much more cumbersome to write with the dates normalized.
    I do not think so. Given a random mix of calendar dates, we can use a portable query without having to assume a particular hardware for a bit mask.

    SELECT contact_name, available_date
    FROM Contact_Availability
    WHERE available_date*
    IN (<< date list>> );

    If we want a subset of days in a week we can a regular expression. For example, this is 2013W39-2 (2013-09-23), so to look ahead for the next three weeks on any Monday, Tuesday or Friday we can use:

    SELECT contact_name, available_date
    FROM Contact_Availability AS A, Calendar AS C
    WHERE contact_name = @in_contact_name
    AND A.available_date*= C.cal_date
    AND (C.week_date LIKE '2013W39-[125]';
    OR C.week_date LIKE '2013W4[01]-[125]');

    If T-SQL had the SIMILAR TO predicate, we would put the the OR into the regular expression. Please write this in a human understandable, fully portable and maintainable expression with bit mask.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Celko View Post
    so to look ahead for the next three weeks on any Monday, Tuesday or Friday we can use:
    My proposed schema works on the assumption that this is a weekly schedule, and that it repeats every week. Based on that assumption, I'd really prefer to read:
    Code:
    SELECT *
       FROM ContactWorkingDays AS Mon
       WHERE WorkingDay IN ('Mon', 'Tue', 'Fri')
    ...than either Blindman's or Celko's alternatives.

    I'd really love to hear the thoughts of the OP (Original Poster, KevinBednar)

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Nordic programmers?

    My proposed schema works on the assumption that this is a weekly schedule, and that it repeats every week.
    I can use mydate LIKE '____W__-[125]' to get search for a generic week. Matching on underscore is really fast.

    The Nordics use the week_date a lot. I ran into as default in a lotof places --school schedules, fiscal calendars, etc.

    I guess when you are inside the arctic circle and your days and seasons are messed up, this and 24 hour time are more comfortable (when 09:00 and 21:00 hrs look alike, AM/PM is not informative). Wonder what they do?

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pat,
    How would you write a query, using your implementation, to find schedules for people that were available Tuesdays, Thursdays, and Saturdays?
    How about finding people that are ONLY available Wednesdays and Fridays?
    How about finding two people with identical schedules?
    How about finding people with complementary schedules?

    Bitmasks do these complicated tasks with relative ease. Hence, I consider them a more robust solution for this scenario.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Celko, the concept of storing dates as strings, and then perform string functions on them of all things, is anathema to me.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just want to throw this out there and see who reckons their solution still works...

    We're all assuming a 7 day working week pattern.
    How does your solution work with an N-day working pattern?

    E.g. I work Monday-Friday and every other Saturday

    George
    Home | Blog

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by gvee View Post
    Just want to throw this out there and see who reckons their solution still works...
    Since the OP's proposed solution won't address this problem I didn't see the need to address it either.

    If that were the case (part of the problem), then I'd cave in and use a solution more like Joe's. His is far more flexible than yours, blindman's or mine but by the same token Joe's also needs a lot more disk and introduces far more complexity in order to get that flexibility.

    As I noted above, I'd really love to see what the OP thinks of this brouhaha!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •