[nim]

Ett snabbt exempel på en vy i MySQL för att se in och utstämplingar.

Där den matchar senaste instämplingen och närmaste utstämpling

CREATE ALGORITHM=UNDEFINED DEFINER=`reed`@`localhost` SQL SECURITY DEFINER VIEW `stamps`
AS SELECT
   `A`.`id` AS `aid`,
   `B`.`id` AS `bid`,
   `A`.`terminalid` AS `aterminalid`,
   cast(`A`.`date` as date) AS `adate`,
   cast(`A`.`date` as time) AS `atime`,
   dayname(`A`.`date`) AS `aday`,
   `A`.`date` AS `adatetime`,
   `B`.`terminalid` AS `bterminalid`,
   cast(`B`.`date` as date) AS `bdate`,
   cast(`B`.`date` as time) AS `btime`,
   dayname(`B`.`date`) AS `bday`,
   `B`.`date` AS `bdatetime`,timestampdiff(HOUR,`A`.`date`,
   `B`.`date`) AS `timedifference`,
   `A`.`type` AS `atype`,
   `B`.`type` AS `btype`,
   `A`.`employee_no` AS `employee_no`,
   `A`.`cardtype` AS `acardtype`,
   if((substr(`A`.`cardno`,1,3) = '000'),substr(`A`.`cardno`,2),`A`.`cardno`) AS `acardno`, `B`.`cardtype` AS `bcardtype`,
   if((substr(`B`.`cardno`,1,3) = '000'),substr(`B`.`cardno`,2),`B`.`cardno`) AS `bcardno`, `A`.`firstname` AS `firstname`,
   `A`.`lastname` AS `lastname`,
   `A`.`role` AS `role`,
   `A`.`competence` AS `competence`
FROM `tt_stamps` `A`
JOIN `tt_stamps` `B` on `B`.`terminalid` = `A`.`terminalid` and `B`.`cardno` = `A`.`cardno and cast `A`.`date` as date = cast(`B`.`date` as date
WHERE 
   `A`.`cardtype` = 'TAGID' AND
   timestampdiff(HOUR,`A`.`date`,`B`.`date`) > 0 AND
   `A`.`type` = 'IN' AND
   `B`.`type` = 'OUT'
ORDER BY cast(`A`.`date` as date);

i följande struktur:

CREATE TABLE `tt_stamps` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `terminalid` varchar(255) COLLATE utf8_swedish_ci DEFAULT NULL,
  `type` enum('IN','OUT') COLLATE utf8_swedish_ci DEFAULT NULL,
  `cardno` varchar(50) COLLATE utf8_swedish_ci DEFAULT NULL,
  `cardtype` varchar(50) COLLATE utf8_swedish_ci DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `location` varchar(255) COLLATE utf8_swedish_ci DEFAULT NULL,
  `username` varchar(255) COLLATE utf8_swedish_ci DEFAULT NULL,
  `employee_no` varchar(255) COLLATE utf8_swedish_ci DEFAULT NULL,
  `firstname` varchar(255) COLLATE utf8_swedish_ci DEFAULT NULL,
  `lastname` varchar(255) COLLATE utf8_swedish_ci DEFAULT NULL,
  `role` varchar(255) COLLATE utf8_swedish_ci DEFAULT NULL,
  `competence` varchar(255) COLLATE utf8_swedish_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `terminalid` (`terminalid`,`cardno`),
  KEY `date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=4511652 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;