Logic to find no. of saturdays and sundays betwen two dates.

[SURA]

You are right Ray.

What the solution was found is temp fix.

[bagus]

Why cant you write a simple server routine for this and call in your job.

couldn't agree more

[FranklinE]

Another way to look at this is the need to count "business" days between dates. The problem, as Ray points to concerning holidays varying between cultures/countries, is how to adjust your calendar from one year to the next.

You have two requirements: count the number of non-weekend days between dates, and adjust for weekdays that should not be counted.

1) A calendar table with a flag for each day of the year: count-me, or don't-count-me.

2) A process that loads this table for each calendar year.

In the US, we have a varying number of holidays each year in the financial industry. We load our calendar every December for the coming year.

A very simplistic calculation would look like this. Instead of a count-me flag, I assign a value or not as follows:

Each day of the year has an ordinal (sequentially numeric) value if it is supposed to be counted. In your case the first step is check the ending date against the table. This gives you your ending ordinal value. Subtract the intransit amount, look up that ordinal on the table for its date.

Just to clarify:

Code: Select all

Mon   Tues  Wed   Thur  Fri   Sat   Sun   Mon   Tues   Wed ...
001   002   003   004   005   ---   ---   006   007    008 ...

If the 2nd Wednesday in my example is May 25, and your intransit value is 6, then the final lookup gives you Tuesday May 17.

Basically, instead of finding your weekend days every time you calculate, just set up to skip over them.

I leave it to the audience to figure out what to do when your start date is in December and your delivery date is in January.