How do I include a time range in my selection criteria?

[Sange]

Hi,

I'm developing a report which brings back the number of arrests over a date range.

The requestor would also like to be able to choose days of the week within this date range and also a time range. An example of this might be: how many arrests were made between (date range) where the day of week was a Monday or a Thursday and the arrest times were between 2200 & 0600 hrs.

This part of my selection criteria works:
{ARRESTS.ARREST_DATE} = {?Date Range} and
{@ARREST_DOW} = {?Day of Week}

The formula for {@ARREST_DOW} is: WeekdayName (DayOfWeek ({ARRESTS.ARREST_DATE}))

The arrest time is actually a datetime field (only the time is populated though) which makes creating a parameter for this complicated as I would have to create it as a datetime parameter when I only want the time component. As a result, I created a formula, ctime({arrest.time}) and added that to the select statement ie.
{@time} = {?Time_Range}

When I entered 02:00 as my minimum and 06:00 as my maximum time the report appeared to work however when I entered 22:00 as my min and 06:00 as my max I got "The Minimum Cannot Be Larger Than The Maximum".

Any suggestions would be most appreciated.

The version is CR10 and the database is Oracle.

Thank you.

 

[CR85user]

You are pretty much there but you must specify only the time component of the datetime field for comparison with your time range.

If you could show the content of the formula {@time} it would assist.

If for example {ARRESTS.ARREST_DATE} is a datetime field then you would use the following:

//Pull relevant time range matches in criteria
time({ARRESTS.ARREST_DATE}) >= minimum({?Time_Range) and
time({ARRESTS.ARREST_DATE}) <= maximum({?Time_Range)

I would also consider adding a new parameter (if you haven't already) to state what type of search they would like to perform.

//{?Type}
//With a static list of options such as:
'Date & Time Range'
'Date Range & Selected Day Of Week'
'Date Range & Selected Time Window'
'Date Range & Selected Day Of Week & Time Window'

Let this be the first parameter and then they can fill in the appropriate fields to be used in the applicable manner.

//Selection Criteria
if {?Type} = 'Date & Time Range' then
{ARRESTS.ARREST_DATE} in {?Date Range} else

if {?Type} = 'Date Range & Selected Day Of Week' then
{ARRESTS.ARREST_DATE} in {?Date Range} and
{@ARREST_DOW} = {?Day of Week} else

if {?Type} = 'Date Range & Selected Time Window' then
{ARRESTS.ARREST_DATE} in {?Date Range} and
time({ARRESTS.ARREST_DATE}) >= minimum({?Time_Range) and
time({ARRESTS.ARREST_DATE}) <= maximum({?Time_Range) else

if {?Type} = 'Date Range & Selected Day Of Week & Time Window' then
{ARRESTS.ARREST_DATE} in {?Date Range} and
{@ARREST_DOW} = {?Day of Week} and
time({ARRESTS.ARREST_DATE}) >= minimum({?Time_Range) and
time({ARRESTS.ARREST_DATE}) <= maximum({?Time_Range)

You don't neccesarily need the additional formulae for {@Time} and {@Day of week} as they can normally be referenced during selection by formatting the formulas correctly. For grouping pruposes you can also then base your group ranges on the parameter selection for type of report to give a more flexible approach.

'J

 

[CR85user]

I just realised also:

'...When I entered 02:00 as my minimum and 06:00 as my maximum time the report appeared to work however when I entered 22:00 as my min and 06:00 as my max I got "The Minimum Cannot Be Larger Than The Maximum".....'


You may have received this message at the time of entering the parameter values.

Consider using two seperate parameters - one for start time and one for end time. this way you would not need the minimum / maximum check and would be able to use any range across dates.

//Selection Criteria
if {?Type} = 'Date & Time Range' then
{ARRESTS.ARREST_DATE} in {?Date Range} else

if {?Type} = 'Date Range & Selected Day Of Week' then
{ARRESTS.ARREST_DATE} in {?Date Range} and
{@ARREST_DOW} = {?Day of Week} else

if {?Type} = 'Date Range & Selected Time Window' then
{ARRESTS.ARREST_DATE} in {?Date Range} and
time({ARRESTS.ARREST_DATE}) >= {?Start Time} and
time({ARRESTS.ARREST_DATE}) <= {?End Time} else

if {?Type} = 'Date Range & Selected Day Of Week & Time Window' then
{ARRESTS.ARREST_DATE} in {?Date Range} and
{@ARREST_DOW} = {?Day of Week} and
time({ARRESTS.ARREST_DATE}) >= {?Start Time} and
time({ARRESTS.ARREST_DATE}) <= {?End Time}

'J

 

[lbass]

The issue is that you are trying to cross days, and a time range parameter only works within a day. So, if you consider the times to belong to the first day, create two separate time parameters, and then try changing your formula to:

{ARRESTS.ARREST_DATE} = {?Date Range} and
{@ARREST_DOW} = {?Day of Week} and
(
if {?start time} > {?end time} then
dateadd("h",-datediff("h",datetime(currentdate,time(0,0,0)), datetime(currentdate,{?endtime})),{@ctime}) in
dateadd("h",-datediff("h",datetime(currentdate,time(0,0,0)),{?endtime}), datetime(currentdate,{?start time})) to
dateadd("h",-datediff("h",datetime(currentdate,time(0,0,0)),{?endtime}), datetime(currentdate,{?end time})) else
{@ctime} in {?start time} to {?end time}
)

I didn't test this, but I think it should be close.

-LB

 

[Sange]

Hi,

Thanks so much for the detailed responses. Just to clarify the data, there are two datetime fields used in this report: ARRESTS.ARREST_DATE and ARRESTS.ARREST_TIME. Even though they are both "datetime" fields, only the date component is used in ARREST_DATE and only the time component in ARREST_TIME. I use the ARREST_DATE field in my select to bring back records where the arrest date is equal to what has been entered by the users. The ARREST_TIME is used in my formula {@time} ie. ctime(ARREST.ARREST_TIME}

As suggested, I created two parameters: start_time and end_time and attempted both suggestions and found the following:

First I attempted CR85User's suggestion and typed the following into my select statement:
{ARRESTS.ARREST_DATE} in {?Date Range} and
{@OFFENCE_DOW} = {?Day of Week} and
time({ARRESTS.ARREST_TIME}) >= {?Start Time} and
time({ARRESTS.ARREST_TIME}) <= {?End Time}

This only seemed to work where the start time was less than the end time eg. start time = 02:00 and end time = 09:00. However if I entered 22:00 as a start time and 04:00 as an end time I didn't get any records at all.

I also tried adding the additional {?Type} parameter with all the options and either got no records returned or "The Minimum Cannot Be Larger Than The Maximum"

When I pasted Ibass' solution into my select statement I got "A Date Is Required Message" around the the {@arrival time} field.

What I actually entered was:

{ARRESTS.ARREST_DATE} = {?Date Range} and
{@OFFENCE_DOW} = {?Day of Week} and
(if {?Start Time} > {?End Time} then
dateadd("h",-datediff("h",datetime(currentdate,time(0,0,0)), datetime(currentdate,{?End Time})),{@time}) in
dateadd("h",-datediff("h",datetime(currentdate,time(0,0,0)),{?End Time}), datetime(currentdate,{?Start Time})) to
dateadd("h",-datediff("h",datetime(currentdate,time(0,0,0)),{?End Time}), datetime(currentdate,{?End Time})) else
{@time} in {?start time} to {?end time})

Any additonal ideas would be most appreciated as I'm fresh out of them

Thanks

 

[CR85user]

{ARRESTS.ARREST_DATE} in {?Date Range} and
{@OFFENCE_DOW} = {?Day of Week} and
time({ARRESTS.ARREST_TIME}) >= {?Start Time} and
time({ARRESTS.ARREST_TIME}) <= {?End Time}

This only seemed to work where the start time was less than the end time eg. start time = 02:00 and end time = 09:00. However if I entered 22:00 as a start time and 04:00 as an end time I didn't get any records at all.

Did you use two seperate parameters for the {?Start Time} and {?End Time} when using this format?

Also could you post the contents of the {@arrival time} formula.

I'm sure we will get there soon

'J

 

[lbass]

Change my formula to:

ARRESTS.ARREST_DATE} = {?Date Range} and
{@OFFENCE_DOW} = {?Day of Week} and
(if {?Start Time} > {?End Time} then
dateadd("h",-datediff("h",datetime(currentdate,time(0,0,0)), datetime(currentdate,{?End Time})),datetime(currentdate,{@time})) in
dateadd("h",-datediff("h",datetime(currentdate,time(0,0,0)),{?End Time}), datetime(currentdate,{?Start Time})) to
dateadd("h",-datediff("h",datetime(currentdate,time(0,0,0)),{?End Time}), datetime(currentdate,{?End Time})) else
{@time} in {?start time} to {?end time})

Forgot to make one of the times a datetime.

-LB

 

[Sange]

Thank you both CRY85User & lbass for your perseverance! Unfortunately I'm still not up and running with this so I'm hoping you are still happy to assist.

CRY85User:

In response to your question regarding the parameters: I created two "time" parameters and called one {?Start Time} and the other {?End Time}

I just realised that I mistakenly called my "{@time}" formula "{@arrival time}" in my last post. Actually there is no {@arrival time} formula just {@time}, and the contents of this formula is ctime(ARRESTS.ARREST_TIME) - I'm so sorry if this caused any confusion.

lbass:

I pasted your formula and then received a "The remaining text does not appear to be part of the formula" message. I'm pretty sure I didn't miss anything so I thought I'd paste the select statement for you to look at it.

ARRESTS.ARREST_DATE} = {?Date Range} and
{@OFFENCE_DOW} = {?Day of Week} and
if {?Start Time} > {?End Time} then
dateadd("h",-datediff("h",datetime(currentdate,time(0,0,0)), datetime(currentdate,{?End Time})),datetime(currentdate,{@time})) in
dateadd("h",-datediff("h",datetime(currentdate,time(0,0,0)),{?End Time}), datetime(currentdate,{?Start Time})) to
dateadd("h",-datediff("h",datetime(currentdate,time(0,0,0)),{?End Time}), datetime(currentdate,{?End Time})) else
{@time} in {?Start Time} to {?End Time}

Thanks again for your time.
Sandra

 

[lbass]

Try:

if {?Start Time} > {?End Time} then
dateadd("s",-datediff("s",datetime({arrests.arrest_date},time(0,0,0)), datetime({arrests.arrest_date},{?End Time}))-1,datetime({arrests.arrest_date},{@time})) in
dateadd("s",-datediff("s",datetime({arrests.arrest_date},time(0,0,0)),datetime({arrests.arrest_date},{?End Time}))-1, datetime({arrests.arrest_date},{?Start Time})) to
dateadd("s",-datediff("s",datetime({arrests.arrest_date},time(0,0,0)),datetime({arrests.arrest_date},{?End Time}))-1, datetime({arrests.arrest_date},{?End Time}))+1 else
{@time} in {?Start Time} to {?End Time}

-LB

 

[Sange]

Hi,

I just tried the new select and got "A Date Is Required Here" message at the first {arrests.arrest_date}. The ARRESTS.ARREST_DATE is a datetime field however only the date component of it is actually populated with correct data. I think the time is usually displayed as 00:00 eg. if an arrest took place on 01/08/2008 at 14:15 then the ARRESTS.ARREST_DATE would be 01/08/2008 00:00 and the ARRESTS.ARREST_TIME would be something like 30/12/2001 14:15 (the date component is a dodgy date). Not quite sure why they've done it this way but it's definitely a pain!

 

[CR85user]

Using the formula given by LBass try:

if {?Start Time} > {?End Time} then
dateadd("s",-datediff("s",datetime(date({arrests.arrest_date}),time(0,0,0)), datetime((date({arrests.arrest_date}),{?End Time}))-1,datetime(date({arrests.arrest_date}),{@time})) in
dateadd("s",-datediff("s",datetime(date({arrests.arrest_date}),time(0,0,0)),datetime(date({arrests.arrest_date}),{?End Time}))-1, datetime(date({arrests.arrest_date}),{?Start Time})) to
dateadd("s",-datediff("s",datetime(date({arrests.arrest_date}),time(0,0,0)),datetime(date({arrests.arrest_date}),{?End Time}))-1, datetime(date({arrests.arrest_date}),{?End Time}))+1 else
{@time} in {?Start Time} to {?End Time}

I see the issue is due to the format of the fields in the DB.

{arrests.arrest_date}

Is a datetime field with only the date part populated.

{ARRESTS.ARREST_TIME}

Is a datetime field with only the time part populated.

This is what has made it a little more complicated, but the solution LBass has given is more thorough than that which I described so stick with that

'J

 

[Sange]

Hi CR85User,

I entered the above and got "The ) is missing" when trying to save.

This message appears after the final comma in the section I've pasted:

if {?Start Time} > {?End Time} then dateadd("s",-datediff("s",datetime(date({arrests.arrest_date}),time(0,0,0)), datetime((date({arrests.arrest_date}),

The datetime fields are definitely making this more difficult. Unfortunately this is a common thread for this particular db - lucky me

 

[CR85user]

Try:

if {?Start Time} > {?End Time} then
dateadd("s",-datediff("s",datetime(date({arrests.arrest_date}),time(0,0,0)), datetime(date({arrests.arrest_date}),{?End Time}))-1,datetime(date({arrests.arrest_date}),{@time})) in
dateadd("s",-datediff("s",datetime(date({arrests.arrest_date}),time(0,0,0)),datetime(date({arrests.arrest_date}),{?End Time}))-1, datetime(date({arrests.arrest_date}),{?Start Time})) to
dateadd("s",-datediff("s",datetime(date({arrests.arrest_date}),time(0,0,0)),datetime(date({arrests.arrest_date}),{?End Time}))-1, datetime(date({arrests.arrest_date}),{?End Time}))+1 else
{@time} in {?Start Time} to {?End Time}


My Apologies as I appear to have added an extra opening parenthesis.

'J

 

[Sange]

Hi CRY85User

Well the good news is that there were no error messages when I inserted the most recent suggestion. However when I ran the report for Thursday, Friday, Saturday between the hours of 22:00 and 02:00 it only returned arrests that occurred up to midnight on each of these days. Any arrests which may have occurred in between midnight and 2am on these days did not appear.

Getting close though so that's a good sign