Search the Community

Just a fun exercise using SQLite: (inspired by this post: (https://www.autoitscript.com/forum/topic/209982-datediff_workdaysonly/) find a past or future "target" date starting from a certain date and counting a number of days, excluding any number of days of the week from the count. formally, a WorkDay function by default only Saturday and Sunday are considered non-working, but you can pass a string with the numbers corresponding to the days of the week to be considered non-working according to this scheme: (0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4 =Thursday, 5=Friday, 6=Saturday) for example, to consider Thursday, Saturday and Sunday as non-working, pass the following string as the third parameter to the _WorkDay() function: "4,6,0" To run the script you need to download the "precompiled binaries for Windows" sqlite3.dll (32 or 64 according to your AutoIt version) from this link (https://www.sqlite.org/download.html) and save it in the same folder as this script. (hope there are no bugs. Suggestions for improvement and bug report are welcome). Have fun. #include <SQLite.au3> ; -- Start the SQLite engine ------------------- Global Static $g__sSQliteDll = _SQLite_Startup(".\sqlite3.dll", False, True) Global Static $hDb = _SQLite_Open() ; by default will open a memory database. ; ---------------------------------------------- ; by default only Saturday and Sunday are considered non-working, ; but you can pass a string with the numbers corresponding to the days of the week ; to be considered non-working according to this scheme: ; (0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4 =Thursday, 5=Friday, 6=Saturday) ; for example, to consider Thursday, Saturday and Sunday as non-working, ; pass the following string as the third parameter to the _WorkDay() function: "4,6,0" ; MsgBox(64, 'example', "in four working days from today it will be the " & _WorkDay(@YEAR & "-" & @MON & "-" & @MDAY, 4)) MsgBox(64, 'example', "nine working days ago was the " & _WorkDay(@YEAR & "-" & @MON & "-" & @MDAY, -9)) _SQLite_Shutdown() Func _WorkDay($StartDate, $iDays = 0, $sHolidays = "0,6") ; $sHolidays: (0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, 6=Saturday) If Not $iDays Then Return $StartDate Local Static $asOrder[] = ['DESC', 'ASC'] ; $iDirection: if negative, search backward, if positive, search forward Local $iDirection = ($iDays < 0 ? -1 : 1) ; we generate a week for each requested day, ; this in order to be able to manage even the worst case scenario, ; where only one day per week could be selected (only one working day per week). Local $iWeeks = Abs($iDays) + 1 ; weeks involved Local $iNumDays = (7 * $iWeeks) * $iDirection ; toal number of days to be generated Local $aMyRcordset, $iMyRows, $iMyColumns, $Date1, $Date2, $StartRange, $EndRange ; shift StartDate by +/- 1 day (exclude starting day from the search) _SQLite_GetTable(-1, "SELECT date('" & $StartDate & "','" & $iDirection & " day');", $aMyRcordset, $iMyRows, $iMyColumns) $Date1 = $aMyRcordset[2] ; fetch the data from the array resulting from the previous query ; Generate EndDate (add $iNumDays to start date) _SQLite_GetTable(-1, "SELECT date('" & $Date1 & "','" & $iNumDays & " days');", $aMyRcordset, $iMyRows, $iMyColumns) $Date2 = $aMyRcordset[2] ; fetch the data from the array resulting from the previous query ; adjust Start / End date according to direction If $iDirection < 0 Then $StartRange = $Date2 $EndRange = $Date1 Else $StartRange = $Date1 $EndRange = $Date2 EndIf ; https://www.geekytidbits.com/date-range-table-sqlite/ ; a bit intricate (messed up) query (but maybe it can be simplified) Local $Query = "WITH RECURSIVE " & _ "cnt(x) AS ( " & _ "SELECT julianday('" & $StartRange & "') " & _ "UNION ALL " & _ "SELECT x+1 FROM cnt " & _ "LIMIT ((julianday('" & $EndRange & "') - julianday('" & $StartRange & "'))+1)) " & _ "SELECT date(x) as date FROM cnt where strftime('%w', date) NOT IN (" & _ "WITH RECURSIVE split(value, str) AS ( " & _ "SELECT null, '" & $sHolidays & "' || ',' " & _ ; list of non-working days of the week "UNION ALL " & _ "SELECT " & _ "substr(str, 0, instr(str, ',')), substr(str, instr(str, ',')+1) " & _ "FROM split WHERE str!='') SELECT value FROM split WHERE value IS NOT NULL) " & _ " ORDER BY date " & $asOrder[$iDirection = 1] & ";" _SQLite_GetTable(-1, $Query, $aMyRcordset, $iMyRows, $iMyColumns) ; execute above query ; _ArrayDisplay($aMyRcordset) Return $aMyRcordset[Abs($iDays) + 1] ; return the target date EndFunc ;==>_WorkDay