List:General Discussion« Previous MessageNext Message »
From:William Hamilton Date:November 1 2010 1:20am
Subject:counting between dates across number of tables
View as plain text  
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)

Thread
counting between dates across number of tablesWilliam Hamilton1 Nov