Can DateDiff calculate the number of Months within a Range by Reference Table?

[SueHopson]

Thanks, in advance for your time.

I am tryin to find a way to create a field in my query that will do the following:

• Check to see when the benefit start date falls between the FeeStart and FeeEnd dates
• Calculate the number of months between the FeeStart and FeeEnd dates for each fee period (note: benefits are ongoing between the price increases)

For example:
Benefit Start Date is 01/08/2018 and is currently set to expire on 31/08/2020. The query should return the following:

38Months as 17 (Aug 2018-Dec 2019 Inclusive)
40Months as 03 (January 2020 - Now Inclusive - or until the next expected fee end date)

Here is where I am at so far....

tbl_fees
FeeStart [Date time field formatted as dd/mm/yyyy]
Def: The date the Fee came into effect.

FeeEnd [Date time field formatted as dd/mm/yyyy]
Def: The data the Fee ceased to be in effect to a vendor price increase.

Fee [Currency field]
Def: Fee due to vendor during this time period.

FeeStart    FeeEnd            Fee
01/01/2018    31/12/2019    $38.00
01/01/2020    31/12/2022    $40.00

tbl_data
Start_Date [Date time field formatted as dd/mm/yyyy]
Def: The date our client was issued the benefit and is payable to the vendor.

End_Date [Date time field formatted as dd/mm/yyyy]
Def: The date our client benefit was cancelled and is no longer payable to the vendor.

At this time the two tables are not linked, and tbl_fees is used as a refence only; since we were not aware there would be price increases at the time the program was initiated and the database built.

qry_Reconciliation
Contains only those fields from tbl_data plus additional calculations as shown below

38Months: IIf([Start_Date] Between #01/01/2018# And #01/01/2020#,(DateDiff("m",[Start_Date],#31/12/2019#)+1),0)

Def: The number months between the start date and the the imposed end-date of 31/12/2019 for this fee. This calculation works well and allows me to calculate the fees owing up to this point.

38TOTAL: 38*[38Months]
 

40Months: IIf([Start_Date] Between #01/01/2020# And #01/01/2023#,(DateDiff("m",[Start_Date],#31/12/2022#)+1),0)

Def: The number months between the start date and the imposed end-date of 31/12/2022 (anticipated next increase) for this fee. This calculation works perfectly as it is written, except that I need the report to show payments only to today's date and this was where I gave up and thought I'm sure I've made this way more complicated than it needs to be and am open to any and all suggestions - If there's another way to tackle what I'm trying to achieve, I'm all ears (and eyes)!

Start_Date    End_Date        38Months        38TOTAL        40Months    
01/08/2018    31/07/2020    17        $646.00        0    
01/09/2018    22/05/2019    16        $608.00        0    
10/10/2018    31/10/2018    15        $570.00        0    
05/11/2018    31/10/2020    14        $532.00        0    
07/12/2018    30/11/2020    13        $494.00        0    
04/01/2019    30/09/2019    12        $456.00        0    
05/02/2019    31/01/2020    11        $418.00        0    
26/03/2019    03/10/2019    10        $380.00        0    
26/04/2019    31/03/2020    9        $342.00        0    
14/05/2019    04/06/2019    8        $304.00        0    
07/06/2019    18/11/2019    7        $266.00        0    
15/08/2019    13/09/2019    5        $190.00        0    
13/09/2019    01/11/2019    4        $152.00        0    
03/10/2019    31/10/2020    3        $114.00        0    
08/11/2019    22/11/2019    2        $76.00        0    
07/01/2020    31/12/2020    0        $0.00        36    
11/02/2020    30/04/2020    0        $0.00        35    
05/03/2020    29/02/2020    0        $0.00        34    
05/03/2020    31/08/2020    0        $0.00        34

Thanks Again!

[cactusdata]

I'm not sure I understand what the final output should be.
However, you can create a query that lists the fee for each month, and then you can sum this as you like.

First, create this tiny query and save it as Ten:

SELECT DISTINCT Abs([id] Mod 10) AS N
FROM MSysObjects;

Next, this query to generate days of months (within the entire range of Date) and save it as MonthsDateRange:

PARAMETERS 
    [DateStart] DateTime, 
    [DateEnd] DateTime;
SELECT 
    [Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000 AS Id, 
    [DateStart] AS DateStart, 
    [DateEnd] AS DateEnd, 
    DateAdd("m",[Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000,[DateStart]) AS DateMonth
FROM 
    Ten AS Ten_0, 
    Ten AS Ten_1, 
    Ten AS Ten_2, 
    Ten AS Ten_3, 
    Ten AS Ten_4, 
    Ten AS Ten_5, 
    Ten AS Ten_6
WHERE 
    (((DateAdd("m",[Ten_0].[N]+[Ten_1].[N]*10+[Ten_2].[N]*100+[Ten_3].[N]*1000+[Ten_4].[N]*10000+[Ten_5].[N]*100000+[Ten_6].[N]*1000000,[DateStart]))<=DateAdd("m",DateDiff("m",[DateStart],DateAdd("d",-1,[DateEnd])),[DateStart])) 
    AND 
    ((Ten_0.N)<=DateDiff("m",[DateStart],[DateEnd])\1) 
    AND 
    ((Ten_1.N)<=DateDiff("m",[DateStart],[DateEnd])\10) 
    AND 
    ((Ten_2.N)<=DateDiff("m",[DateStart],[DateEnd])\100) 
    AND 
    ((Ten_3.N)<=DateDiff("m",[DateStart],[DateEnd])\1000) 
    AND 
    ((Ten_4.N)<=DateDiff("m",[DateStart],[DateEnd])\10000) 
    AND 
    ((Ten_5.N)<=DateDiff("m",[DateStart],[DateEnd])\100000) 
    AND 
    ((Ten_6.N)<=DateDiff("m",[DateStart],[DateEnd])\1000000));

Finally, create a query to list the fee for each month:

SELECT 
    MonthsDateRange.DateMonth AS StartDate, 
    DateSerial(Year([DateMonth]),Month([DateMonth])+1,0) AS EndDate, 
        (Select Top 1 Fee 
        From tblFees As T 
        Where T.FeeStart <= DateMonth 
        Order By FeeStart Desc) AS Fee
FROM 
    MonthsDateRange;

Result:

This you can aggregate in several ways.

[Rabbit]

An alternative method that avoids the dummy queries is to join the two tables on the overlapping date ranges. Then you can just calculate datediff on the larger of the 2 start dates to the smaller of the 2 end dates.

[SueHopson]

Both great answers that I can use in other areas, but in this case the specific return for the query needs to be the number of months at the rate in question:


For example:
Benefit Start Date is 01/08/2018 and is currently set to expire on 31/08/2020 (this date may be adjusted annually if the vendor renews).

The query should return the following

38Months = 17
[38Months is the Field] for the number of months the fee was $38 based on the [FeeStart] and [FeeEnd] dates
(Aug 2018-Dec 2019 Inclusive)

40Months = 03
[40Months is the Field] for the number of months the fee was $40 based on the [FeeStart] and [FeeEnd] dates
(January 2020 - Now Inclusive - or until the next expected fee end (increase) date)

Rabbit,

I think yours is the response I am looking for, just not sure exactly how to get there...

[cactusdata]

As I wrote:

> This you can aggregate in several ways.

Use a crosstab:

TRANSFORM Count(*) 
SELECT Fee
FROM MonthFees
GROUP BY Fee
PIVOT Int([Fee]) & "Months";

Result:

Or:

SELECT 
    Sum(Abs([Fee]=38)) AS 38Months, 
    Sum(Abs([Fee]=40)) AS 40Months
FROM 
    MonthFees;

Result:

[Rabbit]

Sue, just take the query step by step. First thing you would want to do is to join the 2 tables together so that you find all overlapping date ranges.

What would that condition look like? You have two date ranges, there are 6 different ways those 2 date ranges can relate to each other. Write those 6 scenarios out, use actual dates if that makes it easier for you. Of those 6 scenarios, identify scenarios that you want to filter for in the report. Look at those scenarios to see the pattern in the start and end dates.

Post the SQL you have if you run into issues or once you've solved step one. After that, we can move to step 2.