Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 105
Message-ID: <nlnBb.422437$0 v4.20362698@bgt nsc04-news.ops.worldn et.att.net>
Date: Tue, 09 Dec 2003 17:09:39 GMT
NNTP-Posting-Host: 12.72.193.239
X-Complaints-To: abuse@worldnet. att.net
X-Trace: bgtnsc04-news.ops.worldn et.att.net 1070989779 12.72.193.239 (Tue, 09 Dec 2003 17:09:39 GMT)
NNTP-Posting-Date: Tue, 09 Dec 2003 17:09:39 GMT
Organization: AT&T Worldnet
Xref: intern1.nntp.au s1.giganews.com mailing.databas e.mysql:138765
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 (those can be 1-24 months and cross a year boundry)
into a single table query like ;
*Code:*
SELECT (date_format(da te_created,'%m - %Y')) as RptMonth
, SUM(IF(date_cre ated != \"0000-00-00\", 1,0)) AS NEW
, date_format(dat e_created,'%m - %Y')) as Created
, SUM(IF(date_upd ated != \"0000-00-00\", 1,0)) AS Upd
, date_format(dat e_updated,'%m - %Y')) as Updated
from activity_table
where (
(date_format(da te_created,'%Y% m') between \"$begdate\" and
\"$enddate\" ) or
(date_format(da te_updated,'%Y% m') between \"$begdate\" and \"$enddate\" )
)
group by rid
Grouping by rid just to ensure that all rows are returned and to use the
SUM function. Injected > and >> manually in the following table to
highlight
what I am trying to get as results. First result example set 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 concat and sum by date_xxx
and beg-enddate or a lengthy passed array or a mysterious moon phase
(gasp):
*Code:*
SELECT (date_format(da te_created,'%m - %Y')) as RptMonth
, SUM(IF(date_cre ated != \"0000-00-00\", 1,0)) AS NEW
, date_format(dat e_created,'%m - %Y')) as Created
, SUM(IF(date_upd ated != \"0000-00-00\", 1,0)) AS Upd
, date_format(dat e_updated,'%m - %Y')) as Updated
from activity_table
where (
(date_format(da te_created,'%Y% m') between \"$begdate\" and
\"$enddate\" ) or
(date_format(da te_updated,'%Y% m') 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
email nospam1001 at nonags dot com
Content-Transfer-Encoding: 7bit
Lines: 105
Message-ID: <nlnBb.422437$0 v4.20362698@bgt nsc04-news.ops.worldn et.att.net>
Date: Tue, 09 Dec 2003 17:09:39 GMT
NNTP-Posting-Host: 12.72.193.239
X-Complaints-To: abuse@worldnet. att.net
X-Trace: bgtnsc04-news.ops.worldn et.att.net 1070989779 12.72.193.239 (Tue, 09 Dec 2003 17:09:39 GMT)
NNTP-Posting-Date: Tue, 09 Dec 2003 17:09:39 GMT
Organization: AT&T Worldnet
Xref: intern1.nntp.au s1.giganews.com mailing.databas e.mysql:138765
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 (those can be 1-24 months and cross a year boundry)
into a single table query like ;
*Code:*
SELECT (date_format(da te_created,'%m - %Y')) as RptMonth
, SUM(IF(date_cre ated != \"0000-00-00\", 1,0)) AS NEW
, date_format(dat e_created,'%m - %Y')) as Created
, SUM(IF(date_upd ated != \"0000-00-00\", 1,0)) AS Upd
, date_format(dat e_updated,'%m - %Y')) as Updated
from activity_table
where (
(date_format(da te_created,'%Y% m') between \"$begdate\" and
\"$enddate\" ) or
(date_format(da te_updated,'%Y% m') between \"$begdate\" and \"$enddate\" )
)
group by rid
Grouping by rid just to ensure that all rows are returned and to use the
SUM function. Injected > and >> manually in the following table to
highlight
what I am trying to get as results. First result example set 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 concat and sum by date_xxx
and beg-enddate or a lengthy passed array or a mysterious moon phase
(gasp):
*Code:*
SELECT (date_format(da te_created,'%m - %Y')) as RptMonth
, SUM(IF(date_cre ated != \"0000-00-00\", 1,0)) AS NEW
, date_format(dat e_created,'%m - %Y')) as Created
, SUM(IF(date_upd ated != \"0000-00-00\", 1,0)) AS Upd
, date_format(dat e_updated,'%m - %Y')) as Updated
from activity_table
where (
(date_format(da te_created,'%Y% m') between \"$begdate\" and
\"$enddate\" ) or
(date_format(da te_updated,'%Y% m') 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
email nospam1001 at nonags dot com