Need Help with Date Query.....Finding days in between.

[vballvol]

I am writing a resource utilization database. I need a query that will help
find the number of days for each project between specified dates.

I have a project table that contains the project id and name, resource table
that contains the resource id and name, and an assignment table that links
the project id, resource id, start date of assignment, and finish date of
assignment. I need a query that will calculate the amount of days allocated
per project for the specified timeframe.

User opens report, the system asks for a start date and finish date for
assignments.

User enters June 1 for start and June 15 for end.

System returns:

Total work days 11 (I have this part working)

Project 1 - (Started in May, ended June 7) - Resource Allocated 5 BD for this
period
Project 2 - (Started June 12, ends in July) - Resource Allocated 4 BD for
this period
etc.

Please help!!!!!!

 

[John Spencer]

It is hard to tell from your posting, but it looks as if you need to compare
the entered dates against the start and end dates for the project and then
use the correct dates in the same function your are using to calculate total
work days.

Since you didn't post any SQL statement or even the function call we have to
make assumptions on how you are doing this. One assumption is that you are
using a function to do the calculation.

You have the following four cases to deal with:
UserStart => ProjectStart and UserEnd<=ProjectEnd (Use UserStart and
UserEnd)
UserStart < ProjectStart and UserEnd <=ProjectEnd (Use ProjectStart and
UserEnd)
UserStart => ProjectStart and UserEnd >ProjectEnd (Use UserStart and
ProjectEnd)
UserStart < ProjectStart and UserEnd > ProjectEnd (Use ProjectStart and
ProjectEnd)

I would probably write a function and pass those four dates into the
function. Then have the new function call your existing function with the
correct pair of dates.

IF you are doing all this in the query, post the SQL of the query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message