SQL Server Performance

Every Nth working day.

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

  1. MichaelB Member

    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. MichaelB Member

    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
  3. Adriaan New Member

    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. MichaelB Member

    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
  5. Adriaan New Member

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

    ... 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
  7. FrankKalis Moderator

  8. MichaelB Member

    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. MichaelB Member

    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
  10. Adriaan New Member

    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. MichaelB Member

    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. MichaelB Member

    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. FrankKalis Moderator

    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. MichaelB Member

    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. mmarovic Active 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. FrankKalis Moderator

    It's a FAQ for this site, not my own article. That's something completely different. [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<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. mmarovic Active 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=':p' />]<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. FrankKalis Moderator

    No! It's more like ++post [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<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>
  19. Madhivanan Moderator

    <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=':D' />]<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. FrankKalis Moderator

    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>
  21. Adriaan New Member

    While we're not on the subject ...

    ... and another post scored!
  22. FrankKalis Moderator

    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. mmarovic Active 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=':D' />]<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. MichaelB Member

    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

Share This Page