Hi William, get the book "SQL for Smarties" from Joe Celko. What you want is a calendar table.
In short, a table that contains: - all dates for the next 7 years - for each Day: - what week in month - what weekday - is public hokiday Then join with your date range tables, you need a bit of indexing trickery to get it work fast. On Wed, Nov 17, 2010 at 11:24 AM, William Hamilton <[email protected]> wrote: > 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] -- NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected]
