Technical Questions: contraining on date & time

contraining on date & time

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20879
Printed Date: 08 May 2024 at 7:10am

Topic: contraining on date & time

Posted By: Paula J
Subject: contraining on date & time
Date Posted: 11 Jul 2014 at 9:42am

I have a report that I need to run 3 times a day m-f at 9:00, 11:00, 13:00

The 9:00 report I want records that have a date/time of the current date and 7:00-8:30 to drop to the report

The 11:00 report I want records that have a date/time of the current date and 8:31-10:30 to drop to the report

The 13:00 report I want records that have a date/time of the current date and 10:31-12:30.

The above will be based on a record field CHNG_DT that contains the date and time.

Example

Acct # Chng_Dt

100 7/11/14 8:40

200 7/11/14 9:03

300 7/11/14 10:31

9:00 report will be blank

11:00 report would contain acct 100 & 200

13:00 report would contain acct 300

Also if the report is blank I want it to run and have a record of the blank report but I do not want to emailed the blank report.

Can anyone tell me how to set up the report to accomplish the 3 runs but records constraining based on current date and the times I have listed?

-------------
Paula J

Replies:

Posted By: DBlank
Date Posted: 11 Jul 2014 at 10:25am

I am not sure how to schedule the reports as I don't use that function but you can handle the record limitations by using a select statement, something like this..

It assumes that you have the same repiort scheduled on the M-F @ the 3 specified times.

datediff('d',currentdate,field)=0
and
(
(currenttime in time(8,59,00) and time(9,1,0)
and time(field) in time(7,00,00) and time(8,30,0))
or
(currenttime in time(10,59,00) and time(11,1,0)
and time(field) in time(8,30,01) and time(10,30,0))
or
(currenttime in time(12,59,00) and time(13,1,0)
and time(field) in time(10,30,01) and time(12,30,0))
)

Posted By: Paula J
Date Posted: 18 Jul 2014 at 10:26am

Thanks but this does not work when I verify the formula it tells me a time range is required and then highlights the time(8,59,00) portion of the first line in the formula.

-------------
Paula J

Posted By: DBlank
Date Posted: 18 Jul 2014 at 10:41am

sorry-mixing in some non-crystal language

datediff('d',currentdate,field)=0
and
(
(currenttime in time(8,59,00) to time(9,1,0)
and time(field) in time(7,00,00) to time(8,30,0))
or
(currenttime in time(10,59,00) to time(11,1,0)
and time(field) in time(8,30,01) to time(10,30,0))
or
(currenttime in time(12,59,00) to time(13,1,0)
and time(field) in time(10,30,01) to time(12,30,0))
)