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
email is nospam1001 at nonags dot com