So i have 2 tables of this format,
actual_data:
ID,ROLL_NO,ADM_DT,FEES
1,9895987650,2017-11-14,10000
2,78578686,2017-11-10,130
3,434343,2017-11-15,20
temp_data:
ID,R_NO,FS,FT
1,9895987650,9000,F1
5,9895987650,5000,F2
3,78578686,150,F3
4,123456,24141,F4
I'm trying to create a stored-procedure here,
DELIMITER $$
CREATE PROCEDURE `myproc`()
BEGIN
SELECT a.*,b.* FROM actual_data a, temp_data b
WHERE a.ROLL_NO = b.R_NO
AND
((b.FS >= a.FEES AND b.FT IS NOT NULL)
OR
(b.FS < a.FEES AND b.FT IS NOT NULL));
END $$
DELIMITER;
This will give me the following result:
ID,ROLL_NO,ADM_DT,FEES,ID,R_NO,FS,FT
1,9895987650,2017-11-14,10000,1,9895987650,9000,F1
1,9895987650,2017-11-14,10000,5,9895987650,5000,F2
2,78578686,2017-11-10,130,3,78578686,150,F3
Now this is where i'm getting stuck.
What i would like to do is,
IF b.FS >= a.FEES AND b.FT IS NOT NULL
is TRUE
, i would like to select R_NO,sum(FEES) GROUP BY R_NO HAVING sum(FEES) > 1000 ORDER BY R_NO
from the above query result.
ELSE IF b.FS < a.FEES AND b.FT IS NOT NULL
is TRUE
, i would like to select R_NO,sum(FEES) GROUP BY R_NO HAVING sum(FEES) < 1000 ORDER BY R_NO
from the above query result.
Is this possible to do through a stored-procedure? If so, could I get some suggestions as to how this can be done? I'm a newbie when it comes to MySql, so kindly help me out.
Aucun commentaire:
Enregistrer un commentaire