• 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.

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

Thanks again.

• 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`

• 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,

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

)

• Thanks for the script.

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