Hi,
What you request i think it can become like this :
SELECT SUM(IF(HOUR(start)>'02:00:00' AND
HOUR(stop)<='08:00:00',50*sessiontime,0)+IF(HOUR(start)>'08:00:00' AND
HOUR(stop)<='14:00:00',100*sessiontime,0)+IF(HOUR(start)<'14:00:00' AND
HOUR(stop)<='02:00:00',150*sessiontime,0)) from YOUR_TABLE WHERE
username='mehdi' AND start BETWEEN '2002-08-12' AND '2002-08-16';
...this statement it should be work how you wish, but unfortunatelly i think
this SQL don't have good result because of conditions:
'WHERE "02:00:0" < start AND stop <= "08:00:00"
I suppose you want to find what is BETWEEN this HOURs.I think it's a good
ideea if you use and make comparision between hours using TIME_TO_SEC() AND
SEC_TO_TIME.
Regards,
Gelu
_____________________________________________________
G.NET SOFTWARE COMPANY
Permanent e-mail address : [EMAIL PROTECTED]
[EMAIL PROTECTED]
----- Original Message -----
From: "Mehdi Roomi" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, September 06, 2002 6:37 AM
Subject: complex sql query...
> 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
>
>
---------------------------------------------------------------------
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
