Find number of weekdays(Monday,Tuesday..) between two dates monthwise

  • Hi All,

    I need to find average week day count on a monthly basis. My table has the creation date as a field which logs when a query was fired.

    Eg. start date - 1/15/2008; end date - 2/12/2009

    I am able to calculate the actual count of queries fired on each Monday,Tuesday... for the above time range on a monthly basis.

    I am stuck at a point where I need to find the actual no. of Mondays, Tuesdays .. per month for the given time range.

    Any help is welcome.

    TIA,

    Nikhil

  • Can you post what you have? You may have to create a function depending on the query.

  • Database - > LogBase

    Database Columns - > CreationDate, Text

    My Query to to calculate the actual count of queries fired on each Monday,Tuesday... for the above time range on a monthly basis.

    SELECT DATENAME(month, CreationDate) as DayName,DATENAME(dw, CreationDate) as DayName ,count(*) as ActualCount

    FROM LogBase

    Where CreationDate between @fromDate and @toDate and DATENAME(dw, CreationDate)

    group by DATENAME(dw, CreationDate)

    So here I am able to get number of queries/transaction done on each Monday,Tuesday,.. monthwise.

    What I am stuck at is how to I calculate the actual no. of Mondays, Tuesdays .. per month for the given time range. (say range is from 3-june-2008 to 7-July-2008. So here no. of mondays in June would be 4 and no. of Mondays in July would be 1). I need this data so that I can calculate Average WeeKday Transactions on a monthly bias

  • --Create this function and call it from your query passing it

    --dbo.udf_NumXWeekDaysinMonth(CreationDate)

    CREATE Function dbo.udf_NumXWeekDaysinMonth(@Date datetime)

    RETURNS smallint

    AS

    BEGIN

    Declare @dte varchar(10)

    Declare @TestDate varchar(10)

    Declare @i smallint

    Declare @iNumDays smallint

    Set @dte = Convert(varchar(10),@Date,101)

    Set @i = 1

    Set @iNumDays = 0

    While @i < 32

    Begin

    Set @TestDate = cast(month(@dte) as varchar(2)) + '/' + cast(@i as varchar(2)) + '/' + Cast(Year(@dte) as varchar(4))

    --print @TestDate

    IF isdate(@TestDate) = 1

    BEGIN

    IF (DATENAME(dw, @TestDate) = DATENAME(dw, @dte))

    BEGIN

    Set @iNumDays = @iNumDays + 1

    END

    END

    Set @i = @i+1

    End

    Return @iNumDays

    END

  • The formula in the following article will calculate weekdays without a loop. There is, however, no consideration for holidays...

    http://www.sqlservercentral.com/articles/Advanced+Querying/calculatingworkdays/1660/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • For accurate counts of weekdays, I HIGHLY recommend a calendar table. That way, you can include holidays, etc.

    create table Calendar (

    Date datetime primary key,

    constraint CK_Date_NoTime

    check (Date = cast(cast(cast(date as float) as int) as datetime)),

    Workday bit not null,

    Year as datepart(year, date),

    Month as datepart(month, date),

    Day as datepart(day, date),

    WeekDay as datepart(weekday, date))

    go

    insert into dbo.Calendar (Date, Workday)

    select dateadd(day, number, '1/1/2000'),

    case

    when datepart(weekday, dateadd(day, number, '1/1/2000')) between 2 and 6 then 1

    else 0

    end

    from dbo.numbers -- A table of 10-thousand numbers

    go

    create index IDX_Calendar_MonthDay on dbo.Calendar(month, day)

    go

    update dbo.Calendar

    set workday = 0

    where month = 7 and day = 4

    or ... -- fill in other holidays here

    go

    select count(*)

    from dbo.Calendar

    where workday = 1

    and date between '5/20/2008' and '6/9/2008'

    With a table like that, you can easily have SQL do things like calculate the 1st Monday in September, or the 4th Thursday in November, record these things as holidays, and calculate workdays between dates.

    ;with Thanksgiving (Row, Date, Workday) as

    (select row_number() over (order by date), date, workday

    from dbo.Calendar

    where Month = 11

    and WeekDay = 5)

    update Thanksgiving

    set workday = 0

    where row = 4

    You can also add more indexes to it, if you so desire.

    Using a 10-thousand day calendar starting from 1 Jan 2000, will give you a table that goes into 2027, which should be enough to start with. As needed, add more, or create a job that runs on the first day of each year and adds another year worth of days (if you do that, make sure it adds enough to account for leap years).

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • GSquared (6/9/2008)


    For accurate counts of weekdays, I HIGHLY recommend a calendar table. That way, you can include holidays, etc.

    create table Calendar (

    Date datetime primary key,

    constraint CK_Date_NoTime

    check (Date = cast(cast(cast(date as float) as int) as datetime)),

    Workday bit not null,

    Year as datepart(year, date),

    Month as datepart(month, date),

    Day as datepart(day, date),

    WeekDay as datepart(weekday, date))

    go

    insert into dbo.Calendar (Date, Workday)

    select dateadd(day, number, '1/1/2000'),

    case

    when datepart(weekday, dateadd(day, number, '1/1/2000')) between 2 and 6 then 1

    else 0

    end

    from dbo.numbers -- A table of 10-thousand numbers

    go

    create index IDX_Calendar_MonthDay on dbo.Calendar(month, day)

    go

    update dbo.Calendar

    set workday = 0

    where month = 7 and day = 4

    or ... -- fill in other holidays here

    go

    select count(*)

    from dbo.Calendar

    where workday = 1

    and date between '5/20/2008' and '6/9/2008'

    With a table like that, you can easily have SQL do things like calculate the 1st Monday in September, or the 4th Thursday in November, record these things as holidays, and calculate workdays between dates.

    ;with Thanksgiving (Row, Date, Workday) as

    (select row_number() over (order by date), date, workday

    from dbo.Calendar

    where Month = 11

    and WeekDay = 5)

    update Thanksgiving

    set workday = 0

    where row = 4

    You can also add more indexes to it, if you so desire.

    Using a 10-thousand day calendar starting from 1 Jan 2000, will give you a table that goes into 2027, which should be enough to start with. As needed, add more, or create a job that runs on the first day of each year and adds another year worth of days (if you do that, make sure it adds enough to account for leap years).

    Using the code above I came up with the code below for adding company holidays:

    How do I do it without using a cursor?

    --http://www.sqlservercentral.com/Forums/Topic513315-149-1.aspx#bm513318

    IF OBJECT_ID('Calendar', 'U') IS NOT NULL

    DROP TABLE Calendar

    create table Calendar (

    Date datetime primary key,

    constraint CK_Date_NoTime

    check (Date = cast(cast(cast(date as float) as int) as datetime)),

    Workday bit not null,

    Year as datepart(year, date),

    Month as datepart(month, date),

    Day as datepart(day, date),

    WeekDay as datepart(weekday, date),

    LongDay as datename(weekday, date),

    DayType varchar(50))

    go

    --create table Numbers (

    --Num_ID int)

    DECLARE @Numbers TABLE

    (

    Num_ID INT

    )

    declare @number as int

    set @number =1

    while @number <10001

    begin

    insert into @Numbers (num_id) values (@number)

    set @number=@number+1

    end

    insert into dbo.Calendar (Date, Workday,DayType)

    select dateadd(day, num_id, '12/31/1999'),

    case

    when datepart(weekday, dateadd(day, num_id, '12/31/1999')) between 2 and 6 then 1

    else 0

    end,

    case

    when datepart(weekday, dateadd(day, num_id, '12/31/1999')) between 2 and 6 then 'Work'

    else 'Weekend'

    end

    from @Numbers -- A table of 10-thousand numbers

    go

    create index IDX_Calendar_MonthDay on dbo.Calendar(month, day)

    go

    update dbo.Calendar

    set workday = 0, DayType='New Years Day'

    where month = 1 and day = 1

    go

    update dbo.Calendar

    set workday = 0, DayType='Independance Day'

    where month = 7 and day = 4

    go

    update dbo.Calendar

    set workday = 0, DayType='Christmas Day'

    where month = 12 and day = 25

    go

    DECLARE @CalendarYear TABLE

    (

    [Year] INT

    )

    DECLARE @year int

    INSERT INTO @CalendarYear

    select distinct year from calendar order by year

    DECLARE cYear CURSOR

    FOR SELECT year FROM @CalendarYear

    OPEN cYear

    fetch cYear into @year

    while @@fetch_status=0

    begin

    update dbo.Calendar set workday = 0, DayType='Memorial Day' where date=(select max(date) from calendar where month =5 and year=@year and weekday=2)

    update dbo.Calendar set workday = 0, DayType='Labor Day' where date=(select min(date) from calendar where month =9 and year=@year and weekday=2)

    update dbo.Calendar set workday = 0, DayType='Thanksgiving Day' where date=(select max(date)-1 from calendar where month =11 and year=@year and weekday=6)

    update dbo.Calendar set workday = 0, DayType='Day after Thanksgiving Day' where date=(select max(date) from calendar where month =11 and year=@year and weekday=6)

    fetch cYear into @year

    end

    close cYear

    /*

    1st JanNew Year's Day

    Last Monday in MayMemorial Day

    4th JulIndependence Day

    First Monday in SeptemberLabor Day

    Fourth Thursday in NovemberThanksgiving Day

    25th DecChristmas Day

    */

  • I would just wrap your code in () and use it as a type of derived table and average the counts like this...

    select MonthName, Dayname, Average(actualCount)

    from

    (

    SELECT CreationDate,DATENAME(month, CreationDate) as MonthName,DATENAME(dw, CreationDate) as DayName ,count(*) as ActualCount

    FROM LogBase

    Where CreationDate between @fromDate and @toDate

    group by CreationDate, DATENAME(month, CreationDate),DATENAME(dw, CreationDate)

    ) as a

    group by MonthName, Dayname

  • Stephen Yale (6/18/2008)


    .....

    How do I do it without using a cursor?

    .....

    Here's one way:

    CREATE FUNCTION [dbo].[IF_Calendar]

    (

    @StartDate DATE,

    @EndDate DATE,

    @FirstWeekDay VARCHAR(10)

    )

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN

    (

    -- inline tally table

    WITH E1(N) AS (

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

    ), --10E+1 or 10 rows

    E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b), --10E+2 or 100 rows

    E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b), --10E+4 or 10,000 rows max

    iTally AS ( -- generate sufficient rows to cover startdate to enddate inclusive

    SELECT TOP(1+DATEDIFF(DAY,@StartDate,@EndDate))

    rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1

    FROM E3

    )

    -- Do some date arithmetic

    SELECT

    a.DateRange,

    c.[Year],

    c.[Month],

    c.[DayOfMonth],

    c.AbsWeekno,

    c.[DayName],

    d.Holiday

    FROM iTally

    CROSS APPLY (SELECT DateRange = DATEADD(day,rn,@StartDate)) a

    CROSS APPLY (VALUES ('Tuesday',1),('Wednesday',2),('Thursday',3),('Friday',4),('Saturday',5),('Sunday',6),('Monday',7)

    ) b (FirstWeekDay, FirstWeekdayOffset)

    CROSS APPLY (

    SELECT

    [Year] = YEAR(a.DateRange),

    [Month] = MONTH(a.DateRange),

    [DayOfMonth] = DAY(a.DateRange),

    AbsWeekno= DATEDIFF(day,FirstWeekdayOffset,a.DateRange)/7,

    [DayName]= DATENAME(weekday,a.DateRange)

    ) c

    CROSS APPLY (

    SELECT Holiday = CASE

    WHEN [Month] = 1 AND [DayOfMonth] = 1 THEN 'New Year'

    WHEN [Month] = 5 AND [DayOfMonth] >= 25 AND [DayName] = 'Monday' THEN 'Memorial Day'

    WHEN [Month] = 7 AND [DayOfMonth] = 4 THEN 'Independence Day'

    WHEN [Month] = 9 AND [DayOfMonth] <= 7 AND [DayName] = 'Monday' THEN 'Labor Day'

    WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' THEN 'Thanksgiving Day'

    WHEN [Month] = 12 AND [DayOfMonth] = 25 THEN 'Christmas Day'

    ELSE NULL END

    ) d

    WHERE b.FirstWeekDay = @FirstWeekDay

    )

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 9 posts - 1 through 8 (of 8 total)

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