Time Crossing Date Ranges

[texxavery]

I am creating two service level reports that calculate downtime for ecommerce applications use best case numbers and am having problems with my second report. My first report has a time period of 7am-7pm, I use currentdate-1 filter on my datex field and a filter of is between Time(7, 00, 00) and Time (19, 00, 00). works great. But the report for 7pm to 7am crosses two date ranges so all the filters I think of don't work. Tried currentdate-1 and currentdate-2, work for the data but I can't filter correctly because of the duplicate times in both days.

7pm-7am report would be like from 7pm on 12/16/02 to 7am on 12/17/02

and

7am-7pm report would be like from 7am on 12/17/02 to 7pm on 12/17/02

 

[synapsevampire]

Are these separate fields (date and time)?

If not, just concatenate the values, as in:

{MyTable.MyDateTime} >= datetime(year(currentdate-2),month(currentdate-2),day(currentdate-2),19,0,0)
and
{MyTable.MyDateTime} <= datetime(year(currentdate-1),month(currentdate-1),day(currentdate-1),7,0,0)

That would return 2 days ago 7PM to Yesterday 7AM.

If they're separate fields, use the same theory:

(
{MyTable.MyDate} >= datetime(year(currentdate-2),month(currentdate-2),day(currentdate-2))
and
{MyTable.MyTime} >= time(19,0,0)
)

and

(
{MyTable.MyDate} <= date(year(currentdate-1),month(currentdate-1),day(currentdate-1))
and
{MyTable.MyTime} <= time(7,0,0)
)

Hope this resolves for you.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com