How to solve this Month Count Query [Resolved]

Posted by Jayakumars under Sql Server on 10/4/2015 | Points: 10 | Views : 364 | 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.

Mark as Answer if its helpful to you


Responses

Posted by: Rajnilari2015 on: 10/4/2015 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
1
Down

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

Posted by: Jayakumars on: 10/4/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi

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






Mark as Answer if its helpful to you

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

Up
0
Down
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

Posted by: Jayakumars on: 10/4/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
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

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

Up
0
Down
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

Login to post response