counting between dates across number of tables

--20cf3005dcd0af9f160493f39f88
Content-Type: text/plain; charset=ISO-8859-1

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)

--20cf3005dcd0af9f160493f39f88--