Re: SQL Query Question

select entity as "Node Name      ", cast(sum(bytes/1024/1024) as
decimal(10,3)) as "Total MB", substr(cast(min(start_time) as char(26)),1,19)
as "Date/Time           ", cast(substr(cast(max(end_time)-min(start_time) as
char(20)),3,8) as char(8)) as "Length   " from summary where
start_time>=current_timestamp - 22 hours and activity='BACKUP' group by
entity

Paul D. Seay, Jr.
Technical Specialist
Naptheon Inc.
757-688-8180


-----Original Message-----
From: Michael Moore [mailto:Michael_Moore AT VFC DOT COM]
Sent: Friday, September 13, 2002 2:31 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: SQL Query Question


Well,
Since I got an answer to my previous answer, it helped so much, now I have
an additional question(s).

I am pretty new at using SQL queries, so please bare with me..

Here is what I want to do:

I issue this command:
tsm: DSMSPTECH>run backup_stats CL01NAE0 2002-09-08

Script:
select -
  entity, -
  activity, -
  date(start_time) as "Start Date", -
  time(start_time) as "Start Time", -
  date(end_time) as "End Date", -
  time(end_time) as "End Time", -
  bytes, -
  mediaw  -
   from summary  -
     where date(start_time) = -
       '$2' and -
     activity='BACKUP' -
      and entity='$1' -
       order by entity

to get the following:

    ENTITY: CL01NAE0
  ACTIVITY: BACKUP
Start Date: 2002-09-08
Start Time: 08:14:44
  End Date: 2002-09-08
  End Time: 08:55:23
     BYTES: 4404051979
    MEDIAW: 100

    ENTITY: CL01NAE0
  ACTIVITY: BACKUP
Start Date: 2002-09-08
Start Time: 08:15:35
  End Date: 2002-09-08
  End Time: 08:59:42
     BYTES: 4978671627
    MEDIAW: 95

    ENTITY: CL01NAE0
  ACTIVITY: BACKUP
Start Date: 2002-09-08
Start Time: 09:32:02
  End Date: 2002-09-08
  End Time: 09:36:15
     BYTES: 1867811
    MEDIAW: 184

    ENTITY: CL01NAE0
  ACTIVITY: BACKUP
Start Date: 2002-09-08
Start Time: 09:32:02
  End Date: 2002-09-08
  End Time: 09:36:51
     BYTES: 1867811
    MEDIAW: 230

    ENTITY: CL01NAE0
  ACTIVITY: BACKUP
Start Date: 2002-09-08
Start Time: 18:01:02
  End Date: 2002-09-08
  End Time: 18:30:01
     BYTES: 11347080090
   MEDIAW: 100

As you can see, this node is backed up several times per day.  What I need
is a total number of bytes backed up for the day.  The other information in
the script is just there for testing at this time and another report I am
working on (wasted time on media waits).  In the end, I want a report that
looks similar to:

Entity             Date                 Bytes Backed Up
CL01NAE0    2002-09-08  2024528921481


Thanks for assistance!!


Michael Moore
VF Services Inc.
121 Smith Street
Greensboro,  NC  27420-1488

Voice: 336-332-4423
Fax: 336-332-4544