Counting months

[abouddan]

Hi all

I have a table let's call it "Emp". In that table I have Seq as primery key, IdEmp, StartDate as Date, EndDate as Date.
The same Employe can have many records in that table. "Employe1" can starts work in 18/1/2000 and quit work in 25/5/2001, then restart work in 3/3/2005 and still working till now, So not having an EndDate for the second record. Clear?
Table should looks like the following:

Seq--IdEmp--StartDate--EndDate
1------1---------18/1/2000--25/5/2001
2------1----------3/3/2005-- Null

My Q is:

How can I count months that Employe1 had realy worked?
Note that I want to count the complete months, If any start date is not from the biginnig of the month, that month is not counted. The same as for the end date, any end date not reaching the end of the month, that month is not counted.

The result for peivious exemple:
from 1/2/2000 to 30/4/2001 : 15
from 1/4/2005 to 30/4/2007 : 25

15+25=40 months

[cyberdwarf]

Try using the DateDiff function

HTH
Steve

[ADezii]

Hi all

I have a table let's call it "Emp". In that table I have Seq as primery key, IdEmp, StartDate as Date, EndDate as Date.
The same Employe can have many records in that table. "Employe1" can starts work in 18/1/2000 and quit work in 25/5/2001, then restart work in 3/3/2005 and still working till now, So not having an EndDate for the second record. Clear?
Table should looks like the following:

Seq--IdEmp--StartDate--EndDate
1------1---------18/1/2000--25/5/2001
2------1----------3/3/2005-- Null

My Q is:

How can I count months that Employe1 had realy worked?
Note that I want to count the complete months, If any start date is not from the biginnig of the month, that month is not counted. The same as for the end date, any end date not reaching the end of the month, that month is not counted.

The result for peivious exemple:
from 1/2/2000 to 30/4/2001 : 15
from 1/4/2005 to 30/4/2007 : 25

15+25=40 months

I posted the SQL and the code that will achieve the desired results with some fine tuning by yourself. Rather than going into a detailed explanation, I made the code self-documenting. If there are any questions whatsoever, please feel free to ask.

Initial Suggestions:

1. Set a Table Level validation on the Date Fields as follows: [EndDate] > [StartDate].
2. Set the Required property of both Date Fields = Yes.
3. The above actions will prevent the SQL from returning Negative or NULL values respectively.

SELECT Emp.IdEmp, Sum(fCalculateEmployeeWorkMonths([StartDate],[EndDate])) AS Months_Worked
FROM Emp
WHERE Emp.StartDate Is Not Null AND Emp.EndDate Is Not Null
GROUP BY Emp.IdEmp;

[B]Public Function fCalculateEmployeeWorkMonths(dteStart As Date, dteEnd As Date) As Integer[/B]
Dim blnIncludeDay As Boolean, blnIncludeMonth As Boolean
Dim intDayAdjustment As Integer, intMonthAdjustment As Integer

If Day(dteStart) = 1 Then       'Include the Month
  blnIncludeDay = True
Else
  blnIncludeDay = False         'Exclude the Month
End If

'Must check and see if the last day of the Month is included. This Day will be
'either 30 or 31 depending on the Month, but of course February, and of course,
'whether or not it is a Leap Year. Didn't want to get into the Leap Year issue,
'so for all practical purposes, the last day(s) in February will be listed as 28 or 29.
Select Case Month(dteEnd)
  Case 2    'Feb)
    If Day(dteEnd) >= 28 Then       '28 or 29 - last Day(s) in February
      blnIncludeMonth = True
    Else
      blnIncludeMonth = False
    End If
  Case 4, 6, 9, 11      '30 Day end of Month (Apr, Jun, Sep, Nov)
    If Day(dteEnd) = 30 Then
      blnIncludeMonth = True
    Else
      blnIncludeMonth = False
    End If
  Case Else             '31 Day end of Month (Jan, Mar, May, Jul, Aug, Oct, Dec)
    If Day(dteEnd) = 31 Then
      blnIncludeMonth = True
    Else
      blnIncludeMonth = False
    End If
End Select

If blnIncludeDay Then
  intDayAdjustment = 0
Else
  intDayAdjustment = -1
End If

If blnIncludeMonth Then
  intMonthAdjustment = 0
Else
  intMonthAdjustment = -1
End If

fCalculateEmployeeWorkMonths = DateDiff("m", dteStart, dteEnd) + (intDayAdjustment + intMonthAdjustment)
[B]End Function[/B]

[abouddan]

I posted the SQL and the code that will achieve the desired results with some fine tuning by yourself. Rather than going into a detailed explanation, I made the code self-documenting. If there are any questions whatsoever, please feel free to ask.

Initial Suggestions:

1. Set a Table Level validation on the Date Fields as follows: [EndDate] > [StartDate].
2. Set the Required property of both Date Fields = Yes.
3. The above actions will prevent the SQL from returning Negative or NULL values respectively.

SELECT Emp.IdEmp, Sum(fCalculateEmployeeWorkMonths([StartDate],[EndDate])) AS Months_Worked
FROM Emp
WHERE Emp.StartDate Is Not Null AND Emp.EndDate Is Not Null
GROUP BY Emp.IdEmp;

[B]Public Function fCalculateEmployeeWorkMonths(dteStart As Date, dteEnd As Date) As Integer[/B]
Dim blnIncludeDay As Boolean, blnIncludeMonth As Boolean
Dim intDayAdjustment As Integer, intMonthAdjustment As Integer

If Day(dteStart) = 1 Then       'Include the Month
  blnIncludeDay = True
Else
  blnIncludeDay = False         'Exclude the Month
End If

'Must check and see if the last day of the Month is included. This Day will be
'either 30 or 31 depending on the Month, but of course February, and of course,
'whether or not it is a Leap Year. Didn't want to get into the Leap Year issue,
'so for all practical purposes, the last day(s) in February will be listed as 28 or 29.
Select Case Month(dteEnd)
  Case 2    'Feb)
    If Day(dteEnd) >= 28 Then       '28 or 29 - last Day(s) in February
      blnIncludeMonth = True
    Else
      blnIncludeMonth = False
    End If
  Case 4, 6, 9, 11      '30 Day end of Month (Apr, Jun, Sep, Nov)
    If Day(dteEnd) = 30 Then
      blnIncludeMonth = True
    Else
      blnIncludeMonth = False
    End If
  Case Else             '31 Day end of Month (Jan, Mar, May, Jul, Aug, Oct, Dec)
    If Day(dteEnd) = 31 Then
      blnIncludeMonth = True
    Else
      blnIncludeMonth = False
    End If
End Select

If blnIncludeDay Then
  intDayAdjustment = 0
Else
  intDayAdjustment = -1
End If

If blnIncludeMonth Then
  intMonthAdjustment = 0
Else
  intMonthAdjustment = -1
End If

fCalculateEmployeeWorkMonths = DateDiff("m", dteStart, dteEnd) + (intDayAdjustment + intMonthAdjustment)
[B]End Function[/B]

Thank you Adezzi for your time I will try it,
But I think there is something about February. What about If an employee left on 28/2/2000 witch's not the end of the month, in your code this is not considered, cause you are saying that: if month=2 and day>=28 then condition=true, and that is not true in the previous exemple.

Many Thanks for your support

[abouddan]

Try using the DateDiff function

HTH
Steve

I have already tried this function but that is not good for my condition:

e.g. 1:
StartDate=1/3/2000
EndDate=31/12/2000
The result using DateDiff =9
but it must be 10 months instead.

e.g. 2:
StartDate=15/3/2000
EndDate=21/12/2000
The result using DateDiff =9
but it must be 8 months instead.

[ADezii]

Thank you Adezzi for your time I will try it,
But I think there is something about February. What about If an employee left on 28/2/2000 witch's not the end of the month, in your code this is not considered, cause you are saying that: if month=2 and day>=28 then condition=true, and that is not true in the previous exemple.

Many Thanks for your support

Didn't want to get into the Leap Year issue,
'so for all practical purposes, the last day(s) in February will be listed as 28 or 29.

I specifically referred to this in the 4-line commented paragraph, and said that I would not address this issue, but simply stated that an end date of 28 or 29 for the Month of February would for all practical purposes be considered the last day of the Month. To be exact, the pseudo code would be:

If Month = "February" Then
  If it is a Leap Year Then
    If the last Day is the 29th Then
      include the Month
    Else
      do not include the Month
    End If
  Else     'not a Leap Year
    If the last Day is the 28th Then
      include the Month
    Else
      do not include the Month
    End If
  End If
End If

NOTE: If you need the exact code for Leap Year test, let me know.

[NeoPa]

This isn't very simple I know, but the subquery makes it less complex at least.
Let us know how you get on.

SELECT IdEmp,Sum((CLng(Left([ED,4))*12+CLng(Right([ED,2)))-
                 (CLng(Left([SD,4))*12+CLng(Right([SD,2)))-
                 1) AS Months
FROM (SELECT IdEmp,
             Format(Nz([EndDate],Date())+1,'yyyymm') AS ED,
             Format([StartDate]-1,'yyyymm') AS SD
      FROM Emp)
GROUP BY IdEmp

[NeoPa]

Looking at it again, maybe I overcomplicated the method.
Try this version instead :

SELECT IdEmp,
       Sum((Year(ED)-Year(SD))*12+(Month(ED)-Month(SD)-1)) AS Months
FROM (SELECT IdEmp,
             Nz([EndDate],Date())+1 AS ED,
             [StartDate]-1 AS SD
      FROM Emp)
GROUP BY IdEmp

[NeoPa]

NB. I noticed a bug in the previous post (corrected now), where it said +12 instead of *12. If you tried the version before it was fixed it would not have worked so I suggest you try again with the fixed version.