Hi Thufir, I know I am late! And you'll have already resolved. But I haven't been receiving mysql mailing list posts since november for our company server misteries!
Every date can be converted to an absolute number-of-seconds number (for instance with time_to_sec() function), so you can subtract each pair of events, in pseudo o.o. syntax: Break(n).Duration = [Break(n).EndTime - Break(n).StartTime] # in seconds Anyway I think the algorithm you will use for the your calculatiions will need procedural programming. Let me know what you did, it's interesting! Aloha! Claudio Nanni -----Messaggio originale----- Da: news [mailto:[EMAIL PROTECTED] Per conto di Thufir Inviato: martedì 11 marzo 2008 14.38 A: mysql@lists.mysql.com Oggetto: calculating breaks for an employee Just thinking out-loud, but what design would lend itself to: scheduled break: 11:00-11:15 actual break: 10:30-10:40; 11:20:11:25 the point is to capture the fact that the break was taken not at the scheduled time, as well as the odd duration. Plus, the quantity of breaks. Typically, two breaks per day, but exceptions to that should be flagged with a trigger, as well as odd durations. For quantity, COUNT can come into play. How would the duration of the break be counted? I assume that dates can be subtracted from each other resulting in a duration? Is there some sort of scalar to represent that the break wasn't at the correct time? A metric? I'm thinking that the schedule would be its own table, and then an "events" table to store login/logouts, breaks and meal breaks. Would it make sense for the PK of both tables to be the employee ID number? thanks, Thufir -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] "Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto" ***** "This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
