I have a Table call payments,
paymentid stuid stu_name fullamount pay_comp 1 1234 Jhon 2600 Feb 2 1212 Silva 1300 Jan 3 1234 Jhon 1300 March 4 1212 Silva 3900 April 5 3333 Perera 15600 Dec
Here a student can pay for the whole year,for several month or for a single month,Payments are done by 1300, For a example if "Silva" paid 1300 for the first time he will be paying for Jan. If he doing a another 1300 payment,Then it will be marked as for next month,Which is "Feb".If someone complete 15600 payment it will be saved as "Dec". The problem is that when calculating Total Amount for the Jan it must consider that 1300 from Dec payment.
What i want is to calculate total Amount for each month.The problem is "Pay_Com" column is insert records as above.It can be for the whole year or for a single month.Is there any way around this.
I tried different methods,can i use sum of full amount divided by 1300 to calculate full amount for each month.Can anyone suggest me a soultion or a sql Function. even tried IF and Sum with cases..
SELECT CASE WHEN round(sum(full_amount)/1300) = 1 THEN 1 ELSE 0 END AS 'Jan', CASE WHEN round(sum(full_amount)/1300) = 2 THEN 2 ELSE 0 END AS 'Feb', CASE WHEN round(sum(full_amount)/1300) = 3 THEN 3 ELSE 0 END AS 'March',
Can anyone suggest me a Solution. Thanks In Advance.
I am Looking for a Something Like.
Month Full_PaymentsRecived Jan 20000 Feb 3900 .... .... Dec 2600