SQL not exactly a cross-tab NEED new eyes & brains
Posted: Mon Dec 08, 2003 9:23 pm
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 ;
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).
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.
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):
Anybody offer any thoughts on this? Welcome all of them, as your idea might just kick my brains cells in another direction.
TIA
Tbull
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