DateDiff Based on Group Based on Conditional Values

[mavsman]

I am needing to find the # of days between dates (first and last) based on a group ID and one two other columns. Here is an example:

I have Sales Agents that i would like to group on. In the database their ID has a start date. They also have dates associated with that start date when any activity they do happens.

agent start date event date event
001 2011-01-01 2011-01-01 start
001 2011-01-01 2011-01-03 meeting
001 2011-01-01 2011-01-05 training
001 2011-01-01 2011-01-09 end
001 2011-01-11 2011-01-11 start
001 2011-01-11 2011-01-12 meeting
001 2011-01-11 2011-01-19 end

I would like to group on the Agent then calculate the number of days between starting and ending event not counting start event as a day and linked with the start date as the key and not counting weekends/holidays. Also, the search date will be on the event date. So someone will enter the event date and it will give them the agent and number of days based on starting event date:


agent 001, event date 2011-01-01, days = 5
agent 001, event date 2011-01-11, days = 5


Any help would be greatly appreciated!

 

[MCuthill]

mavsman,

I beleive the following link will be a good place to start for how to count weekdays in a given range.

[URL unfurl="true"]http://www.kenhamady.com/form01.html[/url]

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."