Dear All,
I got few tables as below. So then what I do on any time I will calculate the total points collected based on each table via some of this queries like below. What I am using now is the sum function. I am confuse should I like run a monthly service which will total and store in separate table the total for each month rather than doing it live calculation? Which one is more effective?
Query 1
Select sum(tblDriverF2FPrograms.f2FProgramsPoints) As sumF2FPrograms FROM tblDriverF2FPrograms Where tblDriverF2FPrograms.driverID=".$driverID. " And tblDriverF2FPrograms.clientID=".$clientID." And Month(tblDriverF2FPrograms.dateTimeInsert)=".$monthValue. " And Year(tblDriverF2FPrograms.dateTimeInsert)=".$yearValue
Query 2
Select sum(tblDriverMerits.meritsPoints) As sumMerits FROM tblDriverMerits Where tblDriverMerits.driverID=".$driverID. " And tblDriverMerits.clientID=".$clientID." And Month(tblDriverMerits.dateTimeInsert)=".$monthValue. " And Year(tblDriverMerits.dateTimeInsert)=".$yearValue
Table 1
CREATE TABLE IF NOT EXISTS `tblDriverF2FPrograms` (
`driverF2FProgramsID` int(5) NOT NULL auto_increment,
`clientF2FProgramsID` int(5) NOT NULL,
`driverID` int(5) NOT NULL,
`clientID` int(5) NOT NULL,
`employeeIDInsert` int(5) NOT NULL,
`dateTimeInsert` datetime NOT NULL,
`employeeIDVerify` int(5) NOT NULL,
`dateTimeVerify` datetime NOT NULL,
`f2FProgramsPoints` int(2) NOT NULL,
`driverF2FProgramsUpdateStatus` enum('Draft','Approved') NOT NULL,
PRIMARY KEY (`driverF2FProgramsID`),
KEY `driverID` (`driverID`),
KEY `clientID` (`clientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Table 2
CREATE TABLE IF NOT EXISTS `tblDriverMerits` (
`driverMeritsID` int(5) NOT NULL auto_increment,
`clientMeritsID` int(5) NOT NULL,
`driverID` int(5) NOT NULL,
`clientID` int(5) NOT NULL,
`employeeIDInsert` int(5) NOT NULL,
`dateTimeInsert` datetime NOT NULL,
`employeeIDVerify` int(5) NOT NULL,
`dateTimeVerify` datetime NOT NULL,
`meritsPoints` int(2) NOT NULL,
`driverMeritsUpdateStatus` enum('Draft','Approved') NOT NULL,
PRIMARY KEY (`driverMeritsID`),
KEY `driverID` (`driverID`),
KEY `clientID` (`clientID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1