1

Background

We have service contracts that can start and end on any arbitrary dates. Typically however, contracts end "X years minus a day". For example, typical date ranges:

  • Start Date = 2018-01-08, End Date = 2023-01-07
  • Start Date = 2021-06-01, End Date = 2023-05-31

However, some contracts span very arbitrary date ranges. Some are very short, but can also span over a few years:

  • Start Date = 2024-04-30, End Date = 2024-07-16
  • Start Date = 2024-10-07, End Date = 2024-10-30
  • Start Date = 2021-03-15, End Date = 2025-12-25

Requirement

Calculate the number of months the contract spans, to determine how many monthly invoices will be created during the life of the contract. Assume invoices are generated on the last day of the month, so a contract with a Start Date of April 15 would have its first invoice generated on April 30 for the "April Invoice".

Problem

Using only DATEDIFF(MONTH, @Date1, @Date2) produces results that do not always align with the requirement.

When I say below, "produces an incorrect result", I don't mean the DATEDIFF() function is wrong, I'm saying it's not the result that I need for my requirement.

  • PRINT DATEDIFF(MONTH, '2018-01-08', '2023-01-07') produces a correct result of 60 months.
  • PRINT DATEDIFF(MONTH, '2021-06-01', '2023-05-31') produces an incorrect result of 23 months, where I need a result of 24.
  • PRINT DATEDIFF(MONTH, '2024-10-07', '2024-10-30') produces an incorrect result of 0, where I need a result of 1.
  • PRINT DATEDIFF(MONTH, '2024-04-15', '2024-07-16') produces an incorrect result of 3, where I need a result of 4
  • PRINT DATEDIFF(MONTH, '2024-10-07', '2024-10-30') produces an incorrect result of 0, where I need a result of 1.
  • PRINT DATEDIFF(MONTH, '2024-09-15', '2024-10-15') produces an incorrect result of 1, where I need a result of 2.

So, I cannot just + 1 the DATEDIFF() result for all cases. When a contract starts and ends in the same month of the same year, DATEDIFF() produces an in

Current Solution

This is my current solution:

PRINT CASE
    WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
        -- Special case when a contract starts and ends in the same month and year.
        THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
    WHEN MONTH(@StartDate) = MONTH(@EndDate)
        THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
    ELSE
        DATEDIFF(MONTH, @StartDate, @EndDate) + 1
    END

This is my current solution in the form of 5 tests of different date ranges:

DECLARE @StartDate AS DATE = '2018-01-08'
DECLARE @EndDate AS DATE = '2023-01-07'

PRINT '-------------------------------------------------------'
PRINT 'Test #1: @StartDate = 2018-01-08, @EndDate = 2023-01-07'
PRINT '-------------------------------------------------------'
PRINT ''

IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
    PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
    MONTH(@StartDate) = MONTH(@EndDate)
    PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
    PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'

PRINT CASE
        WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
            -- Special case when a contract starts and ends in the same month and year.
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        WHEN MONTH(@StartDate) = MONTH(@EndDate)
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
        ELSE
            DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        END

PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #2: @StartDate = 2021-06-01, @EndDate = 2023-05-31'
PRINT '-------------------------------------------------------'
PRINT ''

SET @StartDate = '2021-06-01'
SET @EndDate = '2023-05-31'

IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
    PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
    MONTH(@StartDate) = MONTH(@EndDate)
    PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
    PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'

PRINT CASE
        WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
            -- Special case when a contract starts and ends in the same month and year.
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        WHEN MONTH(@StartDate) = MONTH(@EndDate)
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
        ELSE
            DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        END

PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #3: @StartDate = 2024-04-30, @EndDate = 2024-07-16'
PRINT '-------------------------------------------------------'
PRINT ''

SET @StartDate = '2024-04-30'
SET @EndDate = '2024-07-16'

IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
    PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
    MONTH(@StartDate) = MONTH(@EndDate)
    PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
    PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'

PRINT CASE
        WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
            -- Special case when a contract starts and ends in the same month and year.
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        WHEN MONTH(@StartDate) = MONTH(@EndDate)
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
        ELSE
            DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        END

PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #4: @StartDate = 2024-10-07, @EndDate = 2024-10-30'
PRINT '-------------------------------------------------------'
PRINT ''

SET @StartDate = '2024-10-07'
SET @EndDate = '2024-10-30'

IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
    PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
    MONTH(@StartDate) = MONTH(@EndDate)
    PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
    PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'

PRINT CASE
        WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
            -- Special case when a contract starts and ends in the same month and year.
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        WHEN MONTH(@StartDate) = MONTH(@EndDate)
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
        ELSE
            DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        END

PRINT ''
PRINT '-------------------------------------------------------'
PRINT 'Test #5: @StartDate = 2024-09-15, @EndDate = 2024-10-15'
PRINT '-------------------------------------------------------'
PRINT ''

SET @StartDate = '2024-09-15'
SET @EndDate = '2024-10-15'

IF MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
    PRINT 'The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.'
ELSE IF
    MONTH(@StartDate) = MONTH(@EndDate)
    PRINT 'The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.'
ELSE
    PRINT 'The dates contain the different months. We are adding 1 to the DATEDIFF() result.'

PRINT CASE
        WHEN MONTH(@StartDate) = MONTH(@EndDate) AND YEAR(@StartDate) = YEAR(@EndDate)
            -- Special case when a contract starts and ends in the same month and year.
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        WHEN MONTH(@StartDate) = MONTH(@EndDate)
            THEN DATEDIFF(MONTH, @StartDate, @EndDate) 
        ELSE
            DATEDIFF(MONTH, @StartDate, @EndDate) + 1
        END

Results:

-------------------------------------------------------
Test #1: @StartDate = 2018-01-08, @EndDate = 2023-01-07
-------------------------------------------------------
The dates contain the same months. We are NOT adding 1 to the DATEDIFF() result.
60
 
-------------------------------------------------------
Test #2: @StartDate = 2021-06-01, @EndDate = 2023-05-31
-------------------------------------------------------
The dates contain the different months. We are adding 1 to the DATEDIFF() result.
24
 
-------------------------------------------------------
Test #3: @StartDate = 2024-04-30, @EndDate = 2024-07-16
-------------------------------------------------------
The dates contain the different months. We are adding 1 to the DATEDIFF() result.
4
 
-------------------------------------------------------
Test #4: @StartDate = 2024-10-07, @EndDate = 2024-10-30
-------------------------------------------------------
The dates contain the same month and year. We are adding 1 to the DATEDIFF() result.
1
 
-------------------------------------------------------
Test #5: @StartDate = 2024-09-15, @EndDate = 2024-10-15
-------------------------------------------------------
The dates contain the different months. We are adding 1 to the DATEDIFF() result.
2

The results do match my requirements, for the 5 sets of test date ranges.

Question

Is there a more reliable way to do this, other than my clumsy CASE statement? Surely I'm not reinventing the wheel here. I'm sure there are more edge cases of date ranges that I have not yet thought of testing. Am I doing this the hard way?

1 Answer 1

5

You aren't really doing date math, you are counting the number of billable events between two dates. There are two approaches to solving this, one using a calendar table and the other using pure date math... but I think you are close to a fully correct solution, but your first case is incorrect. There are 61 invoices that will be generated for a contract from 1/18/2018 - 1/7/2023.

So the method I went with was to just use the EOMONTH of the start and end and add a 1. Please take a closer look at your first test case to see how many invoices should be created for it, I think you will find that 61 is the correct answer.

;WITH CTE_Data AS
    (
    SELECT ContractID, StartDate, EndDate, MonthStart = DATEADD(DAY, 1, EOMONTH(StartDate, -1))
        , DATEDIFF(MONTH, EOMONTH(StartDate), EOMONTH(EndDate)) + 1 AS BillableMonth
    FROM (VALUES (1, '1/18/2018', '1/7/2023')
                , (2, '6/1/2021', '5/31/2023')
                , (3, '4/30/2024', '7/16/2024')
                , (4, '10/7/2024', '10/30/2024')
                , (5, '9/15/2024', '10/15/2024')
            ) AS P (ContractID, StartDate, EndDate)
    )
SELECT * FROM CTE_Data
1
  • Thank you. I didn't realize I really needed 61 invoices for the first example. This ties out with Finance's manual spreadsheet of billing.
    – HardCode
    Commented May 15 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.