CODEWITHSUNDEEP
sqlsql-serversql-server-2012
LCJ
LCJ

Reputation: 22652

Finding the months in between without considering the actual number of days

I have following table in SQL Server 2012 to store payments received for a service. This is not a normalized table – but I don’t have control to change it.

CREATE TABLE #ServicePaymentCollection (ServiceID INT, ServiceDate DATE, ServiceAmount INT, 
                                        PaymentDate DATE, AmountPaid INT)
INSERT INTO #ServicePaymentCollection
SELECT 2, '2017-01-30', 1200, '2017-01-31', 50 UNION
SELECT 2, '2017-01-30', 1200, '2017-02-01', 200 UNION
SELECT 2, '2017-01-30', 1200, '2017-05-20', 200 UNION
SELECT 2, '2017-01-30', 1200, '2017-11-20', 200 UNION
SELECT 2, '2017-01-30', 1200, '2017-12-20', 200 UNION
SELECT 2, '2017-01-30', 1200, '2018-01-10', 200 UNION
SELECT 2, '2017-01-30', 1200, '2018-02-15', 150

I need to list the “Month Difference” for each row. The service was done on Jan/30/2017. First payment was received on Jan/31/2017. For that row, month difference is 0.

Second payment is received on Feb/01/2017. For this row, the month difference is 1.

Following query works fine until the year is changed to 2018 from 2017. When the payment is received Jan/10/2018, it is showing month difference of 100. How to fix it.

Note: This is a simplified scenario for my actual project.

SELECT *, (PaymentYearMonth - ServiceYearMonth) AS MonthDifference
FROM
(
    SELECT *,
            CONVERT(INT,(CONVERT(VARCHAR(20),YEAR(ServiceDate)) + RIGHT('00'+CONVERT(VARCHAR(20), MONTH(ServiceDate)),2)  )) ServiceYearMonth,
            CONVERT(INT,(CONVERT(VARCHAR(20),YEAR(PaymentDate)) + RIGHT('00'+CONVERT(VARCHAR(20), MONTH(PaymentDate)),2)  )) PaymentYearMonth 
    FROM #ServicePaymentCollection
)T
ORDER BY MonthDifference

Result

enter image description here

Upvotes: 0

Views: 42

Answers (1)

jyao
jyao

Reputation: 1630

I think the following query will solve your issue (I kept your original code but add a new column so you can compare the difference)

SELECT *, (PaymentYearMonth - ServiceYearMonth) AS MonthDifference, newMonthDiff = datediff(month, ServiceDate, PaymentDate)
FROM
(
    SELECT *,
            CONVERT(INT,(CONVERT(VARCHAR(20),YEAR(ServiceDate)) + RIGHT('00'+CONVERT(VARCHAR(20), MONTH(ServiceDate)),2)  )) ServiceYearMonth,
            CONVERT(INT,(CONVERT(VARCHAR(20),YEAR(PaymentDate)) + RIGHT('00'+CONVERT(VARCHAR(20), MONTH(PaymentDate)),2)  )) PaymentYearMonth 
            --, ServiceDate, PaymentDate
    FROM #ServicePaymentCollection
)T
ORDER BY MonthDifference

The result is as follows

enter image description here

Upvotes: 1

Related Questions