Grouping date periods (by number of days) but exclude the weekends?

Question

I have a table with start and end dates in. My goal is to have a table that has grouped these dates into how many days the period spans. I thought I had the solution with a simple SQL statement (MS SQL Server 2005) but I want to exclude weekends.

SELECT DATEDIFF(D, StartDate, EndDate)+1 AS Days,
 COUNT(ID) as Count 
FROM myDateTable
GROUP BY DATEDIFF(D, StartDate, EndDate)

This gives a record set of:

Days Count
1    4
2    2
4    1
7    2

Is this possible to exclude the weekends in the SQL statement and if not can it be done using ASP and a array perhaps?

Answer 1

Well then, using Sql Server 2005, you can try something like

DECLARE @Table TABLE(
        ID INT,
        StartDate DATETIME,
        EndDate DATETIME
)

INSERT INTO @Table (ID,StartDate,EndDate) SELECT 1, '25 Jan 2009', '31 Jan 2009'
INSERT INTO @Table (ID,StartDate,EndDate) SELECT 2, '01 Jan 2009', '07 Jan 2009'
INSERT INTO @Table (ID,StartDate,EndDate) SELECT 3, '01 Jan 2009', '14 Jan 2009'

DECLARE @MinDate    DATETIME,
        @MaxDate DATETIME

SELECT  @MinDate = MIN(StartDate) ,
        @MaxDate = MAX(EndDate) 
FROM    @Table
--Create a temp result set between the Min and Max dates, with all dates, and their weekday names
;WITH DayValues AS(
        SELECT  @MinDate DateVal,
                DATENAME(dw, @MinDate) DateValName
        UNION ALL
        SELECT  DateVal + 1,
                DATENAME(dw, DateVal + 1) DateValName
        FROM    DayValues
        WHERE   DateVal + 1 <= @MaxDate
),
--select the count of days for each StartDate and EndDate pair, excluding Saturdays and Sundays
DateCounts AS(
        SELECT  ID,
                (
                    SELECT  COUNT(1) 
                    FROM    DayValues 
                    WHERE   DateVal BETWEEN StartDate AND EndDate 
                    AND     DateValName NOT IN ('Saturday', 'Sunday')
                ) DateCount
        FROM    @Table
)
--Now group and count
SELECT  DateCount,
        COUNT(ID) TotalCount
FROM    DateCounts
GROUP BY DateCount
OPTION (MAXRECURSION 0)

Output

DateCount   TotalCount
----------- -----------
5           2
10          1

EDIT: Brief Explenation

You need to determine the number of days between (and including) 2 dates, that are not weekends.

So using a CTE, I create a temporary result set of dates ebwteen the Min and Max dates, and their Weekday Name (eg Monday, Tuesday... Sunday).

Then, for each of your date pairs, I count the number of entries that does not correspond to Saturday and Sunday.

Answer 2

Here's an ASP function that counts days weekdays between two dates.

<%
    Dim StartDate, EndDate

    StartDate = CDate("1/1/2010")
    EndDate = CDate("2/1/2010")

    Response.Write(WeekDayCount(StartDate, EndDate))


    Function WeekDayCount(StartDate, EndDate)
        dim tempDate, dayCount

        tempDate = StartDate
        dayCount = 0

        'Step forward one day, counting non-week days 
        While tempDate <> EndDate 
            'The 1 and 7 might need to be tweaked depending on the locale of your
            'server.  1 = Sunday, 7 = Saturday
            If DatePart("w", tempDate) <> 1 And DatePart("w", tempDate) <> 7 Then
                dayCount = dayCount + 1
            End If

            tempDate = DateAdd("d", 1, tempDate)
        Wend

          WeekDayCount = dayCount
    End Function
%>

Answer 3

Hi,

Have a look at @@DATEFIRST,

and look a this;

SELECT DATEPART(DW,GETDATE()).

You should be able to run a query WHERE the 'DW' is not equal to the weekend numbers.