Web dev redux.

So, this web dev project I’m working on is for reporting on document processing. I have to have a report that tells when certain documents are taking too long to process. The threshold is 45 days. The things is, the people who want this report want to be able to see the results in both calendar days, and business days. So I have to have a way to calculate both.

Here’s what I’ve come up with. So far. If I have a calendar that shows which days are business days and which days are not, then I can just pick the date range, and do a little simple math. As long as I can tell how many non-business days are in the date range, I can just subtract those from the date range and see how many business days it took to process the doco. Simple, right? But how to get that calendar…

Got my answer from devx.com. Joe Lax wrote an article and a script or two on working with dates in SQL. Now that I have a way to calculate the dates, I can finish up the project in a week or so, and get it all done. For your edification, here’s the script. When I get the rest of the SQL and PHP code together, I’ll post that too, for your reference.

Enjoy.
-D.

/* nbd_lookup table create script.
Flags days as holidays and weekend days so you can determine
whether a day is a business day or not. Useful in calculating
the number of business days an action took, given the date range.
Much thanks to Joe Lax of devx.com for the bulk of this script. */

-- Onward.

-- If the table already exists, and is owned by DBO, drop it.

if exists (select * from INFORMATION_SCHEMA.TABLES where table_schema = 'dbo' and
table_name = 'nbd_lookup' and table_type = 'BASE TABLE')
drop table nbd_lookup
go

-- Create the table.

create table nbd_lookup
(
caldate datetime not null,
weekend int not null,
holiday int not null,
descript varchar(50) null,
constraint pk_NBD primary key (caldate)
)
go

/* Add the calendar days to the table, flagging the weekend days.
This one is good for 2005. It can be a much larger range, though.
As large as the column type will support. */

declare @myday datetime
select @myday = '01/01/2005'

while @myday < '12/31/2005'
begin
INSERT INTO [nbd_lookup]([caldate], [weekend],
[holiday], [descript])
select @myday, case datepart(dw,@myday)
when 1 then 1
when 7 then 1
else 0 end, 0, NULL
select @myday = dateadd(dd,1,@myday)
end

/* Now flag the holidays specific to the calendar year for your company.
Edit this for the holiday schedule for that particular date range.
You may have to add a datepart() function for the year, if your range spans more than one year. i.e., "and datepart(yyyy,caldate) = 2005". */

update nbd_lookup
set holiday = 1, descript = 'New Years'
where
datepart(m,caldate) = 1 and
datepart(dd,caldate) = 1

update nbd_lookup
set holiday = 1, descript = 'MLK Day'
where
datepart(m,caldate) = 1 and
datepart(dd,caldate) = 17

update nbd_lookup
set holiday = 1, descript = 'Memorial Day'
where
datepart(m,caldate) = 5 and
datepart(dd,caldate) = 30

update nbd_lookup
set holiday = 1, descript = 'July 4th'
where
datepart(m,caldate) = 7 and
datepart(dd,caldate) = 4

update nbd_lookup
set holiday = 1, descript = 'Labor Day'
where
datepart(m,caldate) = 9 and
datepart(dd,caldate) = 5

update nbd_lookup
set holiday = 1, descript = 'Thanksgiving'
where
datepart(m,caldate) = 11 and
datepart(dd,caldate) = 24

update nbd_lookup
set holiday = 1, descript = 'Thanksgiving'
where
datepart(m,caldate) = 11 and
datepart(dd,caldate) = 25

update nbd_lookup
set holiday = 1, descript = 'Christmas'
where
datepart(m,caldate) = 12 and
datepart(dd,caldate) = 23

update nbd_lookup
set holiday = 1, descript = 'Christmas'
where
datepart(m,caldate) = 12 and
datepart(dd,caldate) = 26

/* Add as many holidays as you're aware of, for each calendar year you need. That's it. Use it in good health.
-D. */