# How to solve this Month Count Query [Resolved]

Posted by Jayakumars under Sql Server on 10/4/2015 | Points: 10 | Views : 1354 | Status : [Member] [MVP] | Replies : 5
Hi

I have one Doubt for ex:
Loanamount -> 35000
Loan Issue Date 04/04/2015 -> explian Date 04 Apr 2015

Loan payed member payed like this

Sno Date
1. 04/05/2015 -> Month May
2. 04/07/2015 -> Month July
3. 04/09/2015 ->Month Sep
4. 04/12/2015 -> Month Dec

so here left some month like this
month missing here this 6,8,10,11
so i need count is 4
how will do this Sql server Query

Mysql is more most helpful for me.

Kumaraspcode2009@gmail.com

#### Responses

Posted by: Rajnilari2015 on: 10/4/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50
 1 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 TeamJayakumars, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jayakumars on: 10/4/2015 [Member] [MVP] Bronze | Points: 25
 0 HiCan you change this sql (The Full Query ) to Mysql thats help ful for meMark as Answer if its helpful to you Kumaraspcode2009@gmail.comJayakumars, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Rajnilari2015 on: 10/4/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 25
 0 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 TeamJayakumars, if this helps please login to Mark As Answer. | Alert Moderator
Posted by: Jayakumars on: 10/4/2015 [Member] [MVP] Bronze | Points: 25
 0 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 TeamJayakumars, if this helps please login to Mark As Answer. | Alert Moderator