Re: SQL Question
2003-05-21 14:26:40
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
|
|
|