| Author |
Message |
phubbell
Newbie
Joined: 06 Aug 2012
Location: United States
Online Status: Offline
Posts: 3
|
  
Topic: Current Day or previous day data pull
Posted: 06 Aug 2012 at 5:33am |
|
Hello,
I run a couple of reports daily, but I wanted to know if there was an easier way to pull this with a single formula. The reports are being created using Crystal XI
The report is automated and scheduled to run every 4 hours starting at midnight. What I need to do at the midnight run (actually runs at 5 mins past the hour due to data caputured is based on intervals) is to pull previous day and then currentdate starting at 4am. I know there is a way to do it... i am just not at that level yet to figure it out :-)
Any help would be greatly appreciated
Thanks!!
|
|
Paul Hubbell
Global Services Senior Mgr
Xerox
|
 IP Logged |
|
|
|
Sastry
Moderator
Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
|
Posted: 06 Aug 2012 at 5:42am |
Hi
Filter the data through Record Selection formula like :
{DatabaseDateField} = currentdate or {DatabaseDateField} = currentdate -1
This will fileter data and give you yesterday's and today's data.
|
|
Thanks,
Sastry
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Aug 2012 at 5:58am |
if I understand your design specs I would do this a little differently and include the currenttime to assist in which data to select
(currenttime in time(0,0,0) to time(0,10,0) and datediff('d',{table.datefield},currentdate)=1)
or
(currenttime in time(0,10,1) to time(23,59,59) and datediff('d',{table.datefield},currentdate)=0)
|
| IP Logged |
|
phubbell
Newbie
Joined: 06 Aug 2012
Location: United States
Online Status: Offline
Posts: 3
|
Posted: 06 Aug 2012 at 12:14pm |
Question regarding this... the time is not setup as a time in the database table but based on intervals as I have pasted below. The starttime goes from 0 to 2330. How would that be interpreted in the above example? IF {hsplit.starttime}= 0 THEN "12:00am-12:30am" ELSE
IF {hsplit.starttime}=30 THEN "12:30am-1:00am" ELSE
IF {hsplit.starttime}=100 THEN "1:00am-1:30am" ELSE
IF {hsplit.starttime}=130 THEN "1:30am-2:00am" ELSE
IF {hsplit.starttime}=200 THEN "2:00am-2:30am" ELSE
IF {hsplit.starttime}=230 THEN "2:30am-3:00am" ELSE
IF {hsplit.starttime}=300 THEN "3:00am-3:30am" ELSE
IF {hsplit.starttime}=330 THEN "3:30am-4:00am" ELSE
IF {hsplit.starttime}=400 THEN "4:00am-4:30am" ELSE Thanks again for your assistance :-)
|
|
Paul Hubbell
Global Services Senior Mgr
Xerox
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Aug 2012 at 4:30am |
you would not need to use it at all unless you you also only want to pull the records form the last 4 hours.
Is that the case?
|
| IP Logged |
|
phubbell
Newbie
Joined: 06 Aug 2012
Location: United States
Online Status: Offline
Posts: 3
|
Posted: 07 Aug 2012 at 5:18am |
No sir, and I got it to work.. just had to change/add {acdtable.split} = xxxx and
(currenttime in time(0,0,0) to time(0,05,0) and datediff('d',{hsplit.row_date},currentdate)=1)
or
{acdtable.split} = xxx and
(currenttime in time(0,05,1) to time(23,59,59) and datediff('d',{hsplit.row_date},currentdate)=0) I had to add the 2nd {acdtable.split} = xxx and or else it would read every record in the table. Works perfectly though now.. so thank you very much!!
Edited by phubbell - 07 Aug 2012 at 5:19am
|
|
Paul Hubbell
Global Services Senior Mgr
Xerox
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Aug 2012 at 7:18am |
glad you got it to work.
based on your last post I would caution that you keep an eye on it as it looks a little off to me. I am not exactly sure which data based on time that you are trying to grab. If your xxx is the same in both portions I would change it to
{acdtable.split} = xxxx and
(
(currenttime in time(0,0,0) to time(0,10,0) and datediff('d',{table.datefield},currentdate)=1)
or
(currenttime in time(0,10,1) to time(23,59,59) and datediff('d',{table.datefield},currentdate)=0)
)
If the xxx is different in each part and contingent on the current time it would need further tweaking. |
| IP Logged |
|
|
|
