
Let us analyze the problem first.We need to figure out those months for which the user have not payed the LoanAmount.
In that case we know that in a year we have 12 months.So if we subtract the months for which the user has payed the loan amount from the total number of months, then we will get our desired output.
In that case, the algorithm will be
Step 1) Generate a Month Table
Step 2) Find the month numbers for which the user has payed the Loan Amount.
Step 3) Subtract Step 1 and Step 2
Step 4) Take a count of the remaining months obtained from Step 3
But in your case the month table will be generated from the first month the loan amount was payed. So it will start from May(i.e. Month Number 5).
SQL SERVER SOLUTION
---------------------------
Step 1:
--Generate a number table between May to December
;WITH MonthNumCTE AS
(
SELECT mnthNumbers=5
UNION ALL
SELECT mnthNumbers+1
FROM MonthNumCTE WHERE mnthNumbers<12
)
SELECT *
FROM MonthNumCTE
/* Result */
mnthNumbers
5
6
7
8
9
10
11
12
Step 2
DECLARE @T TABLE(Sno INT IDENTITY,[Date] DATE)
INSERT INTO @T VALUES('05/04/2015'),('07/04/2015'),('09/04/2015'),('12/04/2015')
SELECT LoanAmountPaidMonths = MONTH([Date]) FROM @t
/* Result */
LoanAmountPaidMonths
5
7
9
12
Step 3:
SELECT RemainingMonths = m.mnthNumbers
FROM MonthNumCTE m
EXCEPT
SELECT MONTH([Date])
FROM @t
--Observe the EXCEPT KEYWORD.It returns distinct rows from the left input query that aren’t output by the right input query.
/*Result*/
RemainingMonths
6
8
10
11
Step 4:
SELECT CountRemainingMonths = COUNT(*) FROM (
SELECT RemainingMonths = m.mnthNumbers
FROM MonthNumCTE m
EXCEPT
SELECT MONTH([Date])
FROM @t
)X
/*Result*/
CountRemainingMonths
4
The Full Query
DECLARE @T TABLE(Sno INT IDENTITY,[Date] DATE)
INSERT INTO @T VALUES('05/04/2015'),('07/04/2015'),('09/04/2015'),('12/04/2015')
;WITH MonthNumCTE AS
(
SELECT mnthNumbers=5
UNION ALL
SELECT mnthNumbers+1
FROM MonthNumCTE WHERE mnthNumbers<12
)
SELECT CountRemainingMonths = COUNT(*) FROM (
SELECT RemainingMonths = m.mnthNumbers
FROM MonthNumCTE m
EXCEPT
SELECT MONTH([Date])
FROM @t
)X
--
Thanks & Regards,
RNA Team
Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator