I do remember the warning about complex queries slowing server but at this
stage I have no choice :( Looking for some pointers or even happy pay
someone to generate query for me. Using PHPUG as folk are in NZ :) Sorry
for the bit off topic.
I have three tables show below which I am querying in a number of ways. e.g.
I have a report which lists number of reports provided compared to number
which were due over the duration of the project.
I am puzzling over how to select the reports which were due and were
delivered during a period based on frequency** and current date.
eg: I would like to produce an output based on the past week ending on a
Friday (eg: if run on Tuesday 2nd it would still report on the previous
week ending Friday 29th). I can mostly work out the "due" part based on the
frequency ie: weekly due every week, fortnightly - I am using MOD to see if
week is odd or even, monthly not sure yet but work out if this is the last
week of the month I suppose.
**project_cstm -- reportingfrequency will be something like weekly,
fortnightly or monthly
Any constructive pointers welcome and if more details required happy to fill
in the gaps. If you think you can generate me a wizbang great query and
want paid drop me a message here of offlist.
TIA
W
===mixed workings from ===
SELECT
DATE_SUB(DATE_SUB(CURRENT_DATE,INTERVAL(5-DAYOFWEEK(CURRENT_DATE))DAY),INTERVAL
7 DAY)AS startOfPeriod,
DATE_SUB(CURRENT_DATE,INTERVAL(5-DAYOFWEEK(CURRENT_DATE))DAY)AS
endOfPeriod,
CASE project_cstm.`reportingfrequency_c` WHEN "Weekly"THEN "1"WHEN
"Fortnightly"THEN
IF(MOD(week(project.`estimated_start_date`),2)=MOD(WEEK(DATE_SUB(CURRENT_DATE,INTERVAL(5-DAYOFWEEK(CURRENT_DATE))DAY)),2),1,0)WHEN
"Monthly"THEN 'month'ELSE 'bugger!'END AS reportDue,
COUNT(notes.`parent_id`)AS deliveredReports,
project_cstm.`reportingfrequency_c` AS reportFreaquency,
project.`name` AS project_name
FROM
`project` project LEFT OUTER JOIN `notes` notes ON project.`id` =
notes.`parent_id`
LEFT OUTER JOIN `project_cstm` project_cstm ON project.`id` =
project_cstm.`id_c`
WHERE
project.`deleted` = 0
AND project.`estimated_end_date` >
DATE_SUB(CURRENT_DATE,INTERVAL(5-DAYOFWEEK(CURRENT_DATE))DAY)
GROUP BY
project.`id`
====table descriptions====
mysql> describe notes;
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| id | char(36) | NO | PRI | | |
| date_entered | datetime | NO | | | |
| date_modified | datetime | NO | | | |
| modified_user_id | char(36) | YES | | NULL | |
| created_by | char(36) | YES | | NULL | |
| name | varchar(255) | YES | MUL | NULL | |
| filename | varchar(255) | YES | | NULL | |
| file_mime_type | varchar(100) | YES | | NULL | |
| parent_type | varchar(25) | YES | | NULL | |
| parent_id | char(36) | YES | MUL | NULL | |
| contact_id | char(36) | YES | MUL | NULL | |
| portal_flag | tinyint(1) | NO | | 0 | |
| embed_flag | tinyint(1) | NO | | 0 | |
| description | text | YES | | NULL | |
| deleted | tinyint(1) | NO | | 0 | |
+------------------+--------------+------+-----+---------+-------+
15 rows in set (0.00 sec)
mysql> describe project;
+----------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+-------+
| id | char(36) | NO | PRI | | |
| date_entered | datetime | NO | | | |
| date_modified | datetime | NO | | | |
| assigned_user_id | char(36) | YES | | NULL | |
| modified_user_id | char(36) | YES | | NULL | |
| created_by | char(36) | YES | | NULL | |
| name | varchar(50) | NO | | | |
| description | text | YES | | NULL | |
| deleted | tinyint(1) | NO | | 0 | |
| estimated_start_date | date | NO | | | |
| estimated_end_date | date | NO | | | |
| status | varchar(255) | YES | | NULL | |
| priority | varchar(255) | YES | | NULL | |
+----------------------+--------------+------+-----+---------+-------+
13 rows in set (0.00 sec)
mysql> describe project_cstm;
+-----------------------+--------------+------+-----+-------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+-------------+-------+
| id_c | char(36) | NO | PRI | | |
| project_0bjective_1_c | varchar(255) | NO | | Insert text | |
| reportingfrequency_c | varchar(100) | YES | | Weekly | |
| account_id_c | char(36) | YES | | NULL | |
+-----------------------+--------------+------+-----+-------------+-------+
4 rows in set (0.00 sec)
--
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
