LOL! You are absolutely correct. It is almost embarrassing. That is exactly what I asked for and returns the same results as the example I gave. I was using a 2 day time span for testing but what I didn't mention is I wanted to be able to run it for more than 2 days worth of tickets between those hours. The added complexity I was going for allow this query to return a whole weeks work of tickets from 2000 to 0400. I used two days just for getting the query to working with a smaller data set.
Of course there is the extra check for less than midnight and greater than midnight that Fred pointed out. I just took an existing query I had for finding requests between two times on the same day (say 1700 - 2300), changed it to 0 and 24 and doubled it up with an OR. Not ideal but it did what I needed for some quick analysis. Kids, don't follow my example, do nice work instead :) Jason On Thu, Nov 8, 2012 at 6:14 AM, Ken Pritchard <[email protected]> wrote: > ** ** > Hi Jason, > > I'm probably thinking a bit simplisticly, but couldn't you do the > following: > > 'Arrival Time' >= "5/1/2012 08:00:00 PM" AND 'Arrival Time' <= "5/2/2012 > 04:00:00 AM" > > > > ----- Original Message ----- > *From:* Jason Miller <[email protected]> > *Newsgroups:* public.remedy.arsystem.general > *To:* [email protected] > *Sent:* Wednesday, November 07, 2012 5:02 PM > *Subject:* Query for tickets between 8 PM and 4 AM the following day > > ** I am trying to run a quick and dirty search of tickets submitted from 8 > PM and 4 AM the following day. I using this query: > > (((((( 'Arrival Time' - "1/1/1970")+3600) % 86400) >= (20 * 3600)) AND > (((('Arrival Time' - "1/1/1970" )+3600) % 86400) < (24 * 3600))) AND > 'Arrival Time' > "5/1/2012" AND 'Arrival Time' < "5/2/2012") *OR *(((((( > 'Arrival Time' - "1/1/1970")+3600) % 86400) >= (0 * 3600)) AND (((('Arrival > Time' - "1/1/1970" )+3600) % 86400) < (4 * 3600))) AND 'Arrival Time' > > "5/2/2012" AND 'Arrival Time' < "5/3/2012") > > I broke it down into two parts. To the left of the *OR* finds 8 PM to > midnight and to the right of the *OR* finds midnight to 4 AM of the > following day. When I run each half separate it returns the expected > results. When I combine the two with the OR I only get results for the > half (8 to midnight). > > I am sure I am missing something simple. Maybe it is time for some > afternoon coffee? Does anybody see my mistake? > > Thanks, > Jason > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
