Wanda's is good for calendar day, Dwight's is good for last 24 hours, but
for last 24 hours I think the following is easier and is more easily
deciphered when the non-SQL gurus go back through your scripts.

where date_time>(current_timestamp-(1 day))

Alex Paschal
Freightliner, LLC
(503) 745-6850 phone/vmail

-----Original Message-----
From: Dwight Cook [mailto:cookde AT US.IBM DOT COM]
Sent: Wednesday, May 21, 2003 10:54 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: SQL Question


Try something along the lines of

      where cast((current_timestamp-date_time)day as decimal(18,0))<1




                      Shannon Bach
                      <SBach AT MGE DOT COM>          To:
ADSM-L AT VM.MARIST DOT EDU
                      Sent by: "ADSM:          cc:
                      Dist Stor                Subject:  SQL Question
                      Manager"
                      <[email protected]
                      .EDU>


                      05/21/2003 11:49
                      AM
                      Please respond to
                      "ADSM: Dist Stor
                      Manager"






I have a SQL question I have spent hours trying to figure out but I can't
seem to get it.  What I am trying to do is discover how many scratch tapes
we mount in 24 hours.  Because we are an MVS OS/390 the information is a
little harder to get.  I've tried several different ways but always get
some sort of syntax.  Below I will list my examples, maybe someone can lead
me in the right direction.
What I can do is;
tsm: SERVER>select count(*) as SCR from actlog where msgno=1340
        SCR
-----------
       1427

(I am going by the MSGNO in Actlog
Activity Log
------------
Date and Time       Message
----------------------------------------------------------------------------
-------------

05/21/2003 07:06:27 ANR1340I Scratch volume 100024 is now defined in
storage pool OFFSITE01.
What I can't figure out is how to get scratch volumes defined only within
past 24 hours

1. select count(*) as TMS from actlog where msgno=1340 and BEGIND=TODAY-1
2. select count(*) as TMS from actlog where msgno=1340 and
date_time=current_date-1
3.  select count(*) as TMS from actlog where msgno=1340 and
(dayofyear)timestamp=current_date-1
None of these work.  I am probably going about this the wrong way.  I did
try to get the info out of the Summary but couldn't get that to fly either.
1.tsm: SERVER>select count(*) as SCR from summary where activity='TAPE
MOUNT' and (current_art_time)hours<24
ANR2916E The SQL data types INTERVAL HOUR(9) and INTEGER are incompatible
for operator '<'.
Any ideas?

Thanks,
Shannon
Madison Gas & Electric Co.
e-mail sbach AT mge DOT com