Report to generate data between certain times

I'm trying to created a report for a facility that needs to see transaction between their afterHours period.

I will be pulling the report at 7:15am and need to compile the data from 23:30 the previous evening thru 7am the following morning. (This is the After Hours shift)

Can any help please,
John-S PretoriusTechnical Service Manager, Mid-AtlanticAsked:
Who is Participating?
 
vastoConnect With a Mentor Commented:
here is another approach:

record selection formula:
{transaction.date } >= DateAdd("n",-30,CurrentDate)
AND
{transaction.date } <= DateAdd("h",7,CurrentDate)

where {transaction.date } is the date field in you transactions table
0
 
vastoCommented:
I guess your question is how to set the date period (prev day 23:30 - current day 7:00) is this correct ? What type of database you are using ?
0
 
John-S PretoriusTechnical Service Manager, Mid-AtlanticAuthor Commented:
Your perception is correct,
The Data will be pulled from a SQL database using Crystal reports 2011
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
vastoCommented:
If you can create stored procedure you can use the 2 dates returned by this SQL:
----------------------------------------------------------
DECLARE @CurrentDate DATETIME
SET @CurrentDate = CONVERT(DATETIME,FLOOR(CONVERT(DECIMAL(19,9),GETDATE())))

SELECT DateAdd(n,30,DateAdd(hh,23,DateAdd(d,-1,@CurrentDate))), DateAdd(hh,7,@CurrentDate)
-----------------------------------------------------------
your select clause will be like :

SELECT<list of columns>
FROM<tables>
WHERE <Date column> BETWEEN
DateAdd(n,30,DateAdd(hh,23,DateAdd(d,-1,@CurrentDate))) and DateAdd(hh,7,@CurrentDate)

is this going to work or you need it just in crystal reports ?
0
 
John-S PretoriusTechnical Service Manager, Mid-AtlanticAuthor Commented:
This looks good, but I would need to run this in Crystal, it's also an automated proccess which I use a batch program 'Easy View' to run the Crystal and email the results in .pdf.

Thank you for your approach though.
0
 
vastoCommented:
This tool ("Easy view") may be able to set the dates for you. Then all you need to do is to define date parameters in the report and set the values using the tool.

You may also use Command as DataSource in crystal reports and then the SQL from the previous post will work.
0
 
John-S PretoriusTechnical Service Manager, Mid-AtlanticAuthor Commented:
Unfortuanetly Easy view cannot specify times, only previous day, weekly, monthly
0
 
vastoCommented:
Well, you can play with that: pass the previous and current day from easy view and add 23:30 and 7:00 in formulas in crystal reports then use the formulas to filter the data.

As I mentioned you can use Command which will be pure SQL and then you don't need even parameters because the date values will be calculated inside the command with the code I sent.
0
 
John-S PretoriusTechnical Service Manager, Mid-AtlanticAuthor Commented:
Vasto I certainly appreciate your input, but strongly believe there is a very simple way to do this in Crystal

Thank you again for your approach.
0
 
John-S PretoriusTechnical Service Manager, Mid-AtlanticAuthor Commented:
Vasto, thats more like it -  Thank you, perfecto!
0
 
John-S PretoriusTechnical Service Manager, Mid-AtlanticAuthor Commented:
Thank you.
0
 
vastoCommented:
You are welcome :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.