1. Imam tabelu sa podacim:
Code:
ET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for absences
-- ----------------------------
DROP TABLE IF EXISTS `absences`;
CREATE TABLE `absences` (
`uniqueid` int(11) NOT NULL AUTO_INCREMENT,
`employeefk` int(11) DEFAULT NULL,
`from` datetime DEFAULT NULL,
`to` datetime DEFAULT NULL,
`type` varchar(100) DEFAULT NULL,
`approved` int(1) DEFAULT NULL,
`notes` longtext,
PRIMARY KEY (`uniqueid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of absences
-- ----------------------------
INSERT INTO `absences` VALUES ('1', '1', '2015-04-15 22:08:05', '2015-04-17 22:08:10', 'Leave', '2', '');
INSERT INTO `absences` VALUES ('2', '1', '2015-04-18 00:02:00', '2015-04-19 23:59:59', 'Sick leave', '1', null);
INSERT INTO `absences` VALUES ('3', '1', '2015-04-17 23:17:07', '2015-04-17 23:17:07', 'Leave', '0', null);
INSERT INTO `absences` VALUES ('4', '1', '2015-03-20 19:37:28', '2015-03-20 19:37:28', 'Leave', '0', '');
INSERT INTO `absences` VALUES ('5', '1', '2015-04-21 00:04:17', '2015-04-22 00:04:17', 'Leave', '0', '');
INSERT INTO `absences` VALUES ('6', '1', '2015-05-14 00:45:15', '2015-05-14 00:45:15', 'Leave', '0', '');
ET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for absences
-- ----------------------------
DROP TABLE IF EXISTS `absences`;
CREATE TABLE `absences` (
`uniqueid` int(11) NOT NULL AUTO_INCREMENT,
`employeefk` int(11) DEFAULT NULL,
`from` datetime DEFAULT NULL,
`to` datetime DEFAULT NULL,
`type` varchar(100) DEFAULT NULL,
`approved` int(1) DEFAULT NULL,
`notes` longtext,
PRIMARY KEY (`uniqueid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of absences
-- ----------------------------
INSERT INTO `absences` VALUES ('1', '1', '2015-04-15 22:08:05', '2015-04-17 22:08:10', 'Leave', '2', '');
INSERT INTO `absences` VALUES ('2', '1', '2015-04-18 00:02:00', '2015-04-19 23:59:59', 'Sick leave', '1', null);
INSERT INTO `absences` VALUES ('3', '1', '2015-04-17 23:17:07', '2015-04-17 23:17:07', 'Leave', '0', null);
INSERT INTO `absences` VALUES ('4', '1', '2015-03-20 19:37:28', '2015-03-20 19:37:28', 'Leave', '0', '');
INSERT INTO `absences` VALUES ('5', '1', '2015-04-21 00:04:17', '2015-04-22 00:04:17', 'Leave', '0', '');
INSERT INTO `absences` VALUES ('6', '1', '2015-05-14 00:45:15', '2015-05-14 00:45:15', 'Leave', '0', '');
kako mogu da dobijem rezultat ako je u jednom redu 'approved'=1 da izracuna koliko dana ima od 'From' do 'To' i pomnozi sa 8 tj moze da bude 8 radnih sati u toku jednog dana.
Koristio sam DATEDIFF ali ako je u npr. u FROM=22.04.2015 a TO=22.04.2015 izbaci mi da je to 0 dana.
2. Imam tabelu sa podacima:
Code:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for travels
-- ----------------------------
DROP TABLE IF EXISTS `travels`;
CREATE TABLE `travels` (
`uniqueid` int(11) NOT NULL AUTO_INCREMENT,
`employeefk` int(11) DEFAULT NULL,
`customerfk` int(11) DEFAULT NULL,
`countryfk` int(11) DEFAULT NULL,
`cityfk` int(11) DEFAULT NULL,
`datefrom` datetime DEFAULT NULL,
`dateto` datetime DEFAULT NULL,
`cbcarfk` int(11) DEFAULT NULL,
`traveltime` decimal(11,2) DEFAULT NULL,
`visittime` decimal(11,2) DEFAULT NULL,
`kilometres` decimal(11,2) DEFAULT NULL,
`notes` longtext,
`cbpurposefk` int(11) DEFAULT NULL,
`approved` int(1) DEFAULT NULL,
`approvedcost` int(1) DEFAULT NULL,
`transportcost` decimal(11,2) DEFAULT NULL,
PRIMARY KEY (`uniqueid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of travels
-- ----------------------------
INSERT INTO `travels` VALUES ('1', '1', '72', '2', '999', '2015-04-18 20:13:19', '2015-04-19 00:00:00', '2', '1.20', '5.00', '80.00', 'test message', '1', '1', '1', '32.00');
INSERT INTO `travels` VALUES ('2', '1', '14', '2', '28007', '2015-04-18 23:19:24', '2015-04-19 00:00:00', '2', '10.00', '32.00', '10.00', null, '1', '1', '0', '5.50');
INSERT INTO `travels` VALUES ('3', '1', '21', '2', '28202', '2015-04-18 23:54:50', '2015-04-19 00:00:00', '1', '5.00', '2.50', '5.00', null, '1', '1', '1', null);
INSERT INTO `travels` VALUES ('4', '1', '1', '2', '28002', '2015-03-18 23:57:07', '2015-03-20 00:00:00', '2', '1.00', '0.50', '1.00', null, '1', '0', '0', null);
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for travels
-- ----------------------------
DROP TABLE IF EXISTS `travels`;
CREATE TABLE `travels` (
`uniqueid` int(11) NOT NULL AUTO_INCREMENT,
`employeefk` int(11) DEFAULT NULL,
`customerfk` int(11) DEFAULT NULL,
`countryfk` int(11) DEFAULT NULL,
`cityfk` int(11) DEFAULT NULL,
`datefrom` datetime DEFAULT NULL,
`dateto` datetime DEFAULT NULL,
`cbcarfk` int(11) DEFAULT NULL,
`traveltime` decimal(11,2) DEFAULT NULL,
`visittime` decimal(11,2) DEFAULT NULL,
`kilometres` decimal(11,2) DEFAULT NULL,
`notes` longtext,
`cbpurposefk` int(11) DEFAULT NULL,
`approved` int(1) DEFAULT NULL,
`approvedcost` int(1) DEFAULT NULL,
`transportcost` decimal(11,2) DEFAULT NULL,
PRIMARY KEY (`uniqueid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of travels
-- ----------------------------
INSERT INTO `travels` VALUES ('1', '1', '72', '2', '999', '2015-04-18 20:13:19', '2015-04-19 00:00:00', '2', '1.20', '5.00', '80.00', 'test message', '1', '1', '1', '32.00');
INSERT INTO `travels` VALUES ('2', '1', '14', '2', '28007', '2015-04-18 23:19:24', '2015-04-19 00:00:00', '2', '10.00', '32.00', '10.00', null, '1', '1', '0', '5.50');
INSERT INTO `travels` VALUES ('3', '1', '21', '2', '28202', '2015-04-18 23:54:50', '2015-04-19 00:00:00', '1', '5.00', '2.50', '5.00', null, '1', '1', '1', null);
INSERT INTO `travels` VALUES ('4', '1', '1', '2', '28002', '2015-03-18 23:57:07', '2015-03-20 00:00:00', '2', '1.00', '0.50', '1.00', null, '1', '0', '0', null);
u ovom slucaju da li mogu ikako dobiti rezultat ovako npr.: na osnovu prvog i cetvrtog zapisa prvi zapis od 18.04.2015 do 19.04.2014, cetvrti zapis 18.03.2015 do 20.03.2015
- 18.04.2015 ako je approved=1 to je 8 radnih sati
- 19.04.2015 8 radnih sati
- 18.03.2015 ako je approved=1 to je 8 radnih sati
- 19.03.2015 8 radnih sati
- 20.03.2015 8 radnih sati
Nadam se da me razumete, ako bude trebalo jos sta od informacija samo recite.
Unapred se zahvaljujem, srdacan pozdrav!!!!
♫♫♫ (̾●̮̮̃̾•̃̾) ♫♫♫