Environment is MySQL 3.23.33, PHP Version 4.3.2.
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 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,'$daform') between \"$begdate\" and
\"$enddate\") or
(date_format(date_updated,'$daform') between \"$begdate\" and
\"$enddate\")
)
group by rid
Grouping by rid just to ensure that all rows are returned and to use the
SUM functioan. Injected > and >> manually in the following table to
highlight
what I will be trying to explain in a minute. Results 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 pass an array, concat and
sum by date_xxx and beg-enddate (gasp) or something that is mysteriously
related to the phase of the moon, I just can't seem to get it:
*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 or wisdom on this? Welcome all of them, as
your idea might just kick my brains cells in another direction.
TIA