Solved

Getting a Date and Skipping Weekends

Posted on 2006-11-03
4
1,262 Views
Last Modified: 2012-06-22
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:

SELECT
proj_ID,
proj_title,
proj_days,
MAX(task_completeddate),
MAX(task_completeddate)+proj_days
CAST(GetDate()-(MAX(task_completeddate)+proj_days) AS INT)
FROM
tbl_projects,
tbl_tasks
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.
0
Comment
Question by:cnxmax
  • 3
4 Comments
 
LVL 10

Expert Comment

by:AaronAbend
ID: 17872805
this case statement takes your task_completed date and converts saturday and dates to the following monday
case datepart(dw,task_completeddate) when 1 then dateadd(d,1,task_completeddate) when 7 then dateadd(d,2,task_completeddate)  else task_completeddate end

From your question I was not sure exactly what you want...but this should get you started. Let me know if you have questions.

SELECT
proj_ID,
proj_title,
proj_days,
MAX(task_completeddate),
MAX(case datepart(dw,task_completeddate) when 1 then dateadd(d,1,task_completeddate) when 7 then dateadd(d,2,task_completeddate)  else task_completeddate end  )+proj_days
CAST(GetDate()-(MAX(task_completeddate)+proj_days) AS INT)
FROM
tbl_projects,
tbl_tasks
0
 

Author Comment

by:cnxmax
ID: 17886663
AaronAbend:

Thanks for the tip. The Case/When SQL code is interesting, but I don't know if that will be the solution. The main reason is that I need to be able to bump things up by a day for each weekend day between Today and the Date of the Last Task Completion. This could be multiple weeks with multiple weekend days.

I don't just want everything that should have been done Saturday to be bumped to Monday. Everything should be moved up two days for every weekend.

I found a way to do this with a script that basically loops through all of the results, looping through each day between the last completed date and today, and bumping the due date up by one day for each weekend day it finds.

Anyway, this may be beyond what I can do in a SQL statement...
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 17886785
I don't think it is beyond SQL at all.  You can compute the number of weekends by dividing the number of days between getdate() (current datetime) and LastTaskCompleted by 7 and checking the remainder against the day of the week (returned by datepart).  A loop can certainly do it though I was trying to avoid it for performance reasons (big hit in most cases).

Want me to post logic or are you all set?
0
 
LVL 10

Accepted Solution

by:
AaronAbend earned 500 total points
ID: 17886791
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_22049471.html

check out the responses I just got today on this question I asked regarding date logic
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Find unused columns in a table 12 66
T-SQL Import data in Microsoft SQL Server Management Studio using INSERT 3 22
SQL Server Resume 5 44
SQL 2014 missing dll from Bin? 3 31
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question