By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,469 Members | 1,026 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,469 IT Pros & Developers. It's quick & easy.

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

P: 7
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.

Expand|Select|Wrap|Line Numbers
  1. FeeStart    FeeEnd            Fee
  2. 01/01/2018    31/12/2019    $38.00
  3. 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

Expand|Select|Wrap|Line Numbers
  1. 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.

Expand|Select|Wrap|Line Numbers
  1. 38TOTAL: 38*[38Months]
  2.  
Expand|Select|Wrap|Line Numbers
  1. 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)!

Expand|Select|Wrap|Line Numbers
  1. Start_Date    End_Date        38Months        38TOTAL        40Months    
  2. 01/08/2018    31/07/2020    17        $646.00        0    
  3. 01/09/2018    22/05/2019    16        $608.00        0    
  4. 10/10/2018    31/10/2018    15        $570.00        0    
  5. 05/11/2018    31/10/2020    14        $532.00        0    
  6. 07/12/2018    30/11/2020    13        $494.00        0    
  7. 04/01/2019    30/09/2019    12        $456.00        0    
  8. 05/02/2019    31/01/2020    11        $418.00        0    
  9. 26/03/2019    03/10/2019    10        $380.00        0    
  10. 26/04/2019    31/03/2020    9        $342.00        0    
  11. 14/05/2019    04/06/2019    8        $304.00        0    
  12. 07/06/2019    18/11/2019    7        $266.00        0    
  13. 15/08/2019    13/09/2019    5        $190.00        0    
  14. 13/09/2019    01/11/2019    4        $152.00        0    
  15. 03/10/2019    31/10/2020    3        $114.00        0    
  16. 08/11/2019    22/11/2019    2        $76.00        0    
  17. 07/01/2020    31/12/2020    0        $0.00        36    
  18. 11/02/2020    30/04/2020    0        $0.00        35    
  19. 05/03/2020    29/02/2020    0        $0.00        34    
  20. 05/03/2020    31/08/2020    0        $0.00        34
Thanks Again!
3 Weeks Ago #1
Share this Question
Share on Google+
5 Replies


cactusdata
P: 79
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Abs([id] Mod 10) AS N
  2. FROM MSysObjects;
Next, this query to generate days of months (within the entire range of Date) and save it as MonthsDateRange:

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS 
  2.     [DateStart] DateTime, 
  3.     [DateEnd] DateTime;
  4. SELECT 
  5.     [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, 
  6.     [DateStart] AS DateStart, 
  7.     [DateEnd] AS DateEnd, 
  8.     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
  9. FROM 
  10.     Ten AS Ten_0, 
  11.     Ten AS Ten_1, 
  12.     Ten AS Ten_2, 
  13.     Ten AS Ten_3, 
  14.     Ten AS Ten_4, 
  15.     Ten AS Ten_5, 
  16.     Ten AS Ten_6
  17. WHERE 
  18.     (((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])) 
  19.     AND 
  20.     ((Ten_0.N)<=DateDiff("m",[DateStart],[DateEnd])\1) 
  21.     AND 
  22.     ((Ten_1.N)<=DateDiff("m",[DateStart],[DateEnd])\10) 
  23.     AND 
  24.     ((Ten_2.N)<=DateDiff("m",[DateStart],[DateEnd])\100) 
  25.     AND 
  26.     ((Ten_3.N)<=DateDiff("m",[DateStart],[DateEnd])\1000) 
  27.     AND 
  28.     ((Ten_4.N)<=DateDiff("m",[DateStart],[DateEnd])\10000) 
  29.     AND 
  30.     ((Ten_5.N)<=DateDiff("m",[DateStart],[DateEnd])\100000) 
  31.     AND 
  32.     ((Ten_6.N)<=DateDiff("m",[DateStart],[DateEnd])\1000000));
Finally, create a query to list the fee for each month:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     MonthsDateRange.DateMonth AS StartDate, 
  3.     DateSerial(Year([DateMonth]),Month([DateMonth])+1,0) AS EndDate, 
  4.         (Select Top 1 Fee 
  5.         From tblFees As T 
  6.         Where T.FeeStart <= DateMonth 
  7.         Order By FeeStart Desc) AS Fee
  8. FROM 
  9.     MonthsDateRange;
Result:



This you can aggregate in several ways.
2 Weeks Ago #2

Rabbit
Expert Mod 10K+
P: 12,401
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.
2 Weeks Ago #3

P: 7
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...
2 Weeks Ago #4

cactusdata
P: 79
As I wrote:

> This you can aggregate in several ways.

Use a crosstab:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(*) 
  2. SELECT Fee
  3. FROM MonthFees
  4. GROUP BY Fee
  5. PIVOT Int([Fee]) & "Months";
Result:



Or:

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     Sum(Abs([Fee]=38)) AS 38Months, 
  3.     Sum(Abs([Fee]=40)) AS 40Months
  4. FROM 
  5.     MonthFees;
Result:

2 Weeks Ago #5

Rabbit
Expert Mod 10K+
P: 12,401
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.
2 Weeks Ago #6

Post your reply

Sign in to post your reply or Sign up for a free account.