469,148 Members | 1,548 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,148 developers. It's quick & easy.

HELP -- SQL not exactly a cross-tab NEED 2 columns/fields result

Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 105
Message-ID: <nl***********************@bgtnsc04-news.ops.worldnet.att.net>
Date: Tue, 09 Dec 2003 17:09:39 GMT
NNTP-Posting-Host: 12.72.193.239
X-Complaints-To: ab***@worldnet.att.net
X-Trace: bgtnsc04-news.ops.worldnet.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.aus1.giganews.com mailing.database.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(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 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(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 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

Jul 19 '05 #1
0 1699

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Martin Euredjian | last post: by
9 posts views Thread by Tom | last post: by
6 posts views Thread by moi | last post: by
10 posts views Thread by Robert | last post: by
28 posts views Thread by stu_gots | last post: by
4 posts views Thread by Norman Fritag | last post: by
53 posts views Thread by souporpower | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.