Thanks Jochen, looks like PHP to process and squirt back into a table is the way to go for this till dashboard developers provide some options on getting data into their system.
cheers for the input guys - hoe to look at this again next week. W On 17 November 2010 11:45, Jochen Daum <[email protected]> wrote: > 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]<nzphpug%[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]<nzphpug%[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]
