parallon
09-06-2006, 06:20 PM
Hello all. I know that this is mostly VB Script within my question, but I'm hoping someone out there could help me out there. I am writing a script to change project Start and End dates. So, basically, if a user inputs a newer start date, then DateDiff is a positive number, and if the user enters an earlier date, then DateDiff is a negative number. So, all I do is have the newDate = OldDate + DateDiff. And keep looping this until the date is not on a weekend. If it does fall on a weekend, then depending on the direction of the change, I either add 1 or 2 days or subtract 1 or 2 days.
I also need to check to see whether the new date falls on a Holiday (which is defined in the DB) and do the same thing. Well, I pretty much got it working, and then I realized that depending on the sort order of the Holiday recordset, either going ahead in time works, but going back in time doesn't, or vise versa. See, what's happening is this:
Current Dates 9/4/2006 - 9/5/2006
Proposed Dates 9/3/2006 - 9/4/2006
Holidays 8/28/2006 - 9/1/2006
Well, 9/3/2006 is a Sunday, so it should subtract 2 days to put it to Friday 9/1/2006, well this is a Holiday, so it should keep subtracting a day until it is neither a weekend nor a Holiday, so it should continue until 8/25/2006. Well, if the Holiday recordset is sorted Descending, then 9/1/2006 will be the first date it sees, and will continue till the end, but if the recordset is sorted Ascending, then 8/28/2006 will be the first date it sees and continue until it sees 9/1/2006, which will then be the end of the recordset, so when all is said and done, it will only take one Holiday into consideration.
Does anyone have any suggestions? I guess I should run a different query based on the direction of the date changes, but the thing is that the new start date could be before the old one, and the new end date could be after the old one, which means I would have a lot of scenarios.
Here is the code: (NOTE - A lot of lines are commented for testing purposes)
Dim pStartDate, pEndDate, cStartDate, cEndDate, nStartDate, nEndDate, sChgDirection, eChgDirection
Dim StartDiff, EndDiff
'pStartDate = ""
'pEndDate = ""
'cStartDate = ""
'cEndDate = ""
'pStartDate = Request.Form("txtpStartDate") ' Proposed Start Date
pStartDate = "9/7/2006"
'pEndDate = Request.Form("txtpEndDate") ' Proposed End Date
pEndDate = "9/7/2006"
'cStartDate = Request.Form("txtcStartDate") ' Current Start Date
cStartDate = "9/8/2006"
'cEndDate = Request.Form("txtcEndDate") ' Current End Date
cEndDate = "9/9/2006"
StartDiff = DateDiff("d",cStartDate,pStartDate) ' Current Date - Proposed Date
EndDiff = DateDiff("d",cEndDate,pEndDate) ' Current Date - Proposed Date
nStartDate = DateAdd("d",StartDiff,cStartDate) 'New Start Date
nEndDate = DateAdd("d",EndDiff,cEndDate) ' New End Date
If StartDiff > 0 then sChgDirection = "Forward"
If StartDiff < 0 then sChgDirection = "Backward"
If EndDiff > 0 then eChgDirection = "Forward"
If EndDiff < 0 then eChgDirection = "Backward"
%>
<%
Dim rsHoliday
Dim rsHoliday_numRows
Set rsHoliday = Server.CreateObject("ADODB.Recordset")
rsHoliday.ActiveConnection = MM_Tero_Roports_STRING
rsHoliday.Source = "SELECT * FROM Holidays WHERE Empid = '_GLOBAL' Order By Holiday DESC"
rsHoliday.CursorType = 0
rsHoliday.CursorLocation = 2
rsHoliday.LockType = 1
rsHoliday.Open()
rsHoliday_numRows = 0
Do Until rsHoliday.EOF
If nStartDate = rsHoliday.Fields.Item("Holiday").Value then
If sChgDirection = "Forward" then
nStartDate = nStartDate + 1
Else If sChgDirection = "Backward" then
nStartDate = nStartDate - 1
End If
End If
End If
If nEndDate = rsHoliday.Fields.Item("Holiday").Value then
If eChgDirection = "Forward" then
nEndDate = nEndDate + 1
Else If eChgDirection = "Backward" then
nEndDate = nEndDate - 1
End If
End If
End If
rsHoliday.MoveNext
Loop
%>
<%
'If New Dates are GREATER than Old Date and the new dates fall on a weekend, then move new dates to next available date (foreward).
' Otherwise
'If New Dates are LESS than Old Date and the new dates fall on a weekend, then move new dates to previous available date (backward).
If WeekDay(nStartDate) = 1 then 'If Sunday
If StartDiff < 0 Then 'Is New Start Date BEFORE the old Start date?
nStartDate = nStartDate - 2 ' If nStartDate is sooner, then subtract 2
Else if StartDiff > 0 then 'Is New Start Date AFTER the old Start date?
nStartDate = nStartDate + 1 ' If nStartDate is later, then add 1
End If
End If
Else If WeekDay(nStartDate) = 7 then 'If Saturday
If StartDiff < 0 Then 'Is New Start Date BEFORE the old Start date?
nStartDate = nStartDate - 1 ' If nStartDate is sooner, then subtract 2
Else if StartDiff > 0 then 'Is New Start Date AFTER the old Start date?
nStartDate = nStartDate + 2 ' If nStartDate is later, then add 2
End If
End If
End If
End If
If WeekDay(nEndDate) = 1 then 'If Sunday
If EndDiff < 0 Then 'Is New End Date BEFORE the old End date?
nEndDate = nEndDate - 2 ' If nEndDate is BEFORE, then subtract 2
Else if EndDiff > 0 then 'Is New End Date AFTER the old End date?
nEndDate = nEndDate + 1 ' If nEndDate is AFTER, then add 1
End If
End If
Else If WeekDay(nEndDate) = 7 then 'If Saturday
If EndDiff < 0 Then 'Is New End Date BEFORE the old End date?
nEndDate = nEndDate - 1 ' If nEndDate is BEFORE, then subtract 1
Else if EndDiff > 0 then 'Is New End Date AFTER the old End date?
nEndDate = nEndDate + 2 ' If nEndDate is AFTER, then add 2
End If
End If
End If
End If
%>
Thank you for your time,
Parallon
I also need to check to see whether the new date falls on a Holiday (which is defined in the DB) and do the same thing. Well, I pretty much got it working, and then I realized that depending on the sort order of the Holiday recordset, either going ahead in time works, but going back in time doesn't, or vise versa. See, what's happening is this:
Current Dates 9/4/2006 - 9/5/2006
Proposed Dates 9/3/2006 - 9/4/2006
Holidays 8/28/2006 - 9/1/2006
Well, 9/3/2006 is a Sunday, so it should subtract 2 days to put it to Friday 9/1/2006, well this is a Holiday, so it should keep subtracting a day until it is neither a weekend nor a Holiday, so it should continue until 8/25/2006. Well, if the Holiday recordset is sorted Descending, then 9/1/2006 will be the first date it sees, and will continue till the end, but if the recordset is sorted Ascending, then 8/28/2006 will be the first date it sees and continue until it sees 9/1/2006, which will then be the end of the recordset, so when all is said and done, it will only take one Holiday into consideration.
Does anyone have any suggestions? I guess I should run a different query based on the direction of the date changes, but the thing is that the new start date could be before the old one, and the new end date could be after the old one, which means I would have a lot of scenarios.
Here is the code: (NOTE - A lot of lines are commented for testing purposes)
Dim pStartDate, pEndDate, cStartDate, cEndDate, nStartDate, nEndDate, sChgDirection, eChgDirection
Dim StartDiff, EndDiff
'pStartDate = ""
'pEndDate = ""
'cStartDate = ""
'cEndDate = ""
'pStartDate = Request.Form("txtpStartDate") ' Proposed Start Date
pStartDate = "9/7/2006"
'pEndDate = Request.Form("txtpEndDate") ' Proposed End Date
pEndDate = "9/7/2006"
'cStartDate = Request.Form("txtcStartDate") ' Current Start Date
cStartDate = "9/8/2006"
'cEndDate = Request.Form("txtcEndDate") ' Current End Date
cEndDate = "9/9/2006"
StartDiff = DateDiff("d",cStartDate,pStartDate) ' Current Date - Proposed Date
EndDiff = DateDiff("d",cEndDate,pEndDate) ' Current Date - Proposed Date
nStartDate = DateAdd("d",StartDiff,cStartDate) 'New Start Date
nEndDate = DateAdd("d",EndDiff,cEndDate) ' New End Date
If StartDiff > 0 then sChgDirection = "Forward"
If StartDiff < 0 then sChgDirection = "Backward"
If EndDiff > 0 then eChgDirection = "Forward"
If EndDiff < 0 then eChgDirection = "Backward"
%>
<%
Dim rsHoliday
Dim rsHoliday_numRows
Set rsHoliday = Server.CreateObject("ADODB.Recordset")
rsHoliday.ActiveConnection = MM_Tero_Roports_STRING
rsHoliday.Source = "SELECT * FROM Holidays WHERE Empid = '_GLOBAL' Order By Holiday DESC"
rsHoliday.CursorType = 0
rsHoliday.CursorLocation = 2
rsHoliday.LockType = 1
rsHoliday.Open()
rsHoliday_numRows = 0
Do Until rsHoliday.EOF
If nStartDate = rsHoliday.Fields.Item("Holiday").Value then
If sChgDirection = "Forward" then
nStartDate = nStartDate + 1
Else If sChgDirection = "Backward" then
nStartDate = nStartDate - 1
End If
End If
End If
If nEndDate = rsHoliday.Fields.Item("Holiday").Value then
If eChgDirection = "Forward" then
nEndDate = nEndDate + 1
Else If eChgDirection = "Backward" then
nEndDate = nEndDate - 1
End If
End If
End If
rsHoliday.MoveNext
Loop
%>
<%
'If New Dates are GREATER than Old Date and the new dates fall on a weekend, then move new dates to next available date (foreward).
' Otherwise
'If New Dates are LESS than Old Date and the new dates fall on a weekend, then move new dates to previous available date (backward).
If WeekDay(nStartDate) = 1 then 'If Sunday
If StartDiff < 0 Then 'Is New Start Date BEFORE the old Start date?
nStartDate = nStartDate - 2 ' If nStartDate is sooner, then subtract 2
Else if StartDiff > 0 then 'Is New Start Date AFTER the old Start date?
nStartDate = nStartDate + 1 ' If nStartDate is later, then add 1
End If
End If
Else If WeekDay(nStartDate) = 7 then 'If Saturday
If StartDiff < 0 Then 'Is New Start Date BEFORE the old Start date?
nStartDate = nStartDate - 1 ' If nStartDate is sooner, then subtract 2
Else if StartDiff > 0 then 'Is New Start Date AFTER the old Start date?
nStartDate = nStartDate + 2 ' If nStartDate is later, then add 2
End If
End If
End If
End If
If WeekDay(nEndDate) = 1 then 'If Sunday
If EndDiff < 0 Then 'Is New End Date BEFORE the old End date?
nEndDate = nEndDate - 2 ' If nEndDate is BEFORE, then subtract 2
Else if EndDiff > 0 then 'Is New End Date AFTER the old End date?
nEndDate = nEndDate + 1 ' If nEndDate is AFTER, then add 1
End If
End If
Else If WeekDay(nEndDate) = 7 then 'If Saturday
If EndDiff < 0 Then 'Is New End Date BEFORE the old End date?
nEndDate = nEndDate - 1 ' If nEndDate is BEFORE, then subtract 1
Else if EndDiff > 0 then 'Is New End Date AFTER the old End date?
nEndDate = nEndDate + 2 ' If nEndDate is AFTER, then add 2
End If
End If
End If
End If
%>
Thank you for your time,
Parallon