O posibila solutie MS Access
Query sql_1:
CREATE TABLE ZileNelucratoare (DataCalendaristica DATETIME PRIMARY KEY)
Query sql_2:
INSERT INTO ZileNelucratoare VALUES ('2007-01-01');
Query sql_3:
INSERT INTO ZileNelucratoare VALUES ('2007-01-02');
Query sql_4:
INSERT INTO ZileNelucratoare VALUES ('2007-02-08');
Query sql_5:
CREATE TABLE ZileConcediuConsumate ( LunaZi DATETIME PRIMARY KEY , NrZileConcediuConsumate INT NOT NULL)
'0=zi lucratoare / 1=zi nelucratoare
Public Function EsteZiNelucratoare(dt As Date) As Byte 'VB Byte = Int8
If DatePart("w", dt, vbSunday, vbFirstJan1) = 7 Or DatePart("w", dt, vbSunday, vbFirstJan1) = 1 Then
EsteZiNelucratoare = 1
ElseIf IsNull(DLookup("DataCalendaristica", "ZileNelucratoare", "DataCalendaristica = #" & Format(dt, "yyyy-MM-dd") & "# ")) = False Then
EsteZiNelucratoare = 1
Else
EsteZiNelucratoare = 0
End If
End Function
Public Sub ZileConcediu(dt_start As Date, nr_zile As Long) 'insereaza inregistrari in tabela
CurrentDb.Execute "DELETE FROM ZileConcediuConsumate"
Dim luna_curenta As Long, i As Long, zile_lunare_concediu_consumate As Long
luna_curenta = Month(dt_start)
i = -1
zile_lunare_concediu_consumate = 0
While nr_zile > 0
i = i + 1
Dim dt_curenta As Date, sql As String
dt_curenta = DateAdd("d", i, dt_start)
If (luna_curenta <> Month(dt_curenta)) Then
sql = "INSERT INTO ZileConcediuConsumate VALUES( #" & Format(dt_curenta, "yyyy-MM-dd") & "#" & "," & zile_lunare_concediu_consumate & " )"
CurrentDb.Execute sql
luna_curenta = Month(dt_curenta)
zile_lunare_concediu_consumate = 0
End If
If EsteZiNelucratoare(dt_curenta) = 0 Then
'Debug.Print dt_curenta, zile_lunare_concediu_consumate
zile_lunare_concediu_consumate = zile_lunare_concediu_consumate + 1
nr_zile = nr_zile - 1
End If
Wend
sql = "INSERT INTO ZileConcediuConsumate VALUES( #" & Format(dt_curenta, "yyyy-MM-dd") & "#" & "," & zile_lunare_concediu_consumate & " )"
CurrentDb.Execute sql
End Sub
Exemplu utilizare:
[1] din VBA se apeleaza procedura cu parametrii doriti:
Call ZileConcediu ("2007-01-21" , 38 ) 'procedura insereaza in ZileConcediuConsumate inregistrarile dorite
[2] din SQL se interogheaza tabela ZileConcediuConsumate
SELECT *
FROM ZileConcediuConsumate