i am successfully using the following query to select the number of hours an
employee works on each project per month:
mysql> SELECT SUM(IF(MONTH(period)=01,hours,0)) as 'Jan',
SUM(IF(MONTH(period)=02,hours,0)) as 'Feb', . . . SUM(IF(MONTH
(period)=12,hours,0)) as 'Dec' FROM log WHERE id = '12345' AND period >=
'1999-07-01' AND period <= date_add('1999-07-01', interval 1 year) GROUP BY
pj;
the resultset (in bad ascii) is:
+-------+-------+-------+-------+------+------+-------+-------+-------+-----
--+------+-------+-------+
| pj | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep
|Oct | Nov | Dec |
+-------+-------+-------+-------+------+------+-------+-------+-------+-----
--+------+-------+-------+
| 00748 | 0.0 | 0.0 | 2.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
0.0 | 0.0 | 0.0 | 0.0 |
| 41857 | 121.5 | 140.5 | 180.5 | 90.0 | 74.5 | 150.0 | 0.0 | 0.0 |
0.0 |75.0 | 0.0 | 213.5 |
| 41992 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 37.5 |
0.0 | 0.0 | 0.0 | 0.0 |
| 42620 | 0.0 | 0.0 | -25.0 | 0.0 | 0.0 | 0.0 | 149.5 | 110.0 |
134.0 |69.5 | 134.5 | 143.5 |
| 57307 | 0.0 | 0.0 | 0.0 | 0.0 | 1.0 | 0.0 | 0.0 | 0.0 |
0.0 | 0.0 | 0.0 | 0.0 |
| 79811 | 0.0 | 0.0 | 0.0 | 45.0 | 81.0 | 0.0 | 0.0 | 0.0 |
0.0 | 0.0 | 0.0 | 0.0 |
| HOLID | 0.0 | 7.5 | 0.0 | 0.0 | 7.5 | 0.0 | 0.0 | 0.0 |
0.0 | 0.0 | 18.5 | 53.0 |
| OTHER | 9.5 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
16.0 | 0.0 | 0.0 | 0.0 |
| PERS | 0.0 | 9.5 | 15.0 | 15.0 | 8.5 | 15.0 | 0.0 | 0.0 |
0.0 | 0.0 | 1.5 | 0.0 |
| SICK | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
9.5 |14.5 | 0.0 | 0.0 |
| VACAT | 19.0 | 7.5 | 0.0 | 0.0 | 0.0 | 0.0 | 19.0 | 12.0 |
9.5 | 0.0 | 5.0 | 31.0 |
+-------+-------+-------+-------+------+------+-------+-------+-------+-----
--+------+-------+-------+
11 rows in set (0.01 sec)
you may notice that i'm making the select between the start date
'1999-07-01' and end date '2000-06-01' (one year from start date). the
problem is: i want to start the result set from the start date, in this
case, July. so the result set for one specific project might look somthing
like (mock-up):
+-------+-------+-------+-------+------+------+-------+-------+-------+-----
--+------+-------+-------+
| pj | Jul | Aug | Sep | Oct | Nov | Dec | Jan | Feb | Mar
|Apr | May | Jun |
+-------+-------+-------+-------+------+------+-------+-------+-------+-----
--+------+-------+-------+
| 00748 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
2.0 | 0.0 | 0.0 | 0.0 |
+-------+-------+-------+-------+------+------+-------+-------+-------+-----
--+------+-------+-------+
11 rows in set (0.01 sec)
any ideas?
~leif
**************************************************************************
This e-mail and any files transmitted with it may contain privileged or
confidential information. It is solely for use by the individual for whom
it is intended, even if addressed incorrectly. If you received this e-mail
in error, please notify the sender; do not disclose, copy, distribute, or
take any action in reliance on the contents of this information; and delete
it from your system. Any other use of this e-mail is prohibited. Thank you
for your compliance.
---------------------------------------------------------------------
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
