This is (from my view) a complex SQL question, but it's also somewhat of a mathematics problem too.
Here's the Setup...
- Table 1 stores Projects
- Table 2 stores Project Tasks
Everything is pretty standard, except I have a certain field in the projects table that allows me to specific the frequency a task needs to be done for the project in days. So, if a certain project has this field set to 3, I want to do a task on that project every 3 days.
Here's my current SQL statement:
CAST(GetDate()-(MAX(task_completeddate)+proj_days) AS INT)
WHERE proj_ID = task_project
GROUP BY proj_ID, proj_title, proj_days
ORDER BY CAST(GetDate()-(MAX(task_completeddate)+proj_days) AS INT)
Here's what this gives me...
A List of Projects:
A list of projects ordered by the longest time (in days) OVERDUE I am to do a task. This is calculate by subtracting the sum of the date of the most recent task completed for that project and the project days, subtracted from the current date, converted to an integer.
The following Fields:
0 - Project ID
1 - Project Title
2 - Project Task Frequency Days
3 - The Date of the Most Recent Task Completed for That Project
4 - The Date a Task Was Due to Be Completed
5 - The Number of Days Past Due
This system works like a charm. However, I'm wanting a new component and it's way outside my capabilities. Here's the challenge:
Give me all the same information, but the last two columns would reflect the values based on not counting weekend days.
So, if a task was completed on Friday, 10/3 and it's days were set to 3, the next due date would normally be Monday, 10/6. But with the new values, it would show a task due Wednesday, 10/8 because it skips Saturday and Sunday.
Ideally I would like to solve this problem using SQL, but if no one can help me do that I will accept a vbscript that takes an array generated by these results and gives me what I'm looking for.