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:[EMAIL PROTECTED] On Behalf Of Prather, Wanda Sent: Wednesday, December 01, 2004 5:40 PM To: [EMAIL PROTECTED] 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:[EMAIL PROTECTED] On Behalf Of Andrew Raibeck Sent: Wednesday, December 01, 2004 10:37 AM To: [EMAIL PROTECTED] 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/[EMAIL PROTECTED] Internet e-mail: [EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> 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. > > >>> [EMAIL PROTECTED] 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. > > > > > > > >>>>[EMAIL PROTECTED] 11/30/2004 9:09:03 AM >>> > > > > From: ADSM: Dist Stor Manager [mailto:[EMAIL PROTECTED] 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 ([EMAIL PROTECTED]) > > 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
