Home » Developer & Programmer » Reports & Discoverer » retrieving time based transactions. (oracle Reports 10g, windows xp)
retrieving time based transactions. [message #469854] Fri, 06 August 2010 02:35 Go to next message
myclassic
Messages: 136
Registered: December 2006
Location: Pakistan
Senior Member
Dear

I have some transactions in my table with date and time.

i want to pass from date, to date and from time , to time as parameter.

when i pass one date and two time parameters, it works fine.

but when i try to pass from date and to date (two date parameters) and two time parameters then it does not work accurately.

e.g. i want to pass 05-Aug-2010 and 06-Aug-2010 and time from 08:00:00 and 14:00:00 then it only retrieves data of both dates having only this time range. however i need to get transaction of 05-aug-2010 from 08:00:00 to 06-aug-2010 14:00:00.

how it is possible.

regards.
Re: retrieving time based transactions. [message #469855 is a reply to message #469854] Fri, 06 August 2010 02:38 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Make it two parameters only. Both of them should contain date and time. For example:
From date = 05-aug-2010 08:00:00
To date   = 06-aug 2010 14:00:00

Because, with 4 parameters, you'd have to concatenate date and time, apply the correct date format mask and then use those values in report's query.
Re: retrieving time based transactions. [message #469862 is a reply to message #469855] Fri, 06 August 2010 02:56 Go to previous messageGo to next message
myclassic
Messages: 136
Registered: December 2006
Location: Pakistan
Senior Member
thanks dear.

now keep in mind the shifting process.

08:00:00 first shift
16:00:00 second shift
21:00:00 third shift.

now we want to retrieve data on the basis of given from and to date as per shifts.

means... from 05-aug-2010 to 10-aug-2010 second shift only.

how will it be catered in this format.

regards.
Re: retrieving time based transactions. [message #469866 is a reply to message #469862] Fri, 06 August 2010 03:14 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You might create another parameter named "shift" and include it into the WHERE clause (using a DECODE, for example). Something like
... and to_number(to_char(date_column, 'hh24')) between decode(:shift, 1, 8, 2, 16)
                                                    and decode(:shift, 1, 16, 2, 21)
Previous Topic: discoverer
Next Topic: .rtf report
Goto Forum:
  


Current Time: Fri Mar 29 00:07:59 CDT 2024