This is possible, ish..
I posted a similar question to the list a while ago, and the only select
query I ended up with was this set of multiple queries, one for each
interval of a day going back 5 days;
select stgpool_name,devclass_name,'<=1 day',count(*) as Total from
volumes where status='PENDING' and
cast((current_timestamp-PENDING_DATE)days as decimal)<=1 group by
stgpool_name,devclass_name
select stgpool_name,devclass_name,'>1 <=2 days',count(*) as Total from
volumes where status='PENDING' and
cast((current_timestamp-PENDING_DATE)days as decimal)>1 and
cast((current_timestamp-PENDING_DATE)days as decimal)<=2 group by
stgpool_name,devclass_name
select stgpool_name,devclass_name,'>2 <=3 days',count(*) as Total from
volumes where status='PENDING' and
cast((current_timestamp-PENDING_DATE)days as decimal)>2 and
cast((current_timestamp-PENDING_DATE)days as decimal)<=3 group by
stgpool_name,devclass_name
select stgpool_name,devclass_name,'>3 <=4 days',count(*) as Total from
volumes where status='PENDING' and
cast((current_timestamp-PENDING_DATE)days as decimal)>3 and
cast((current_timestamp-PENDING_DATE)days as decimal)<=4 group by
stgpool_name,devclass_name
select stgpool_name,devclass_name,'>4 <=5 days',count(*) as Total from
volumes where status='PENDING' and
cast((current_timestamp-PENDING_DATE)days as decimal)>4 and
cast((current_timestamp-PENDING_DATE)days as decimal)<=5 group by
stgpool_name,devclass_name
Matt.
_-'-_
-|-
-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Prather, Wanda
Sent: Wednesday, December 01, 2004 5:40 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: SQL Help
Andy,
I don't think this is possible.
DISTINCT doesn't help
TSM always responds "unknown SQL column" when a results column is
included in the group by
It won't take a function in the GROUP BY
And It won't let me do a SELECT inside the FROM
-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Andrew Raibeck
Sent: Wednesday, December 01, 2004 10:37 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: SQL Help
Try adding the DISTINCT keyword between SELECT and DATE(PENDING_DATE),
i.e.
select distinct date(pending_date) ...
You'll want to verify that the results are correct by matching up the
counts against the results of the select without DISTINCT. For example,
using the output you show below, I'd expect to see a single line showing
a
count of 5 for 11/27/2004.
Regards,
Andy
Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: storman AT us.ibm DOT com
The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.
"ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 12/01/2004
08:18:16:
> That does not give me the desired results:
>
> tsm: ULTSM> select date(pending_date) as "Date",count(*) from volumes
> where -
> cont> status='PENDING' group by pending_date
>
> Date Unnamed[2]
> ---------- -----------
> 2004-11-27 1
> 2004-11-27 1
> 2004-11-27 1
> 2004-11-27 1
> 2004-11-27 1
>
> group by pending_date groups by the time stamp not the date stamp. I
> want a count of pending tapes by date not by second.
>
> >>> gretchen AT PRINCETON DOT EDU 12/1/2004 10:02:40 AM >>>
> The group by statement is incorrect, use the following:
>
> group by pending_date
>
> the conversion to date format is already taken care of
> in the first part of the select statement.
>
> David E Ehresman wrote:
>
> > tsm: ULTSM> select date(pending_date) as "Date",count(*) from
> volumes
> > where -
> > cont> status='PENDING' group by "Date"
> > ANR2940E The reference 'Date' is an unknown SQL column name.
> >
> > |
> >
> > .........................................................V.....
> > e",count(*) from volumes where status='PENDING' group by
> > "Date"
> >
> > ANS8001I Return code 3.
> >
> >
> >
> >>>>mark.stapleton AT BERBEE DOT COM 11/30/2004 9:09:03 AM >>>
> >
> > From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On
> > Behalf Of David E Ehresman
> >
> >>I'm trying to get a count of tapes pending by date. I'm using:
> >> select date(pending_date),count(*) from volumes where
> >>status='PENDING' -
> >> group by date(pending_date)
> >>but I get the error message:
> >> ANR2904E Unexpected SQL key word token - 'DATE'.
> >>pointing to the date in the group by clause.
> >>
> >>Anyone know how to get a pending count by date?
> >
> >
> > Try
> >
> > select date(pending_date) as "Date",count(*) from volumes where
> > status='PENDING' group by "Date"
> >
> > --
> > Mark Stapleton (stapleton AT berbee DOT com)
> > Berbee Information Networks
> > Office 262.521.5627
___________________________ Disclaimer Notice __________________________
This message and any attachments are confidential and should only be read by
those to whom they are addressed. If you are not the intended recipient, please
contact us, delete the message from your computer and destroy any copies. Any
distribution or copying without our prior permission is prohibited.
Internet communications are not always secure and therefore Powergen Retail
Limited does not accept legal responsibility for this message. The recipient is
responsible for verifying its authenticity before acting on the contents. Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Powergen Retail Limited.
Registered addresses:
Powergen Retail Limited, Westwood Way, Westwood Business Park, Coventry, CV4
8LG.
Registered in England and Wales No: 3407430
Telephone +44 (0) 2476 42 4000
Fax +44 (0) 2476 42 5432
|