Re: Backup and trimming data idea

[newbie Shai]

Dear Rick,
Below are the details you required.

Some of the queries.
Query 1

SELECT DISTINCT tblLocationFrom.geoFenceName As locationFrom, tblLocationTo.geoFenceName As locationTo,
tblLink.linkID,CAST(Date_Add(tblLink.dateTimeStartJourney, Interval '".$gmtValue."' hour_minute) AS CHAR) As dateTimeStartJourney,
CAST(Date_Add(tblLink.dateTimeEndJourney, Interval '".$gmtValue."' hour_minute) AS CHAR) As dateTimeEndJourney1,CAST(Date_Add(CAST(Date_Add(tblLink.dateTimeEnd, Interval '-08:00' hour_minute) AS CHAR) , Interval '".$gmtValue."' hour_minute) AS CHAR) As dateTimeEndJourney2,
FROM tblLink
JOIN tblGeoFence AS tblLocationFrom ON tblLink.locationFromID = tblLocationFrom.geoFenceID
JOIN tblGeoFence AS tblLocationTo ON tblLink.locationToID = tblLocationTo.geoFenceID
WHERE (dateTimeStartJourney between '".$b. "' And '".$e."' And tblLink.compID=$cID

Query 2

SELECT tblGeoFence.geoFenceName, CAST(Date_Add(tblEventAlert.eventAlertDateTime , Interval '".$gmtValue."' hour_minute) AS CHAR) As eventAlertDateTime
FROM tblEventAlert
LEFT JOIN tblGeoFence ON tblEventAlert.geoFenceID=tblGeoFence.geoFenceID
WHERE tblEventAlert.link=".$lID." Order By tblEventAlert.eventAlertDateTime Asc";

Create table statements.

CREATE TABLE IF NOT EXISTS `tblLink` (
`linkID` int(5) NOT NULL,
`compID` int(5) NOT NULL,
`vehicleID` int(5) NOT NULL,
`deviceID` int(5) NOT NULL,
`locationFromID` int(5) NOT NULL,
`locationToID` int(5) NOT NULL,
`employeeIDInsert` int(5) NOT NULL,
`dateTimeInsert` datetime NOT NULL,
`dateTimeStartJourney` datetime NOT NULL,
`dateTimeEnd` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`dateTimeEndJourney` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`linkStatus` enum('a','d','e','m') NOT NULL,
PRIMARY KEY (`linkID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE IF NOT EXISTS `tblEmailLog` (
`emailLogID` int(11) NOT NULL AUTO_INCREMENT,
`compID` smallint(6) NOT NULL,
`linkID` int(11) NOT NULL DEFAULT '0',
`userID` smallint(6) NOT NULL,
`alertCodeID` tinyint(4) NOT NULL,
`eventAlertID` int(11) NOT NULL,
`userEmail` varchar(100) NOT NULL,
`alertDateTime` datetime NOT NULL,
`alertInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`emailLogID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `tblEventAlert` (
`eventAlertID` int(11) NOT NULL AUTO_INCREMENT,
`compID` int(5) NOT NULL,
`linkID` int(11) NOT NULL DEFAULT '0',
`mainDataID` int(5) NOT NULL,
`vehicleID` int(5) NOT NULL,
`geoFenceID` int(5) NOT NULL,
`eventAlertDateTime` datetime NOT NULL,
`eventAlertSentEmail` varchar(50) DEFAULT NULL,
`eventAlertMessage` varchar(255) NOT NULL,
PRIMARY KEY (`eventAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



CREATE TABLE IF NOT EXISTS `tblMainData` (
`mainDataID` int(11) NOT NULL AUTO_INCREMENT,
`linkID` int(5) NOT NULL,
`header` varchar(3) NOT NULL,
`deviceSerialNumber` varchar(20) NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`speed` float NOT NULL,
`course` int(3) NOT NULL,
`dateTimer` datetime NOT NULL,
`gpsDateTime` datetime NOT NULL,
`insertDateTime` datetime NOT NULL,
`odoMeter` float NOT NULL DEFAULT '0',
`driverID` int(5) NOT NULL,
`eventAlertID` int(11) NOT NULL DEFAULT '0',
`mainDataInsertDateTime` datetime NOT NULL,
`gpsString` varchar(450) NOT NULL,
PRIMARY KEY (`mainDataID`),
KEY `dateTime` (`dateTimer`),
KEY `linkID` (`linkID`),
KEY `eventAlertID` (`eventAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `tblSubData` (
`subDataID` int(11) NOT NULL AUTO_INCREMENT,
`mainDataID` int(11) NOT NULL,
`linkID` int(11) NOT NULL,
`eventAlertID` int(11) NOT NULL,
`deviceSerialNumber` varchar(20) NOT NULL,
`subdeviceSerialNumber` varchar(20) NOT NULL,
`dateTimer` datetime NOT NULL,
`eventType` varchar(2) NOT NULL
PRIMARY KEY (`subDataID`),
KEY `mainDataID` (`mainDataID`),
KEY `linkID` (`linkID`),
KEY `eventAlertID` (`eventAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;



Edited 1 time(s). Last edit at 06/26/2012 08:36AM by newbie Shai.