X working days before a specified date -- sql server programming

Steve Kass
2/13/2005 5:01:20 PM

Adrian,

I didn't test this, but my guess is that you can just
reverse the BETWEEN condition, so it becomes

BETWEEN c.CalendarDate AND CalendarDays.CalendarDate

But this is not a very efficient solution to the problem,
and you might consider this:

By adding an additional column OrdinalWorkingDate to the calendar
table, you can answer questions like this with two indexed lookups,
which is must faster than the query using COUNT(). For a given
CalendarDate, the OrdinalWorkingDate value in the same row
tells how many working days there are in the entire calendar before
that date. If there are 2198 working days before a given date, and
you want to move forward 10 working days, just find the row where
OrdinalWorkingDate is 2208 and both weekend and holiday are 0
(just so you don't get weekend days or holidays as additional answers).

Note that you'll have to update the OrdinalWorkingDate column if
you change any of the holiday or weekend values, or if you insert or
delete data from the calendar table. You can write a trigger to take
care of that if you need to.

-- add this column to the table:
OrdinalWorkingDate int not null default -1

-- after the holiday and weekend values are set,
-- update the OrdinalWorkingDate column:
update CalendarDays set
OrdinalWorkingDate = (
select count(*)
from CalendarDays as C2
where C2.CalendarDate <= CalendarDays.CalendarDate
and C2.holiday = 0 and C2.weekend = 0
)

-- index that column to cover queries with holiday and weekend
-- criteria also
create nonclustered index CalendarDays_od
on CalendarDays(OrdinalWorkingDate, holiday, weekend)

-- Here's your query. For the working day 10 working
-- days previous to ' 20050115' , use - 10 instead of + 10
select CalendarDate
from CalendarDays c
where OrdinalWorkingDate = (
select OrdinalWorkingDate
from CalendarDays
where CalendarDate = '20050115'
) + 10
and holiday = 0 and weekend = 0

-- Steve Kass
-- Drew University
-- Ref: B18217C3-11E0-46DE-B06D-5EE3DCB17327

[quoted text, click to view]

Adrian
2/13/2005 7:07:19 PM

I am trying to get a query to return the number of working days before a
specified date

I am creating a table of Calendar days as follows
====================================================
--This creates the necessary table of days in the calendar.
if exists (select * from INFORMATION_SCHEMA.TABLES where table_schema =
'dbo' and
table_name = 'CalendarDays' and table_type = 'BASE TABLE')
drop table CalendarDays

create table CalendarDays
(
CalendarDate datetime not null,
Weekend int not null,
Holiday int not null,
Descript varchar(30) null,
TwentyFiveWorkingDays datetime null,
constraint pk_CalendarDays primary key (CalendarDate)
)

-- fill the table up with starting values
declare @myday datetime
select @myday = '01/01/2004'

while @myday < '02/28/2007' -- 28 Feb 2007
begin

INSERT INTO CalendarDays(CalendarDate, Weekend, Holiday, Descript)
select @myday, case datepart(dw,@myday)
when 1 then 1
when 7 then 1
else 0 end,
0, @myday

select @myday = dateadd(dd,1,@myday)

end

--Enter some set holidays.
--Remainder need to enter manually
update CalendarDays
set holiday = 1, descript = 'Christmas'
where
datepart(m,CalendarDate) = 12 and
datepart(dd,CalendarDate) = 25

update CalendarDays
set holiday = 1, descript = 'Boxing Day'
where
datepart(m,CalendarDate) = 12 and
datepart(dd,CalendarDate) = 26

update CalendarDays
set holiday = 1, descript = 'New Year'
where
datepart(m,CalendarDate) = 1 and
datepart(dd,CalendarDate) = 1
===========================================

The following query will return the date 10 working days after the 15 Jan
2005, and I can easily change this to return the 15th (or whatever) working
day after the 15 Jan or any other date.

=======================================
Select CalendarDate
from CalendarDays c
where (SELECT COUNT(*)
FROM calendardays c2
WHERE c2.CalendarDate BETWEEN CalendarDays.CalendarDate AND c.CalendarDate
AND c2.weekend = 0 AND c2.holiday = 0)
= 11
AND c.weekend = 0 AND c.holiday = 0
) as WorkingDays
FROM CalendarDays
WHERE CalendarDate = '15 jan 2005'
========================================

What I am looking for is a similar query that would return so many working
dasy BEFORE the specified date, for example 10 working days before the 15
Jan.

Any help greatly appreciated.

Thanks,

Adrian.

Adrian
2/15/2005 5:01:44 PM

Thanks very much to Celko and Steve.

I implemented Steve's suggestion and it works very well.

Adrian.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : X working days before a specified date