Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2012
    Posts
    4

    Unanswered: Calculate Date Difference, Excluding Dates

    I have already seen stored procedures that can calculate a difference in dates, excluding the weekends. I am wondering if anyone has seen an extension of such a SQL query to exclude not only weekends, but other dates as well. We have a table of "holidays" (not necessarily standard holidays), and I am wondering if there is a way to exclude them from the calculation.

    Glenn Rosenthal

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about like this expression?

    Code:
    DATEDIFF(day , @startDate , @endDate)
    - (SELECT COUNT(holiday)
        FROM  holidays
        WHERE holiday BETWEEN @startDate
                          AND @endDate
      )

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...won't work if you are excluding holidays and weekends, and the holiday falls on a weekend.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I assumed that weekends also included in a table of "holidays".

  5. #5
    Join Date
    Aug 2012
    Posts
    4
    No, weekends would not be in the table for "holidays".

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here's a function you can use. You could easily replace the hard-code holiday list with a reference to a table of Holidays. Use the "IsWorkDay" field to determine whether you dress business casual or lounge around in your pajamas on any given date.

    Code:
    create function [dbo].[DateRange](@StartDate date, @EndDate date)
    returns table
    --Test parameters
    --declare	@StartDate date
    --declare	@EndDate date
    --set		@StartDate = GETDATE()
    --set		@EndDate = '2013-01-01'
    return
    with Holidays as
    		(
    		select '2011-01-03' as HolidayDate, 'New Years' as Holiday
    		union select '2011-05-30' as HolidayDate, 'Memorial Day' as Holiday
    		union select '2011-07-04' as HolidayDate, 'Independence Day' as Holiday
    		union select '2011-09-05' as HolidayDate, 'Labor Day' as Holiday
    		union select '2011-11-24' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2011-11-25' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2011-12-26' as HolidayDate, 'Christmas Day' as Holiday
    
    		union select '2012-01-02' as HolidayDate, 'New Years' as Holiday
    		union select '2012-05-28' as HolidayDate, 'Memorial Day' as Holiday
    		union select '2012-07-04' as HolidayDate, 'Independence Day' as Holiday
    		union select '2012-09-03' as HolidayDate, 'Labor Day' as Holiday
    		union select '2012-11-22' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2012-11-23' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2012-12-25' as HolidayDate, 'Christmas Day' as Holiday
    
    		union select '2013-01-01' as HolidayDate, 'New Years' as Holiday
    		union select '2013-05-27' as HolidayDate, 'Memorial Day' as Holiday
    		union select '2013-07-04' as HolidayDate, 'Independence Day' as Holiday
    		union select '2013-09-02' as HolidayDate, 'Labor Day' as Holiday
    		union select '2013-11-28' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2013-11-29' as HolidayDate, 'Thanksgiving' as Holiday
    		union select '2013-12-25' as HolidayDate, 'Christmas Day' as Holiday
    		),
    	DateList as
    		(
    		select	Number as DateNumber,
    				DATEADD(day, number, @StartDate) as DateValue
    		from	dbadb.dbo.SequentialNumbers
    		where	DATEADD(day, number, @StartDate) <= @EndDate
    		)
    select	DateList.DateNumber,
    		DateList.DateValue,
    		datepart(weekday, DateList.DateValue) DayOfWeek,
    		datepart(day, DateList.DateValue) DayOfMonth,
    		datepart(DAYOFYEAR, DateList.DateValue) DayOfYear,
    		case when datepart(weekday, DateList.DateValue) between 2 and 6 then 1 else 0 end as IsWeekDay,
    		case when (datepart(weekday, DateList.DateValue) between 2 and 6 and Holidays.HolidayDate is null) then 1 else 0 end as IsWorkDay,
    		case when Holidays.HolidayDate is null then 0 else 1 end as IsHoliday,
    		coalesce(Holidays.Holiday, '') as HolidayName
    from	DateList
    		left outer join Holidays on DateList.DateValue = Holidays.HolidayDate
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...oops. You'll need to create and populate this table first:
    Code:
    CREATE TABLE [dbo].[SequentialNumbers](
    	[Number] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[Number] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    ;
    with NumList (NumVal) as
    		(select 0 as NumVal
    		UNION ALL
    		select NumVal + 1
    		from NumList
    		where NumVal < 9)
    insert into SequentialNumbers (Number)
    select	Ones.NumVal + Tens.NumVal * 10 + Hundreds.NumVal * 100 + Thousands.NumVal * 1000
    from	NumList Ones,
    		NumList Tens,
    		NumList Hundreds,
    		NumList Thousands
    where not exists
    		(select	*
    		from	SequentialNumbers
    		where	Number = Ones.NumVal + Tens.NumVal * 10 + Hundreds.NumVal * 100 + Thousands.NumVal * 1000)
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I like to include weekends to holidays table.
    Because,
    (1) It may be not permanently guaranteed weekends being holidays.
    For example, some Japanese companies(e.g. Toyota) had been trying to 3 days holidays in a week
    or to work on week ends and to make holidays in weekdays
    for cost saving and/or averaging of use of electricity.
    (2) Logics of holidays are separated into two parts "holidays" table and query logic,
    if weekends were not included in holidays table.
    (This might be more serious in maintenance phase.)


    But, if you don't want to put weekends to "holidays" table,
    how about this expression?

    I'm sorry my example was wrong in /* number of weekends between @startDate and @endDate */
    So, please try blindman's example.
    Last edited by tonkuma; 08-28-12 at 02:15. Reason: Spelling(electricity). Removed wrong sample code.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •