How to solve this Month Count Query [Resolved]

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

Hi

Can you change this sql (The Full Query ) to Mysql thats help ful for me

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

This is an ANSI SQL Version Query.Should work in any database

CREATE TABLE MyTable(Sno INT IDENTITY,[Date] DATE)

INSERT INTO MyTable VALUES('05/04/2015'),('07/04/2015'),('09/04/2015'),('12/04/2015')



SELECT 

	CountRemainingMonths = COUNT(*) 

FROM (

	SELECT *

		FROM   (

				SELECT 5 mnthNumbers UNION ALL

				SELECT 6 mnthNumbers UNION ALL

				SELECT 7 mnthNumbers UNION ALL

				SELECT 8 mnthNumbers UNION ALL

				SELECT 9 mnthNumbers UNION ALL

				SELECT 10 mnthNumbers UNION ALL

				SELECT 11 mnthNumbers UNION ALL

				SELECT 12 mnthNumbers) AS MonthNum 

	WHERE MonthNum.mnthNumbers NOT IN 

	(

		SELECT MONTH([Date])

		FROM MyTable t    

	)

)X



DROP TABLE MyTable

In MySQL , we cannot use EXCEPT. Henceforth I have used NOT IN.

It can also be done by using LEFT JOIN also.

SELECT MonthNum.mnthNumbers

		FROM   (

				SELECT 5 mnthNumbers UNION ALL

				SELECT 6 mnthNumbers UNION ALL

				SELECT 7 mnthNumbers UNION ALL

				SELECT 8 mnthNumbers UNION ALL

				SELECT 9 mnthNumbers UNION ALL

				SELECT 10 mnthNumbers UNION ALL

				SELECT 11 mnthNumbers UNION ALL

				SELECT 12 mnthNumbers) AS MonthNum 

	LEFT JOIN 	

			(

				SELECT dt = MONTH([Date])

				FROM MyTable t    

			) X ON MonthNum.mnthNumbers = X.dt

	WHERE X.dt IS NULL

Finally add the Count
Hope this helps.

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Hi
Hello

where is SELECT MonthNum.mnthNumbers this field in your table?

your table sno date only so where it is come from this SELECT MonthNum.mnthNumbers ?

Mark as Answer if its helpful to you

Kumaraspcode2009@gmail.com

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Hi,
As you can figure out that, we have simulated the situation.

SELECT MonthNum.mnthNumbers

		FROM   (

				SELECT 5 mnthNumbers UNION ALL

				SELECT 6 mnthNumbers UNION ALL

				SELECT 7 mnthNumbers UNION ALL

				SELECT 8 mnthNumbers UNION ALL

				SELECT 9 mnthNumbers UNION ALL

				SELECT 10 mnthNumbers UNION ALL

				SELECT 11 mnthNumbers UNION ALL

				SELECT 12 mnthNumbers) AS MonthNum 

will generate the Month Number table.
From this table and the table where you are storing the LoanAmounts per user basis, we need to figure out those months for which the user has not payed yet (I believe that's your requirement as what I figured out from the problem statement).
For this to happen we need to perform a MINUS between these two sets.

SELECT *

		FROM   (

				SELECT 5 mnthNumbers UNION ALL

				SELECT 6 mnthNumbers UNION ALL

				SELECT 7 mnthNumbers UNION ALL

				SELECT 8 mnthNumbers UNION ALL

				SELECT 9 mnthNumbers UNION ALL

				SELECT 10 mnthNumbers UNION ALL

				SELECT 11 mnthNumbers UNION ALL

				SELECT 12 mnthNumbers) AS MonthNum 

	WHERE MonthNum.mnthNumbers NOT IN 

	(

		SELECT MONTH([Date])

		FROM MyTable t    

	)

Will do the it.
As a last thing, we need to get the count of those records which is achieved by using the COUNT function.
What extra(I think ) we need to do is to figure out the loans paid by a particular user.This can be achieved by using a WHERE CLAUSE as the filtering condition. Also the month table should be generated from 1 to 12 and from the user's first payment month, the Month Numbers should be accepted .Again it's a filtering by using the WHERE CLAUSE.
Hope this helps.

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator