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.

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

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

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