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 all
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 Created
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 ridSUM functioan. Injected >> 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: Select all
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 16Code: Select all
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 16Code: Select all
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 Created
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 RptMonthAnybody offer any thoughts on this? Welcome all of them, as your idea might just kick my brains cells in another direction.
TIA
Tbull