0

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
2
Contributors
1
Reply
2
Views
6 Years
Discussion Span
Last Post by almostbob
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.