Report writer query help
Date: Wed, 18 Jul 2001 14:00:37 GMT
Message-ID: <3B559600.557F8419_at_canada.com>
[Quoted] I've tried this in sql plus and it seems to grab what i want but in Developer Reports 6i in a query it returns nothing. I'm counting the number of records for a certain time of day but if the time of day goes past midnight, i need to count those that have a time of day before 05:00 the next day. An input parameter, :timeofday, contains the time ranges requested, with one being 18:00-05:00 .. that's the value that is being substringed in the query below. I want it to check the parameter and if the string = "05:00" then return the record with date + 1 day concantenated with the "05:00" otherwise, return the current record plus the concatenated "05:00".
select tsa_rte, count(*) ttl_ontime_w
from tsa_timerec
[Quoted] where to_char(tsa_dep_dte,'HH24:MI') >= SUBSTR(:timeofday,1,5)
and to_char(tsa_dep_dte,'DD-MON-YYYYHH24:MI') <= DECODE(substr(:timeofday,7,5),'05:00',to_char(tsa_dep_dte+1,'DD-MON-YYYY')||SUBSTR(:timeofday,7,5),[Quoted] to_char(tsa_dep_dte,'DD-MON-YYYY')||SUBSTR(:timeofday,7,5)) GROUP BY tsa_rte;
Suggestions?
-- Diane Johnston It's not a smile, it's a lid on a scream!Received on Wed Jul 18 2001 - 16:00:37 CEST