[RESOLVED]to generate payable months

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

Leave a Reply