Have included a couple of extra columns here in an attempt to clarify what
I am trying to do. In the table rid is a unique, auto-increment. The fields
date_created and date_updated are datetime format yyyymmdd hh:mm:ss. Passing a begining and ending date format of mm-yyyy as begdate 06-2003 and endate 11-2003 (those can be 1-24 months and cross a year boundry) into a single table query like ;
*Code:*
SELECT (date_format(date_created,'%m - %Y')) as RptMonth
, SUM(IF(date_created != \"0000-00-00\", 1,0)) AS NEW
, date_format(date_created,'%m - %Y')) as Created
, SUM(IF(date_updated != \"0000-00-00\", 1,0)) AS Upd
, date_format(date_updated,'%m - %Y')) as Updated
from activity_table
where (
(date_format(date_created,'%Y%m') between \"$begdate\" and \"$enddate\") or
(date_format(date_updated,'%Y%m') between \"$begdate\" and \"$enddate\")
)
group by rid
Grouping by rid just to ensure that all rows are returned and to use the
SUM function. Injected > and >> manually in the following table to highlight
what I am trying to get as results. First result example set is a series of rows
(small representative set is shown here).
*Code:*
Activity For period of Jun/2003 to Nov/2003 RptMonth New Created Upd Updated Total 07 - 2003 1 07 - 2003 0 00 - 0000 1 07 - 2003 1 07 - 2003 0 00 - 0000 2 07 - 2003 1 07 - 2003 0 00 - 0000 3 07 - 2003 1 07 - 2003 0 00 - 0000 4 07 - 2003 1 07 - 2003 0 00 - 0000 5 07 - 2003 1 > 07 - 2003 1 >> 09 - 2003 6 08 - 2003 1 08 - 2003 0 00 - 0000 7 08 - 2003 1 08 - 2003 0 00 - 0000 8 08 - 2003 1 08 - 2003 0 00 - 0000 9 09 - 2003 1 09 - 2003 0 00 - 0000 10 10 - 2003 1 10 - 2003 0 00 - 0000 11 10 - 2003 1 10 - 2003 0 00 - 0000 12 10 - 2003 1 10 - 2003 0 00 - 0000 13 10 - 2003 1 10 - 2003 0 00 - 0000 14 10 - 2003 1 10 - 2003 0 00 - 0000 15 11 - 2003 1 11 - 2003 0 00 - 0000 16
I am certain that a query can return a table that is like my desired output, but I am absolutely stuck on geting the results set. Desired output would be a table that looks like the following, note that the >> wouldn't actually be displayed it is added here, just to show where that row highlighted above would get counted. Also the Total is decremented by that 1. In the actual output I wouldn't even display the Created or Updated date.
*Code:*
Activity For period of Jun/2003 to Nov/2003 RptMonth New Created Upd Updated Total 07 - 2003 6 > 07 - 2003 0 00 - 0000 6 08 - 2003 3 08 - 2003 0 00 - 0000 9 09 - 2003 1 09 - 2003 1 >> 09 - 2003 10 10 - 2003 5 10 - 2003 0 00 - 0000 15 11 - 2003 1 11 - 2003 0 00 - 0000 16
I have tripped and ripped and fallen all over myself on this, and think that it is something like - maybe i have to concat and sum by date_xxx and beg-enddate or a lengthy passed array or a mysterious moon phase (gasp):
*Code:*
SELECT (date_format(date_created,'%m - %Y')) as RptMonth
, SUM(IF(date_created != \"0000-00-00\", 1,0)) AS NEW
, date_format(date_created,'%m - %Y')) as Created
, SUM(IF(date_updated != \"0000-00-00\", 1,0)) AS Upd
, date_format(date_updated,'%m - %Y')) as Updated
from activity_table
where (
(date_format(date_created,'%Y%m') between \"$begdate\" and \"$enddate\") or
(date_format(date_updated,'%Y%m') between \"$begdate\" and \"$enddate\")
)
group by RptMonth
Anybody offer any thoughts on this? Welcome all of them, as your idea might just kick my brains cells in another direction.
TIA email nospam1001 at nonags dot com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
