Puzzle: dateadd workdays only

Hello Louis:
You wrote on Tue, 4 Jan 2005 15:11:01 -0600:
LD> First, why have a table named workDays that contains more than
LD> workdays?
In order to have it filled in not by myself but by HR person, using a nice
dialog with checkboxes.
...
LD> Third, this query seems to work, you can make it into a stored
LD> procedure :)
LD> declare @.f int
LD> declare @.D1 datetime
LD> set @.d1 = '2005-01-01'
LD> set @.f = 6
LD> select *
LD> from workdays
LD> where workday = 1
LD> and date > @.d1
LD> and (select count(*)
LD> from workdays as w
LD> where w.date <= workdays.date
LD> and date > @.d1
LD> and workday = 1) = @.f
the code is OK, but VERY slow. If you populate the table up to, say, 2050
and try it, you will see. Can you make a query that would deliver the result
in a second?
LD> Fourth, what is this for? Why specifically a one-query stored
LD> procedure?
As the subject says, it was a puzzle :-)
VadimOn Tue, 25 Jan 2005 11:38:53 -0600, Vadim Rapp wrote:
Hi Vadim,

> LD> Fourth, what is this for? Why specifically a one-query stored
> LD> procedure?
>As the subject says, it was a puzzle :-)
Ah - I *love* puzzles!

>the code is OK, but VERY slow. If you populate the table up to, say, 2050
>and try it, you will see. Can you make a query that would deliver the resul
t
>in a second?
Here's a query that is extremely fast if the number of workdays to add is
low. At my system, it can calculate 10 workdays ahead in less than 0.003
seconds, 100 workdays in 0.016 seconds, 1025 workdays in approx. 1 second
and 2000 workdays in under 4 seconds.
The time saving trick is to limit the search to dates in a qualifying
range: the date to be found will always be at least @.f days after the
starting date and never more than 1 and a half time @.f days after the
starting date (to which I add 3 extra days, to make sure I still get an
answer if the date submitted is a friday befor a wend that is
immediately followed by a holiday and @.f is 1). It should be possible to
reduce the range even further - e.g. by setting the lowest possible date
at start date + (1.4 * @.f) minus 1 or 2 to correct for low values of @.f,
but I don't want to do the maths required to get the "best" starting and
ending point.
The query uses a calendar table (http://www.aspfaq.com/show.asp?id=2519);
I did not test to see if a covering index would speed it up further.
select c1.dt
from dbo.Calendar AS c1
inner join dbo.Calendar AS c2
on c2.dt >= @.d1
and c2.dt < c1.dt
and c2.isWday = 1
and c2.isHoliday is null
where c1.isWday = 1
and c1.isHoliday is null
and c1.dt >= dateadd(day, @.f, @.d1)
and c1.dt <= dateadd(day, (@.f * 1.5) + 3, @.d1)
group by c1.dt
having count(*) = @.f
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 26 Jan 2005 00:25:52 +0100, Hugo Kornelis wrote:
(snip)
Woops - that was an incomplete copy/paste.
Here's the complete script:
declare @.f int
declare @.d1 smalldatetime
set @.d1 = '20050104'
set @.f = 2000
select c1.dt
from dbo.Calendar AS c1
inner join dbo.Calendar AS c2
on c2.dt >= @.d1
and c2.dt < c1.dt
and c2.isWday = 1
and c2.isHoliday is null
where c1.isWday = 1
and c1.isHoliday is null
and c1.dt >= dateadd(day, @.f, @.d1)
and c1.dt <= dateadd(day, (@.f * 1.5) + 3, @.d1)
group by c1.dt
having count(*) = @.f
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo:
You wrote in conference microsoft.public.sqlserver.programming on Wed, 26
Jan 2005 00:25:52 +0100:
HK> The time saving trick is to limit the search to dates in a qualifying
HK> range:
<snip>
HK> and c1.dt >= dateadd(day, @.f, @.d1)
HK> and c1.dt <= dateadd(day, (@.f * 1.5) + 3, @.d1)
HK> group by c1.dt
Yes, that's what I did as well. I think a sufficiently accurate range is thi
s:
if we have 5 workdays in a w
and we have N holidays in a year,
then it's safe to take the range as
between
@.date1 * 7.0/5.0 + (7-5) and
@.date1 * 7.0/5.0 + (7-5) + N * ( @.F / 365 + 1 )
HK> Ah - I *love* puzzles!
ok... then here's another one:
Find the date of the last Friday without using CASE (and in one query, of
course). If today is Friday, the result should be today's date.
regards,
Vadim Rapp
Vadim Rapp Consulting|||On Tue, 25 Jan 2005 22:30:08 -0600, Vadim Rapp wrote:
(snip)
> HK> Ah - I *love* puzzles!
>ok... then here's another one:
>Find the date of the last Friday without using CASE (and in one query, of
>course). If today is Friday, the result should be today's date.
Hi Vadim,
That's a question I've seen popping up in the newsgroups several times
lately. There are lots of possible solutions, but most of them depend on a
specific setting of SET DATEFIRST, or use a Calendar table.
Here's a solution that is independant of DATEFIRST and that won't need a
Calendar table:
SELECT DATEADD( day,
(DATEDIFF (day, '20000107', CURRENT_TIMESTAMP) / 7) * 7,
'20000107')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)