Início / dba / Perguntas / 339544
Accepted
HardCode
Asked: 2024-05-16 01:28:45 +0800 CST

Número de meses reais entre duas datas

  • 772

Fundo

Temos contratos de serviço que podem começar e terminar em qualquer data arbitrária. Normalmente, porém, os contratos terminam em “X anos menos um dia”. Por exemplo, intervalos de datas típicos:

  • Data de início = 08/01/2018, Data de término = 07/01/2023
  • Data de início = 01/06/2021, Data de término = 31/05/2023

No entanto, alguns contratos abrangem intervalos de datas muito arbitrários. Alguns são muito curtos, mas também podem durar alguns anos:

  • Data de início = 30/04/2024, Data de término = 16/07/2024
  • Data de início = 07/10/2024, Data de término = 30/10/2024
  • Data de início = 15/03/2021, Data de término = 25/12/2025

Requerimento

Calcule o número de meses que o contrato abrange, para determinar quantas faturas mensais serão criadas durante a vigência do contrato. Suponha que as faturas sejam geradas no último dia do mês, portanto, um contrato com data de início em 15 de abril teria sua primeira fatura gerada em 30 de abril para a "Fatura de abril".

Problema

Usar apenas DATEDIFF(MONTH, @Date1, @Date2) produz resultados que nem sempre estão alinhados com o requisito.

Quando digo abaixo "produz um resultado incorreto", não quero dizer que a DATEDIFF()função esteja errada, estou dizendo que não é o resultado que preciso para minha exigência.

  • PRINT DATEDIFF(MONTH, '2018-01-08', '2023-01-07')produz um resultado correto de 60 meses.
  • PRINT DATEDIFF(MONTH, '2021-06-01', '2023-05-31')produz um resultado incorreto de 23 meses, onde preciso de um resultado de 24.
  • PRINT DATEDIFF(MONTH, '2024-10-07', '2024-10-30')produz um resultado incorreto de 0, onde preciso de um resultado de 1.
  • PRINT DATEDIFF(MONTH, '2024-04-15', '2024-07-16')produz um resultado incorreto de 3, onde preciso de um resultado de 4
  • PRINT DATEDIFF(MONTH, '2024-10-07', '2024-10-30')produz um resultado incorreto de 0, onde preciso de um resultado de 1.
  • PRINT DATEDIFF(MONTH, '2024-09-15', '2024-10-15')produz um resultado incorreto de 1, onde preciso de um resultado de 2.

Portanto, não posso apenas + 1o DATEDIFF()resultado para todos os casos. Quando um contrato começa e termina no mesmo mês do mesmo ano, DATEDIFF()produz um in

Solução atual

Esta é a minha solução atual:

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

Esta é minha solução atual na forma de 5 testes de diferentes períodos:

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

Resultados:

-------------------------------------------------------
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

Os resultados correspondem aos meus requisitos, para os 5 conjuntos de intervalos de datas de teste.

Pergunta

Existe uma maneira mais confiável de fazer isso, além da minha CASEdeclaração desajeitada? Certamente não estou reinventando a roda aqui. Tenho certeza de que há mais casos extremos de intervalos de datas que ainda não pensei em testar. Estou fazendo isso da maneira mais difícil?

sql-server

1 respostas

  1. Best Answer

    Você não está realmente fazendo contas de datas, mas sim contando o número de eventos faturáveis ​​entre duas datas. Existem duas abordagens para resolver isso, uma usando uma tabela de calendário e outra usando matemática pura de datas... mas acho que você está perto de uma solução totalmente correta, mas seu primeiro caso está incorreto. Existem 61 faturas que serão geradas para um contrato de 18/01/2018 a 07/01/2023.

    Portanto, o método que usei foi apenas usar o EOMONTH do início e do fim e adicionar 1. Dê uma olhada mais de perto no seu primeiro caso de teste para ver quantas faturas devem ser criadas para ele, acho que você descobrirá que 61 é a resposta correta.

    ;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
    • 5

relate perguntas