Hi,
I need to find out the number of payable months for the Student monthly payment. Pl find the course details and Amount Paid in the table given below.
StudentAdmission | CourseFeePaid | ||||||
StudentName | MonthJoined | MonthlyFee | StudentName | MonthlyFee | PaidDate | ForMonth | |
Usha | Jun-14 | 5000 | Usha | 5000 | 2014-06-15′ | Jun-14 | |
Ramya | Sep-14 | 5000 | Usha | 5000 | 2014-07-06′ | Jul-14 | |
Usha | 5000 | 2014-08-01′ | Aug-14 | ||||
Usha | 5000 | 2014-09-12′ | Sep-14 | ||||
Ramya | 5000 | 2014-09-03′ | Sep-14 | ||||
Ramya | 5000 | 2014-10-16′ | Oct-14 |
Now I want to generate the number of payable months as on date like Usha – 6 months ie six rows & Ramya – 3 months ie 3 rows in SQL Server
StudentName | MonthtobePaid | MonthFee |
Usha | Jun-14 | 5000 |
Usha | Jul-14 | 5000 |
Usha | Aug-14 | 5000 |
Usha | Sep-14 | 5000 |
Usha | Oct-14 | 5000 |
Usha | Nov-14 | 5000 |
Ramya | Sep-14 | 5000 |
Ramya | Oct-14 | 5000 |
Ramya | Nov-14 |
5000 |
Pl suggest valuable feedback. Thanks in advance.
Hi Uma,
From your description, if you want to get the month value from the date. You could use the
MONTH function. It will represents the month of the specified date. If the data type is varchar, you could use the
SUBSTRING or the LEFT function to get the characters. You could refer to the following code.
select StudentName, ForMonth as MonthtobePaid, MONTH(PaidDate) as PayMonth, SUBSTRING(ForMonth,1,3) as PayMoth2 from [Table]
OutPut:
StudentName MonthtobePaid PayMonth PayMoth2 Ramya Oct-14 10 Oct Ramya Sep-14 9 Sep Usha Jun-14 6 Jun Usha Jul-14 7 Jul Usha Aug-14 8 Aug Usha Sep-14 9 Sep
If you want to get the total amount of Paid, you could use SUM function.
Uma Sengoden
Now I want to generate the number of payable months as on date like Usha – 6 months ie six rows & Ramya – 3 months ie 3 rows in SQL Server
StudentName MonthtobePaid MonthFee Usha Jun-14 5000 Usha Jul-14 5000 Usha Aug-14 5000 Usha Sep-14 5000 Usha Oct-14 5000 Usha Nov-14 5000 Ramya Sep-14 5000 Ramya Oct-14 5000 Ramya Nov-14 5000
For this issue, I suggest you could use Group By and Order By clause. Please try to use the following code.
select StudentName, ForMonth as MonthtobePaid, MonthlyFee from [Table] group by StudentName, ForMonth, MonthlyFee, PaidDate order by StudentName, PaidDate
OutPut:
StudentName MonthtobePaid MonthlyFee Ramya Sep-14 5000 Ramya Oct-14 5000 Usha Jun-14 5000 Usha Jul-14 5000 Usha Aug-14 5000 Usha Sep-14 5000
If you have any other questions about my reply, please let me know freely.
Best Regards,
Dillion
Hi,
Thanks for reply.
Pl let me know how to generate the number of payable months as per data given below.
Now I want to generate the number of payable months as on date like Usha – 6 months ie six rows & Ramya – 3 months ie 3 rows in SQL Server
StudentName | MonthtobePaid | MonthFee |
Usha | Jun-14 | 5000 |
Usha | Jul-14 | 5000 |
Usha | Aug-14 | 5000 |
Usha | Sep-14 | 5000 |
Usha | Oct-14 | 5000 |
Usha | Nov-14 | 5000 |
Ramya | Sep-14 | 5000 |
Ramya | Oct-14 | 5000 |
Ramya | Nov-14 |
5000 |
Pl suggest valuable feedback. Thanks in advance.
First 2 tables are available. Third one which I have given now is to be generated. Pl suggest.
Hi Uma,
Uma Sengoden
Now I want to generate the number of payable months as on date like Usha – 6 months ie six rows & Ramya – 3 months ie 3 rows in SQL Server
StudentName MonthtobePaid MonthFee Usha Jun-14 5000 Usha Jul-14 5000 Usha Aug-14 5000 Usha Sep-14 5000 Usha Oct-14 5000 Usha Nov-14 5000 Ramya Sep-14 5000 Ramya Oct-14 5000 Ramya Nov-14 5000
Please try to use the following code, I suppose it can achieve it.
select StudentName, ForMonth as MonthtobePaid, MonthlyFee from [Table] group by StudentName, ForMonth, MonthlyFee, PaidDate order by StudentName, PaidDate
If you have any other questions about my reply, please let me know freely.
Best Regards,
Dillion