?
Solved

Report to generate data between certain times

Posted on 2011-10-19
12
Medium Priority
?
189 Views
Last Modified: 2012-05-12
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,
0
Comment
Question by:John-S Pretorius
  • 6
  • 6
12 Comments
 
LVL 18

Expert Comment

by:vasto
ID: 36995285
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
 

Author Comment

by:John-S Pretorius
ID: 36995370
Your perception is correct,
The Data will be pulled from a SQL database using Crystal reports 2011
0
 
LVL 18

Expert Comment

by:vasto
ID: 36995492
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
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 

Author Comment

by:John-S Pretorius
ID: 36995527
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
 
LVL 18

Expert Comment

by:vasto
ID: 36995698
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
 

Author Comment

by:John-S Pretorius
ID: 36995713
Unfortuanetly Easy view cannot specify times, only previous day, weekly, monthly
0
 
LVL 18

Expert Comment

by:vasto
ID: 36995842
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
 

Author Comment

by:John-S Pretorius
ID: 36995935
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
 
LVL 18

Accepted Solution

by:
vasto earned 1000 total points
ID: 36996558
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
 

Author Comment

by:John-S Pretorius
ID: 36996597
Vasto, thats more like it -  Thank you, perfecto!
0
 

Author Closing Comment

by:John-S Pretorius
ID: 36996601
Thank you.
0
 
LVL 18

Expert Comment

by:vasto
ID: 36996643
You are welcome :)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month13 days, 14 hours left to enroll

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question