Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Understanding and Calculating Dates-3 : Page 3

Create a Table of Days in Your Calendar
First, create a table (see Listing 2) that holds the results of your calculations and actually helps solve your problem.

The weekend and holiday field contains 1 if the day is a weekend or holiday, respectively, and 0 otherwise. The nextday, twoday, and ups_ground columns store when a package is due to arrive, based on the shipment type.

Populate the Table with a Row for Each Day
Your next step is to insert a row for each day (calendar days, of course, not just business days) for a reasonable range of dates. In the code below, I've chosen to insert dates for four years—2001 through 2005. If you look closely, I've actually inserted ten rows for the year 2005. Why? As you will soon see, I actually use the rows in the table to find "the first day that is two or more days that is not a holiday or weekend." Because I want to find the arrival dates for "12/31/2004," I have to include in the table the rows for the beginning of 2005.

I use the CASE statement to set the weekend field correctly. Datepart(dw,@myday) returns the day of the week of the variable @myday. If it's either 7 or 1 (i.e., Saturday or Sunday) then the case statement evaluates to a 1, otherwise it inserts a 0 into that column (see Listing 3).

Set the Holidays Correctly
At this point, entering the holidays really means updating the correct rows by changing the holiday column to 1 for each day that's a holiday. In the code below I've updated the table for three sample holidays. As you can see, I don't have to write an update statement for every July 4th in my table. Instead, by using the DATEPART() function I can update every row where the month equals July and the day equals the fourth of the month (see Listing 4).

Calculate the Arrival Dates
Now that you have the shipments table properly set up, it's easy to calculate the arrival dates. Remember, the algorithm for two-day shipping: "Choose the first day that is two or more days greater than the shipping date that is not a weekend or holiday."

Using April 16, 2001 as the end date, Table 1 shows you how to write a query to calculate when it will arrive.

Now instead of calculating it for one day at a time, let's calculate it for all the rows in the table and store the results in the proper columns—i.e., "nextday," "twoday," etc. (as shown in Listing 5).

Once you've done all this, obtaining the arrival date for items shipped on say May 1st is as simple as this:

select nextday,twoday,ups_ground from shipments where shipdate = '05/01/2001' nextday twoday ups_ground ----------------------- ----------------------- ---------------------- 2001-05-02 00:00:00.000 2001-05-03 00:00:00.000 2001-05-07 0:00:00.000

Joe Lax has spent the last 10 years working in various database environments and has been a practicing DBA on all versions of SQL Server from version 4.2 onward. Joe is also a MCSE and an MCT. Recently, he has started to learn Oracle, which affords him no end of fun.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date