CODEWITHSUNDEEP
ms-access
rbhalla
rbhalla

Reputation: 1189

Using DatePart() with date ranges crossing the datepart boundary

I am currently trying to summarise some data tables into a report. Each record in the table consists of a date range, something like this:

StartDate    EndDate
--------------------
13/04/13     15/04/13
17/04/13     24/04/13
28/04/13     03/05/13
05/05/13     10/05/13

Assuming the date ranges signify something like days of leave, I want to be able to calculate the total amount of days of leave per month. I came across the DatePart function which seems to work apart from one edge case: when the date range crosses a month boundary. Since the DatePart function returns the month for one given date, I am no longer able to use that to determine the amount of days of leave for that edge case record (in the example above it is record 3), since it applies to two separate months.

Ideally I want my final table to look like:

 Month       #OfDays
--------------------
   4           11               (1st record - 2, 2nd record - 7, 3rd record - 2)
   5           8                (3rd record - 3, 4th record - 5)

I've considered some messy options, such as populating a temporary table having each record signifying a different day and then doing a query on that, but I am not sure how this ties in with a report. Right now my report record source is the (incorrect) query, is it possible to have a record source as a VBA function that returns a recordsource?

Another thing I thought was to possibly to have an initial query that splits up any edge cases into two seperate records, where the date range only covers one month, and then use that for my final grouping query. Is that even possible?

I feel there may be a much simpler solution to this problem yet I can't see it.

If anyone has any ideas it would be much appreciated!

Upvotes: 3

Views: 1310

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

To accomplish your task using Access queries you will need to create a table named [Numbers] with a single Number (Long Integer) column named [n] containing the numbers 1, 2, 3, ... up to the highest year you expect to be working with. I created mine as follows

   n
----
   1
   2
   3
 ...
2499
2500

You'll also need to paste the following VBA function into an Access Module

Public Function IsValidDayOfYear(YearValue As Long, DayValue As Long) As Boolean
Dim IsLeapYear As Boolean
If (YearValue Mod 400) = 0 Then
    IsLeapYear = True
ElseIf (YearValue Mod 100) = 0 Then
    IsLeapYear = False
ElseIf (YearValue Mod 4) = 0 Then
    IsLeapYear = True
Else
    IsLeapYear = False
End If
IsValidDayOfYear = (DayValue <= IIf(IsLeapYear, 366, 365))
End Function

Let's assume that your source table is called [DateRanges]. We'll start by creating a query that generates every day of the year for each year represented in the source table. The trick here is that DateSerial() "rolls over" month boundaries, so

DateSerial(2013, 1, 32) = #2013-02-01#

and

DateSerial(2013, 1, 234) = #2013-08-22#

SELECT DateSerial(yr.n, 1, dy.n) AS [Date]
FROM Numbers yr, Numbers dy
WHERE 
    (
        yr.n 
            BETWEEN (SELECT MIN(DatePart("yyyy", DateRanges.StartDate)) FROM DateRanges)
                AND (SELECT MAX(DatePart("yyyy", DateRanges.EndDate)) FROM DateRanges)
    )
    AND (dy.n < 367) AND IsValidDayOfYear(yr.n, dy.n)

For your sample data, that query returns all days in 2013.

Let's save that query as [AllDays]. Now we can use it to extract the individual days for each date range (omitting StartDate so the final counts match yours in the question)

SELECT [Date] FROM AllDays
WHERE EXISTS 
    (
        SELECT * FROM DateRanges 
        WHERE AllDays.[Date] BETWEEN DateAdd("d", 1, DateRanges.StartDate) AND DateRanges.EndDate
    )

That returns the individual days corresponding to each range, i.e.,

Date      
----------
2013-04-14
2013-04-15
2013-04-18
2013-04-19
2013-04-20
2013-04-21
2013-04-22
2013-04-23
2013-04-24
2013-04-29
2013-04-30
2013-05-01
2013-05-02
2013-05-03
2013-05-06
2013-05-07
2013-05-08
2013-05-09
2013-05-10

We can save that query as [RangeDays] and then use it to calculate our counts by month...

SELECT DatePart("m", [Date]) AS [Month], COUNT(*) AS NumOfDays
FROM RangeDays
GROUP BY DatePart("m", [Date])

...returning

Month  NumOfDays
-----  ---------
    4         11
    5          8

Upvotes: 2

Related Questions