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 compaired 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 week based on frequency** and current date. 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 TIA W ===mixed workings === 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)