Hi, Suppose there is a sql table with the following fields and values: +-----------------------------------------------------------------+ |username| start | stop |sessiontime| +--------+---------------------+----------------------+-----------+ |mehdi | 2002-08-12 00:22:20 | 2002-08-12 00:24:20 | 120 | +--------+---------------------+----------------------+-----------+ |mehdi | 2002-08-13 01:10:20 | 2002-08-13 01:40:20 | 1800 | +--------+---------------------+----------------------+-----------+ |mehdi | 2002-08-14 08:30:20 | 2002-08-14 09:30:20 | 3600 | +--------+---------------------+----------------------+-----------+ |mehdi | 2002-08-15 15:22:20 | 2002-08-15 17:22:20 | 7200 | +--------+---------------------+----------------------+-----------+
* I need ONE single sql query to return the following sum: sum = 50 * T1 + 100 * T2 + 150 * T3 where the T1,T2 & T3 are : T1 : total sessiontimes for user 'mehdi' WHERE "02:00:0" < start AND stop <= "08:00:00" T2 : total sessiontimes for user 'mehdi' WHERE "08:00:0" < start AND stop <= "14:00:00" T3 : total sessiontimes for user 'mehdi' WHERE "14:00:0" < start AND stop <= "02:00:00" for example for the above table: T1 = 1920 T2 = 3600 T3 = 7200 SUM = 50 * 1920 + 100 * 3600 + 150 * 7200 * the second question is: if we have a row like below: +-----------------------------------------------------------------+ |username| start | stop |sessiontime| +--------+---------------------+----------------------+-----------+ |mehdi | 2002-08-12 00:00:00 | 2002-08-12 10:00:00 | 36000 | +--------+---------------------+----------------------+-----------+ how to calculate the above sum ? please consider in this new row sessiontime (start - stop) lasts from T1 to T2 and it must calculate in this way: 50 * 28800 + 100 * 7200 thanks alot M.Roomi [EMAIL PROTECTED] _________________________________________________________________ Chat with friends online, try MSN Messenger: http://messenger.msn.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
