Re: Report writer query help

"Diane Johnston" <tvordlj_at_canada.com> schrieb im Newsbeitrag news:3B559600.557F8419_at_canada.com...
> 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
> 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),
> 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!

Hello,

I had Problems with string compare too. So I modified the Querry's of Date compare:
Exemp. :
> where to_char(tsa_dep_dte,'HH24:MI') >= SUBSTR(:timeofday,1,5)
where trunc(tsa_dep_dte,'HH') >= trunc(to_date(:timeofday,'DD-MON-YYYY HH24:MI'),'HH')

Regards, Anette Received on Thu Jul 19 2001 - 10:51:20 CEST