Terry wrote:
> I am trying to count overlapping dates from a single field of my program. I
> have a field which has the same stock number in it. Then I have a field that
> has the dates it was gone. I am trying to figure out how to count the dates
> that are overlapping so I get a count of the stock number that are gone at
> the same time. The stock number field and the date fields are set up in the
> following format:
>
> Stock Number Date out Date in
> 000000000 12Jan06 15Feb06
> 000000000 20Jan06 09Feb06
>
> There are about 5000 entries in the database.
>
> Any help would be greatly apprecitaed
Try my DateIntersection function in a subquery. Something like:
SELECT StockNumber, [Date out], [Date in], (SELECT COUNT(*) FROM
tblInOut AS A WHERE DateIntersection(tblInOut.[Date out], tblInOut.[Date
in], A.[Date out], A.[Date in]) > 0) AND A.[Stock Number] =
tblInOut.[Stock Number]) - 1 AS HowManyOverlapThisRange FROM tblInOut;
'Begin module code ----
Public Function DateIntersection(dt1 As Date, dt2 As Date, dt3 As Date,
dt4 As Date) As Integer
'Return the number of days overlapping two date ranges
'Assumes d1 <= d2 and d3 <= d4 Ranges can go in either order
DateIntersection = 0
If dt2 <= dt3 Then
If dt2 = dt3 Then DateIntersection = 1
Exit Function
End If
If dt4 <= dt1 Then
If dt4 = dt1 Then DateIntersection = 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt3, dt2) + 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt3, dt4) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt1, dt2) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt1, dt4) + 1
End If
End Function
'End module code ------
This will probably run slowly on 5000 records so post back if you need
something efficient so that someone else can help :-). You can also
change this function to return a boolean value instead of the number of
days that intersect. The '> 0' in the SQL would change to '= -1' if
that's done.
James A. Fortune
(E-Mail Removed)
Similar Threads
