Post reply

BusinessDaysToCalendarDays (For Unusual Business Weeks)

  • July 5, 2012 at 12:24 pm

    #263475

    Comments posted to this topic are about the item BusinessDaysToCalendarDays (For Unusual Business Weeks)

  • July 23, 2012 at 8:21 am

    #1516259

    Hi Mr. Celko, i really appreciate your time and enlightenment. The method you proposed is really good, but you missed the point of the problem I speak of. The idea is to know how many calendar days are needed to complete N bussines days starting at a specific day. For example, my package starts transit on friday, if it needs 7 bussines days to arrive it's destiny, how many calendar days it will took?. I'm sure it can be achivieded with thecalendar table, but in your example you gave a solution to a different problem.

  • July 23, 2012 at 8:32 am

    #1516270

    I will look deeply into the calendar table you told me ,and try to find a solution for the package transit problem.

    Thanks again.

  • July 24, 2012 at 4:12 pm

    #1517107

    So, i figured that if I base the calculation on the julian_business_nbr field of the calendar table, the desired value would be obtained with something like:

    SELECT CalendarDays = DATEDIFF( DAY, s.cal_date, MIN(e.cal_date) )

    FROM Calendar s

    JOIN Calendar e ON e.julian_business_nbr = s.julian_business_nbr + @BusinessDays

    WHERE s.cal_date = @StartDate

    GROUP BY s.cal_date

    Now, if there is a field called IsBussinesDay, the query goes like this:

    SELECT CalendarDays = DATEDIFF(DAY,MIN(c.cal_date),MAX(c.cal_date))

    FROM

    (

    SELECT TOP (@BussinesDays) cal_date

    FROM Calendar WHERE cal_date > = StartDate AND IsBussinesDay = 1

    ) c

  • July 24, 2012 at 4:31 pm

    #1517110

    Hi Mr. Celko,

    I would like to know your opinion on handling different bussines week configurations for services we use.

    The concrete example is Fedex services. The following options have different bussines days:

    - Fedex Home (Tuesday to Saturday)

    - Fedex Ground (Monday to Friday)

    - Fedex SameDay (All week)

    If I wanted to perform packing delivery calculations, would you recommend a Calendar table that contains the calendar days of each service or just a small BussinessWeek table that holds the days of the week?.

    I think that calendar table is nice, because it will help us work with the holidays.

    -- Option 1

    CREATE TABLE Calendar

    (

    CalendarConfiguration INT NOT NULL,

    CalendarDate DATE NOT NULL,

    JulianBusinessNumber INT NOT NULL,

    IsBussinesDay BIT NOT NULL,

    etc...

    )

    -- Option 2

    CREATE TABLE BussinesWeeks

    (

    WeekConfiguration INT NOT NULL,

    WeekDay INT NOT NULL CHECK( WeekDay BETWEEN 1 AND 7),

    BussinesDayNumber INT NOT NULL,

    IsBussinesDay BIT NOT NULL

    )

  • May 10, 2016 at 9:22 am

    #1877848

    Thanks for the script.

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

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