Every Nth working day.

Discussion in 'General Developer Questions' started by MichaelB, Jun 27, 2006.

1. MichaelBMember

I am trying to write a function to get every weekday this month from a calendar table likehttp://www.aspfaq.com/show.asp?id=2519

I know I can do something like this:

SELECT TOP 1
@current= a.isodate
FROM
Calendar a
INNER JOIN
(
SELECT
[year],
[Month],
[week],
dayofmonth
FROM
Calendar
WHERE
dte = @eDate
) AS b ON
a.[year] = b.[year]
AND a.[month] = b.[month]
AND a.dayofmonth = @xth
ORDER BY
isodate desc

But I want it to be row driven so I can look for only working days (have a field for that) and count only those that are working days. I am unable to get this in my head at this time. I have been spening over a week writing a function that will do almost any possible date calulation. I will be happy to share with the group when I am done. It will have about 780 queries and runs quickly.

Michael B
Sr. DBA

"The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7
2. MichaelBMember

One note. I am looking for a set-based solution. I am sure it can be done that way.

Michael B
Sr. DBA

"The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

Not immediately set-based ...

DECLARE @Month TINYINT, @Year SMALLINT, @WeekDay SMALLINT
DECLARE @MaxDay TINYINT, @WorkingDate DATETIME
DECLARE @t TABLE (MyDate DATETIME)

SET @WeekDay = 3 -- whichever weekday you need
SET @Month = 4
SET @Year = 2004

SET @MaxDay = DATEPART(DAY, (CAST(CAST(@Year AS VARCHAR(4)) + '-' + CAST(@Month + 1 AS VARCHAR(2)) + '-1' AS DATETIME)) -1)

SET @WorkingDate = CAST(CAST(@Year AS VARCHAR(4)) + '-' + CAST(@Month AS VARCHAR(2)) + '-' + CAST(@Weekday AS CHAR(1)) AS DATETIME)
INSERT INTO @t VALUES (@WorkingDate)

WHILE DAY(@WorkingDate) + 7 <= @MaxDay
BEGIN
SET @WorkingDate = @WorkingDate + 7
INSERT INTO @t VALUES (@WorkingDate)
END

SELECT * FROM @t
4. MichaelBMember

I appreciate it. I was looking to use the calendar table since the whole function is to be baded on it and our working days are stored in it too (we are a financial co and trade days are important).

Michael B
Sr. DBA

"The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

Well, you could expand on the code to turn it into a table-returning UDF ...

... and also make a small change to avoid an error for December -

IF @Month < 12
SET @MaxDay = DATEPART(DAY, (CAST(CAST(@Year AS VARCHAR(4)) + '-' + CAST(@Month + 1 AS VARCHAR(2)) + '-1' AS DATETIME)) -1)
ELSE
SET @MaxDay = 31

8. MichaelBMember

There should be one date returned. If you pass a date like '12/15/2005' then the Nth working day for that month where the Nth = 15 would be 12/21/2005 which gives the 15th working day of that month.

Michael B
Sr. DBA

"The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7
9. MichaelBMember

This is close, but it doesnt give the right amount of weekends.

select

dte from
Calendar
where
[year] = year(@edate)
AND [month] = month(@edate)
and dayofmonth = (@xth) +
(
SELECT
count(*) as dayofmonth
FROM
Calendar
WHERE
[year] = year(@edate)
AND [month] = month(@edate)
AND dayofmonth <= @xth
AND (isHoliday = 1
ORisweekday = 0)

Any thoughts?
)

Michael B
Sr. DBA

"The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7

Okay - the Nth working day of a given month, as a UDF:

CREATE FUNCTION dbo.fnGetNthWorkingDay (@N TINYINT, @Month TINYINT, @Year SMALLINT)
RETURNS DATETIME
AS
BEGIN

DECLARE @TestDay SMALLINT, @MaxDay TINYINT, @WorkingDate DATETIME, @DayCount TINYINT

SET @TestDay = 0
SET @DayCount = 0

SET @MaxDay = DATEPART(DAY, (CAST(CAST(@Year AS VARCHAR(4)) + '-' + CAST(@Month + 1 AS VARCHAR(2)) + '-1' AS DATETIME)) -1)

SET @WorkingDate = CAST(CAST(@Year AS VARCHAR(4)) + '-' + CAST(@Month AS VARCHAR(2)) + '-1' AS DATETIME)

WHILE @TestDay + 1 <= @MaxDay
BEGIN
SET @TestDay = @TestDay + 1
IF DATEPART(WEEKDAY, @WorkingDate) NOT IN (1, 7)
SET @DayCount = @DayCount + 1
IF @DayCount = @N
BREAK
SET @WorkingDate = @WorkingDate + 1
END

RETURN (@WorkingDate)

END
GO
11. MichaelBMember

so how does this look at my data that describes working days? It is not M-F. I appreciate the effort.

Michael B
Sr. DBA

"The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7
12. MichaelBMember

I figured it out! Small round of applause<img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />I is so simple when it is setbased.<br /><br />declare @working table (eid int identity(1,1) NOT NULL, edate char(10))<br />insert into @working<br />SELECT <br /> dte<br />FROM <br /> Calendar w<br />WHERE <br /> [year] = year(@edate)<br />AND [quarter] = (select quarter from Calendar where dte = @edate)<br /> AND isHoliday = 0<br />and isweekday = 1 <br /><br />select @current = edate from @working where eid = @xth<br /><br />In this I also found that datepart(ww,'12/4/2006') is wrong. Date part doesnt work with weeks well so I had to go back to the Calendar table to get it proper.<br />Thanks to everyone who contributed!<br /><br />Michael B<br />Sr. DBA<br /><br />"The fear of the Lord is the beginning of knowledge,<br />but fools despise wisdom and instruction." Proverbs 1:7
13. FrankKalisModerator

Sorry for being late on this one. I see you've found a solution already. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br />Your problem is very similar to the one I've described briefly here:<a target="_blank" href=http://www.sql-server-performance.com/q&a124.asp>http://www.sql-server-performance.com/q&a124.asp</a><br /><br />Given the following DDL:<br /><pre id="code"><font face="courier" size="2" id="code"><br />IF OBJECT_ID('Calendar') &gt; 0<br /> DROP TABLE Calendar<br />GO<br />CREATE TABLE Calendar<br />(<br /> dt DATETIME<br /> , isweekday BIT<br />)<br /><br />INSERT INTO Calendar (dt, isweekday)<br /> SELECT DATEADD(DAY, Number, '20060601')<br /> , CASE WHEN DATEPART(dw, DATEADD(DAY, Number, '20060601')) IN (1, 7) THEN 0 ELSE 1 END<br /> FROM master..spt_Values<br /> WHERE Type='P'<br /> AND Number BETWEEN 0 AND 30<br /> ORDER BY Number<br /></font id="code"></pre id="code"><br /><br />Probably the easiest solution is to use TOP like this<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @dt DATETIME<br /><br />SELECT TOP 3 @dt = dt<br /> FROM Calendar<br /> WHERE isweekday != 0<br /> ORDER BY dt<br />SELECT @dt dt<br /><br />dt <br />------------------------------------------------------ <br />2006-06-05 00:00:00.000<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />However, when used on SQL Server 2000 and below, TOP doesn't accept a variable. So, this solution is rather static (unless you're going to use Dynamic SQL, of course). So, you can also put this in an SP and work with a temp table, just like you also did.<br /><pre id="code"><font face="courier" size="2" id="code"><br />CREATE PROC dbo.GetNthLatestEntry (@NthLatest INT)<br />AS <br />SET NOCOUNT ON<br />BEGIN<br /> CREATE TABLE #Entry <br /> (<br /> ID INT PRIMARY KEY NOT NULL IDENTITY(1,1)<br /> , Entry DATETIME NOT NULL<br /> )<br /> INSERT INTO #Entry (Entry) SELECT dt FROM Calendar WHERE isweekday != 0 ORDER BY dt<br /> SELECT Entry hire_date<br /> FROM #Entry <br /> WHERE ID = @NthLatest<br /> DROP TABLE #Entry<br />END<br />SET NOCOUNT OFF<br />GO<br />DECLARE @start DATETIME<br />SET @Start = '20060603'<br />DECLARE @DesiredDay INT<br />SET @DesiredDay = DATEPART(DAY, @Start)<br />EXEC dbo.GetNthLatestEntry @DesiredDay<br />DROP PROCEDURE dbo.GetNthLatestEntry<br /><br />hire_date <br />------------------------------------------------------ <br />2006-06-05 00:00:00.000<br /></font id="code"></pre id="code"><br />[<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
14. MichaelBMember

I ran into problems using the month and day of month fields due to how weeks are calulated using ISO standard. Now I have gone the matimatical way... just thought I would share if anyone searches for this<br /><br />set @current = NULL<br /><br />SELECT <br /> top 1<br /> @current =<br /> dbo.isodate((dte-dayofmonth) + @xth)<br />FROM <br /> EBSCalendar w<br /> <br />WHERE <br /> dte &gt;= @edate<br />AND isEBSHoliday = 0<br />AND isweekday = 1<br />order by <br /> dte asc<br /><br />I learned this from a programmer.. imagine... Sometimes you have to think not relational, but like a inline programmer <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Michael B<br />Sr. DBA<br /><br />"The fear of the Lord is the beginning of knowledge,<br />but fools despise wisdom and instruction." Proverbs 1:7
15. mmarovicActive Member

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Your problem is very similar to the one I've described briefly here:<a target="_blank" href=http://www.sql-server-performance.com/q&a124.asp>http://www.sql-server-performance.com/q&a124.asp</a><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />What a ... Never mind. [<img src='/community/emoticons/emotion-5.gif' alt='' />]
16. FrankKalisModerator

It's a FAQ for this site, not my own article. That's something completely different. [<img src='/community/emoticons/emotion-4.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
17. mmarovicActive Member

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br />That's something completely different. [<img src='/community/emoticons/emotion-4.gif' alt='' />]<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Sure [<img src='/community/emoticons/emotion-5.gif' alt='' />]<br /><br />post++
18. FrankKalisModerator

No! It's more like ++post [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />No! It's more like ++post [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />So you are strong in C, C++ also? [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
20. FrankKalisModerator

Not that much these days anymore. Though C++ is absolutely cool. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

While we're not on the subject ...

... and another post scored!
22. FrankKalisModerator

I like these easy-going Fridays. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>
23. mmarovicActive Member

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br />No! It's more like ++post [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />So you are strong in C, C++ also? [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />and ++C too [<img src='/community/emoticons/emotion-5.gif' alt='' />]<br /><br />post++
24. MichaelBMember

How about SQL???<img src='/community/emoticons/emotion-1.gif' alt='' /> <br />zzzzz <br /><br />lets make it a hot topic<img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />Michael B<br />Sr. DBA<br /><br />"The fear of the Lord is the beginning of knowledge,<br />but fools despise wisdom and instruction." Proverbs 1:7