determine the months in each year of an interval

[easyit]

Hi,

I'm looking for a solution for the following:
I have a table with two datefields, the startdate of a study and the end date. The studies vary between 2 to 6 years and start and different months/days.

I would like to fill a table with fields containing the months for each year that falls within the study. Example:
startdate 1-3-2012
enddate 28-2-2015

should end up in four record like:
[year]: 2012; [months]: 10
[year]: 2013; [months]: 12
[year]: 2014; [months]: 12
[year]: 2015; [months]: 2

a vba or sql solution is welcome.




EasyIT

"Do you think that’s air you're breathing?

 

[MajP]

you can make a calculated field in the query using the dateDiff function with the interval of months "M".

http://www.techonthenet.com/access/functions/date/datediff.php

 

[easyit]

Hi MajP,

thank you for your response. However, the calculation itself is not my issue, it is splitting the study period into year intervals and calculate the months in each year. I have worked out a solution in VBA, but wonder if it can be done in sql as well.

Maarten

EasyIT

"Do you think that’s air you're breathing?

 

[Remou]

You can do a lot with a counter table. In this case, the Counter table contains the integers from zero to one hundred in a field (column) called ID.

Select

SELECT Format([Mth],"yyyy") AS Yr, Count(a.Mth) AS CountOfMth
FROM (SELECT DateAdd("m",[ID],#3/1/2012#) AS Mth
FROM [Counter]
WHERE DateAdd("m",[ID],#3/1/2012#)<#2/28/2015#)  AS a
GROUP BY Format([Mth],"yyyy")

Create table

SELECT Format([Mth],"yyyy") AS Yr, Count(a.Mth) AS CountOfMth INTO MonthCount
FROM (SELECT DateAdd("m",[ID],#3/1/2012#) AS Mth
FROM [Counter]
WHERE DateAdd("m",[ID],#3/1/2012#)<#2/28/2015#)  AS a
GROUP BY Format([Mth],"yyyy")

And so on.

http://lessthandot.com

 

[Andrzejek]

What about if your dates are:
startdate 15-3-2012
enddate 2-2-2015

or
startdate 31-3-2012
enddate 1-2-2015

or
startdate 12-31-2012
enddate 1-1-2015 etc.
???

Or do you ALWAYS start at the beginning of the month and stop st the last day of the month?



Have fun.

---- Andy

 

[easyit]

Hi,

I have created my own solutions in VBA. For those who are interested:

Public Function fgetMonthsPerYear(startDate As Date, endDate As Date) As Variant

Dim myArray As Variant
Dim i As Integer
Dim tmp_EndDate, tmp_StartDate As Date
Dim StartYear, EndYear As Integer
Dim iYears As Integer
    
If startDate > endDate Then
    Exit Function
Else
    ReDim myArray(DateDiff("yyyy", startDate, endDate) + 1, 2)
    
    On Error GoTo fgetMonthsPerYear_Error
    
    StartYear = Year(startDate)
    EndYear = Year(endDate)
    iYears = EndYear - StartYear + 1
    i = 0
    tmp_EndDate = DateValue("31-12-" & Year(startDate))
    tmp_StartDate = startDate
    
    For i = 1 To (iYears)
    
    'Debug.Print tmp_StartDate & "    " & tmp_EndDate & "     " & Year(tmp_StartDate) & ": " & DateDiff("m", tmp_StartDate, tmp_EndDate) + 1
    'Debug.Print Year(tmp_StartDate) & ", " & DateDiff("m", tmp_StartDate, tmp_EndDate) + 1
    myArray(i, 1) = Year(tmp_StartDate)
    myArray(i, 2) = DateDiff("m", tmp_StartDate, tmp_EndDate) + 1
    'Debug.Print myArray( i,1) & "  " & myArray( i,2)
    tmp_StartDate = DateAdd("yyyy", 1, tmp_StartDate)
    tmp_StartDate = DateValue("1-1-" & Year(tmp_StartDate))
    
    If Year(tmp_EndDate) < (Year(endDate) - 1) Then 'nb laatste jaar moet het anders....
        tmp_EndDate = DateAdd("yyyy", 1, tmp_EndDate)
    Else
        tmp_EndDate = endDate
    End If
    Next
    fgetMonthsPerYear = (myArray)
End If

On Error GoTo 0
Exit Function

fgetMonthsPerYear_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fgetMonthsPerYear of Module mod_cono"

End Function

EasyIT

"Do you think that’s air you're breathing?