A "WorkDay" function using SQLite

[Gianni]

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.

expandcollapse popup

#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

 

Edited April 5, 2023 by Gianni