Post reply

Number of Business Day in Current Quarter

  • November 12, 2012 at 7:59 am

    #266855

    So I'm creating a report that has a QTD column, but not your traditional QTD. The request I'm getting is to SUM the QTD total by the number of business days of the current quarter. This is where I'm having issues. Need a little assistance on creating this query. Any assistance will be appreciated.

    Here's an example.

    SELECT

    ProjectID,

    (SUM(ClosedTickets / COUNT(DISTINCT ReportDate) / "This is where the # of business days for the quarter goes" as QTDClosedTickets.

    From TableName

    GROUP BY ProjectID

    Thanks! Hope my explaination was clear.

  • November 12, 2012 at 8:20 am

    #1558196

    What I would do is build a calendar table and link to that based on what you class is a business day.

    http://qa.sqlservercentral.com/scripts/Date/68389/

    Will also come in handy for other calendar based queries.

  • November 12, 2012 at 8:23 am

    #1558198

    Darth do you have a Calendar table?

    for me, business days, well, depend on our specific business.

    for example, the quarter for today's date is from 10/1/2012 to 12/31/2012.

    there are 92 days in that stretch.

    there are 66 weekdays (M-F) in that stretch.

    but for my company, there are 61 work days, because of the following holidays, which occur on weekdays:

    TheDate HolidayName

    2012-10-08 00:00:00.000 Columbus Day

    2012-11-22 00:00:00.000 Thanksgiving Day

    2012-12-24 00:00:00.000 Christmas Eve

    2012-12-25 00:00:00.000 Christmas Day

    2012-12-31 00:00:00.000 New Years Eve,Kwanzaa(Sixth of 7 days)

    for me, it was easy to hit my calendar table like this:

    /*

    --Results

    61

    */

    select

    SUM(ISWORKDAY)

    from TallyCalendar

    where yearnumber = YEAR(getdate())

    and datepart(qq,thedate) =datepart(qq,getdate())

    From there, i could get the total and join it to any query i needed to.

    so would you want 66 days in your calculation, or 61, or something else? also, your company might have holidays off that are different from my own...you might work Veterans day, but be off on the company prez's birthday or something.

    I've thrown my own version of a Calendar table (TallyCalendar) out there on the forum quite a few times, it might help you out in the future.

    http://www.stormrage.com/SQLStuff/TallyCalendar_Complete_With_DST.txt

    so

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 12, 2012 at 9:26 am

    #1558209

    anthony.green (11/12/2012)

    What I would do is build a calendar table and link to that based on what you class is a business day.

    http://qa.sqlservercentral.com/scripts/Date/68389/

    Will also come in handy for other calendar based queries.

    Anthony, this calendar table worked great for me. I needed the quarter work days completed field to do the equation I need and worked fantasic.

    Thanks.

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

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