Post reply

Parameters

  • July 11, 2011 at 6:11 pm

    #243877

    Hi All,

    I have an issue regarding the parameters in one of my stored procedures.

    DECLARE @start_date DATETIME

    DECLARE @end_date DATETIME

    DECLARE @NumberOfWorkdays INT --Calculate the number of work days over the StartDate/EndDate period

    SET @start_date = CONVERT(VARCHAR,[MYDATABASE].dbo.[firstOfWeek]((GETDATE()),2), 101)

    SET @end_date = DATEADD(s, -1, CONVERT(VARCHAR, (@start_date+5), 101))

    SELECT @NumberOfWorkdays = [MYDATABASE].[dbo].[udf_BUSINESS_DAYS] (@start_date, @end_date)

    I have two UDF's

    [MYDATABASE].dbo.[firstOfWeek]: The report is scheduled to run on Saturdays, so this UDF gets the start date i.e Monday.

    and the Enddate is also calculate.

    [MYDATABASE].[dbo].[udf_BUSINESS_DAYS]: Calculates the number of business dates between the Startdate and Enddate (Federal holidays are ignored)

    and I am using this Start date and End date values in temp tables conditions and and I have some Avg calculations with @NumberOfWorkdays.

    My issue is Suppose July 4th is a Holiday. My start date should be 07/05/2011, rather than 07/04/2011. How can I do this for all the federal holidays.

    Thanks in advance.

  • July 11, 2011 at 6:21 pm

    #1351388

    That's not how I handle this.

    I have a calendar table. In that table I also include holidays.

    Then I add a calculated column

    CASE WHEN WeekDay BETWEEN 1 and 5 AND IsHoliday = 0 THEN 1 ELSE 0 END As IsBusDay

    Then I just scan that table between start and enddate and SUM(IsBusDay).

    Works like a charm and never had a problem with it.

  • July 11, 2011 at 8:26 pm

    #1351401

    Ninja is wise.

    With a calendar table you can also do things like add columns for each country you might be working with, so you can define national holidays not applicable in other countries. Or a column to flag company holidays. You can also flag dates as being weekend dates. All of which make it very easy to write flexible queries.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • July 12, 2011 at 4:42 pm

    #1351934

    +1 on the calendar table

    Here is a function that can help you generate one from scratch with lots of useful columns:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • July 12, 2011 at 7:22 pm

    #1351949

    Thanks all...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply