Page 1 of 1

SQL not exactly a cross-tab NEED new eyes & brains

Posted: Mon Dec 08, 2003 9:23 pm
by Tbull
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 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 rid
Grouping by rid just to ensure that all rows are returned and to use the
SUM 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        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: 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        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 bye date_xxx and beg-enddate (gasp):

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 RptMonth

Anybody offer any thoughts on this? Welcome all of them, as your idea might just kick my brains cells in another direction.

TIA
Tbull