Re: grouping data on weekly basis

Tech-Archive recommends: Speed Up your PC by fixing your registry

JACKPOT


SQL Recipe: Federal government working days between two dates.

If you?d ever like to programmatically determine the number of federal
working days between two dates, here?s a quick and dirty solution I
cooked up the other day in Microsoft SQL Server. It can be useful in
monitering deadlines and evaluating bureaucratic processing speeds (say,
for example, a log of Freedom of Information Act requests).

It?s a three-step process.

1. Create a calendar table that distinguishes working days from
weekends and holidays.
2. Create a user-defined function that will use that calendar to
count the number of working days between two dates.
3. Count the days.

Below you can find a calendar creation script I adapted from one
published on aspfaq.com. Besides distinguishing weekdays from weekends,
it marks off all federal holidays ? as specificed by the U.S. Office of
Personnel Management ? from Jan. 1, 2000 to Dec. 31, 2010.

First we create the calendar table. It will have three fields, one for
the date and then two binary fields, one that will automatically
determine the weekdays using the DATEPART function, and another that we
will use later to designate working days.

CREATE TABLE dbo.FederalCalendar (
dt SMALLDATETIME PRIMARY KEY CLUSTERED,
isWeekDay AS CONVERT(BIT, CASE
WHEN DATEPART(dw, dt) IN (1,7) THEN 0
ELSE 1 END),
isWorkDay BIT DEFAULT 1
);
GO

Next we need to populate the date field, dt, with the range of days in
our calendar. In this case, it will be from Jan. 1, 2000 through Dec.
31, 2010.

DECLARE @dt SMALLDATETIME;
SET @dt = '20000101';
WHILE @dt<= '20101231'
BEGIN
INSERT dbo.FederalCalendar(dt) SELECT @dt;
SET @dt = @dt + 1;
END

Then winnow down our working days field by eliminating the weekends.

UPDATE dbo.FederalCalendar
SET isWorkDay = 0
WHERE isWeekday = 0;


--------------------------
My websites
http://www.eecpworld.com/
http://www.jungle-forex.com/


*** Sent via Developersdex http://www.developersdex.com ***
.