Number of Dates in a Date Range by Quarter

Question

Post reply

Number of Dates in a Date Range by Quarter

terickso

SSC Rookie

Points: 42
More actions
November 18, 2008 at 12:40 pm

#202958

Hi-
I have an asp.net page in C# where a user enters a start date and end date for a project. I need to determine the number of working days per quarter for this project with Q1 beginning Sept 1. For example, if the start date of a project is 09/10/2008 and the End Date is 11/10/08 then the only quarter affected is Q1 and the number of work days is 44 . However, if the End date is 12/10/08 then Q1 has 56 work days and Q2 has 8 work days.
I also need to run this query every time a user enters a new project on the website and have the dates populate a datagrid. Each project is assigned a projectID as an identity.
I have set up a calendar table that includes the holidays and weekend days.

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

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

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 1

Hi.

There are a few scripts on the net that could serve as a good basis for what you need. Eg

http://stackoverflow.com/questions/252519?sort=votes

You could then write another function that calls this one but adjusts the dates for each quarter.

However, if you want to take into account holidays then you will need something a bit more solid.... Create a table that contains all the work days for the year and then count the number of records between your dates. You could add another column that contains "Quarter" and then GROUP by that column.

This solution would provide full accuracy and control.... But would require some maintenance (you would need to make sure all the dates you need are in the table, possibly insert more each year...)

B

terickso SSC Rookie Points: 42 More actions · Answer 2

However, if you want to take into account holidays then you will need something a bit more solid.... Create a table that contains all the work days for the year and then count the number of records between your dates. You could add another column that contains "Quarter" and then GROUP by that column.

I have a table (workcalendar) that already includes all the work days for the year and for the fiscal year and assigns each to a quarter. How would I perform this count?

Thanks

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 3

Can you tell me the structure of the table (table name, column names) and I can show you the query...

terickso SSC Rookie Points: 42 More actions · Answer 4

Sure!

Calendar Table Name: WorkCalendar

Columns: dt, isWeekDay, isHoliday, Y, FY, Q

Table name that I am getting start and end dates for each project: Model

Columns: StartDate, EndDate, Q1Days, Q2Days, Q3Days, Q4Days

So I need to read in the StartDate and EndDate from Model and count the number of work days per quarter from the WorkCalendar Table. I then want to store these values in the corresponding columns in Model Table (total Q1 work days stored in Q1Days, etc)

Thanks!

Bevan Keighley SSCommitted Points: 1825 More actions · Answer 5

Something a bit like this....

select model.startdate, model.enddate, WorkCalendar.q, count(WorkCalendar.dt)

from model inner join WorkCalendar on model.startdate = WorkCalendar.dt

where WorkCalendar.isWeekDay=1 and WorkCalendar.isHoliday=0

group by model.startdate, model.enddate, WorkCalendar.q