2

This question is about a MySQL stored procedure to select the records from two tables by joining them and also do some arithmetic operations in that.

I have 3 tables for fees one is to store fee types, another one is to store fee allocated to each student class wise, and the last one is to store the transactions like on which date they paid, and how much, etc.

This is my database and I have a query to result only transactions made: http://www.sqlfiddle.com/#!2/479e4/2

But I want to sum the amount of each fee type in (tbl_feecollection) and subtract them from corresponding fee type allocated to each student wise (tbl_stdfee) at last show the final output only balance amounts for each fee type. Could anyone please tell me how to do this? I know this is complex SQL query, but I am a beginner.

Table structures and with some data:

CREATE TABLE `tbl_admission` (
  `AdmNo` varchar(50) NOT NULL,
  `Grp` varchar(45) NOT NULL,
  `Cls` varchar(45) NOT NULL,
  `rollno` int(11) NOT NULL,  
  `StdNm` varchar(50)
);

INSERT INTO `tbl_admission` (`AdmNo`, `Grp`, `Cls`, `rollno`, `StdNm`)
VALUES ('1','4','22','1','zzz'),('2','4','22','2','xxyy');

CREATE TABLE `tbl_classes` (
  `C_Id` int(11),
  `C_Name` varchar(255) NOT NULL,
  `G_Id` int(11) NOT NULL,
  `status` tinyint(1)
);

INSERT INTO `tbl_classes` (`C_Id`, `C_Name`, `G_Id`, `status`)
VALUES (22,'X A',4,1),(23,'X B',4,1);

CREATE TABLE `tbl_feevars` (
  `id` int(11) NOT NULL,
  `FeeName` varchar(70) NOT NULL
);



INSERT INTO `tbl_feevars` (`id`, `FeeName`) 
VALUES (1,'Admission Fee'),(2,'Spl Fee'),(3,'Tution Fee'),
(4,'Computer Fee'),(5,'Stationary'),(6,'Transport'),
(7,'Hostel fee'),(8,'Lab'),(9,'Library'),(10,'Miscell');


CREATE TABLE `tbl_stdfees` (
  `id` int(11),
  `Admno` varchar(50) default NULL,
  `admissionfee` float(10,2) default '0.00',
  `splfee` float(10,2) default '0.00',
  `tutionfee` float(10,2) default '0.00',
  `computerfee` float(10,2) default '0.00',
  `stationary` float(10,2) default '0.00',
  `transport` float(10,2) default '0.00',
  `hostelfee` float(10,2) default '0.00',
  `lab` float(10,2) default '0.00',
  `library` float(10,2) default '0.00',
  `miscell` float(10,2) default '0.00'
  );

INSERT INTO `tbl_stdfees` (`id`, `Admno`, `admissionfee`, `splfee`, `tutionfee`, `computerfee`, `stationary`, `transport`, `hostelfee`, `lab`, `library`, `miscell`) 
VALUES (8,'1',5000.00,NULL,NULL,2500.00,1500.00,0.00,0.00,0.00,0.00,5000.00),
(9,'2',5000.00,0.00,0.00,2500.00,1500.00,0.00,0.00,0.00,0.00,5000.00),
(10,'3',5000.00,0.00,0.00,2500.00,1500.00,0.00,0.00,0.00,0.00,5000.00);


CREATE TABLE `tbl_feecollection` (
  `auto_id` int(11) NOT NULL auto_increment,
  `GrupNm` varchar(40) NOT NULL,
  `class` varchar(30) NOT NULL,
  `rollno` int(2) NOT NULL,
  `sadmno` int(11) NOT NULL,
  `PaM` varchar(30) NOT NULL,
  `DDCN` int(25) NOT NULL,
  `bank` varchar(30) NOT NULL,
  `cheqd` date NOT NULL,
  `remarks` varchar(150) NOT NULL,
  `pdate` date NOT NULL,
  `admissionfee` float(10,2) default '0.00',
  `splfee` float(10,2) default '0.00',
  `tutionfee` float(10,2) default '0.00',
  `computerfee` float(10,2) default '0.00',
  `stationary` float(10,2) default '0.00',
  `transport` float(10,2) default '0.00',
  `hostelfee` float(10,2) default '0.00',
  `lab` float(10,2) default '0.00',
  `library` float(10,2) default '0.00',
  `miscell` float(10,2) default '0.00',
  PRIMARY KEY  (`auto_id`)
);

INSERT INTO `tbl_feecollection` (`auto_id`, `GrupNm`, `class`, `rollno`, `sadmno`, `PaM`, `DDCN`, `bank`, `cheqd`, `remarks`, `pdate`, `admissionfee`, `splfee`, `tutionfee`, `computerfee`, `stationary`, `transport`, `hostelfee`, `lab`, `library`, `miscell`)
VALUES (1,'4','22',1,1,'Cheque',12345,'ICICI','2013-07-01','No','2013-07-01',500.00,0.00,0.00,500.00,0.00,0.00,200.00,0.00,0.00,200.00),(2,'4','22',2,2,'Cheque',78787,'IDBI','2013-07-10','No','2013-07-12',500.00,0.00,0.00,500.00,0.00,0.00,200.00,0.00,0.00,300.00),(3,'4','22',2,2,'Cheque',78789,'IDBI','2013-07-31','No','2013-07-22',500.00,0.00,0.00,500.00,0.00,0.00,200.00,0.00,0.00,300.00);

And I have a query to show some results:

set @sqlList = null;
set @query = null;
SELECT
  GROUP_CONCAT(concat('tfc.', Replace(FeeName,' ',''))) 
INTO @sqlList
FROM tbl_feevars;
SET @query 
  = CONCAT('SELECT tc.C_Name,tfc.rollno,ta.StdNm,
                tfc.pdate,',@sqlList, ' 
            FROM tbl_feecollection tfc
            INNER JOIN tbl_classes tc
              ON tfc.class = tc.C_Id
            INNER JOIN tbl_admission ta
              ON ta.AdmNo = tfc.sadmno
            where tfc.pdate >=  ''2013-07-01'' 
              and tfc.pdate <= ''2013-07-31'' Order by C_Name DESC ');

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Please tell me now how to resolve the issue.

  • Plz spend the time on your question - so much work you have already, so some more characters would be nice, like 'please' instead of 'plz'. Furthermore, we really love SQLFiddle here but it is sometimes down - so it would be really nice to have the table definitions here as well. Thanks! – dezso Jul 22 '13 at 9:48
  • thank you @dezso. Now I have posted my full db here with my query. could you pl see this and tell me now how to make this working for my requirement – Sreenivas T Jul 23 '13 at 5:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.