That's great John! Thank you. You have stated the question correctly.
The function works but it's a bit pokey and we need to calculate this on an
ongoing basis as the time window is always changing. I'll see if I can work
with what you've offered given the caveats. Do you think changing to days
from months would get around some of the problems?
FYI I was also offered the following on sqlserver.programming but couldn't
see a way to coerce it into Access SQL:
;with x
as
(
select
ClientID
, max (ThruDate) as ThruDate
from
Employers
group by
ClientID
), y
as
(
select
e.ClientID
, e.EmployerName
, e.FromDate
, e.ThruDate
, 0 as lvl
, datediff (dd, e.FromDate, e.ThruDate) as delta
from
Employers e
join
x on x.ClientID = e.ClientID
and x.ThruDate = e.ThruDate
union all
select
e.ClientID
, e.EmployerName
, e.FromDate
, e.ThruDate
, y.lvl + 1 as lvl
, datediff (dd, e.FromDate, e.ThruDate) as delta
from
Employers e
join
y on y.ClientID = e.ClientID
and y.FromDate = e.ThruDate
)
select
ClientID
from
y
group by
ClientID
having
sum (delta) / 365.0 >= 3.0
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
If the question is
Has this employee been employed for 36 months in the last three years?
Then the SQL might look like the following
SELECT ClientID
, Sum(DateDiff("m",IIF(FromDate<DateAdd("yyyy",-3,Date()),
DateAdd("yyyy",-3,Date()), FromDate,Nz(ThruDate,Date()))) as MonthsEmployed
FROM tblClientEmployer
WHERE ThruDate > DateAdd("yyyy",-3,Date()) or ThruDate is Null
GROUP BY ClientID
HAVING Sum(DateDiff("m",IIF(FromDate<DateAdd("yyyy",-3,Date()),
DateAdd("yyyy",-3,Date()), FromDate,Nz(ThruDate,Date()))) >= 36
There are some problems here.
If I was employed from the 1st of the month to the last of the month
consecutively by 36 employers then my sum would be zero even though I worked
for the entire time.
If was employed on the last day of the month and the first day of the next
month and did this for 36 months, then my sum would be 36 even though I may
have worked a total of 72 days in the entire 36 months.
I think that your function accounts for all these problems. I'm sure there
is
a way to do this accurately with a query, but I have not worked out the
details in my head to do so.
If the VBA function works and is fast enough, I would not worry about trying
to find a pure SQL solution.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Patrick said:
I wrote a function. Would still love to see it done in Access SQL though.
Function EmploymentSpan(ByVal vlngClientID As Long) As Integer
On Error GoTo EmploymentSpan_Exit
Dim rst As DAO.Recordset
Dim strSQL As String
Dim intMonths As Integer
Dim dtmLastStart As Date
strSQL = "SELECT tblClientEmployer.FromDate AS FDate,
IIf(IsNull([ThruDate]),Date(),[ThruDate]) AS TDate FROM tblClient INNER
JOIN
tblClientEmployer ON tblClient.ClientID = tblClientEmployer.ClientID WHERE
(((tblClientEmployer.FromDate) Is Not Null And
(tblClientEmployer.FromDate)>DateAdd('yyyy',16,[BirthDate])) AND
((tblClientEmployer.ClientID)=" & vlngClientID & ")) ORDER BY
tblClientEmployer.FromDate DESC"
Set rst = DBEngine(0)(0).OpenRecordset(strSQL)
With rst
If Not (.BOF And .EOF) Then dtmLastStart = Date
Do Until (intMonths >= 36) Or .EOF
If !TDate + 1 >= dtmLastStart Then
intMonths = intMonths + DateDiff("m", !FDate,
dtmLastStart)
dtmLastStart = !FDate
End If
.MoveNext
Loop
End With
EmploymentSpan_Exit:
On Error Resume Next
rst.Close
Set rst = Nothing
EmploymentSpan = intMonths
End Function
I have a table containing Employer data for Clients. The table includes
the
following data fields.
ClientID
EmployerName
...
FromDate
ThruDate
Clients may have more than 1 Employer record.
I am trying to select those clients having 3 or more consecutive years of
employment *across* employer records working backwards from the current
employer record.
Yea, that's what I said.
Any ideas would be appreciated.
Patrick
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Patrick Jackman
Vancouver, BC
604-874-5774
