How can I pull data for a week using start time of 7:00AM Sunday 1

[wrburch]

Instead of using the filter LastFullWeek, I need a formula that runs Sunday 7:00AM thru next Sunday 6:59AM. I have separate Date and Time fields with time as a string.
(Created Date) is a DateTime field but only has 12:00AM as the time.
(Created Time) shows as 070000.

Any help is appreciated.

 

[bdreed35]

There is certainly more than 1 way to accomplish this. Here is one approach:

Use this logic in your Record Selection Formula:

(
{table.CreatedDate} in minimum(LastFullWeek) _to_ maximum(LastFullWeek)
OR
({table.CreatedDate} = minimum(LastFullWeek) and {table.CreatedTime} >= "070000")
OR
({table.CreatedDate} = maximum(LastFullWeek) and {table.CreatedTime} < "070000")
)

In the first line, you are pulling the Monday through Friday dates. "_to_" is there by design.
The other lines sets the criteria for the first day of the week and time and the last day of the week and the time.

~Brian

 

[wrburch]

Thanks Brian, this worked great with 1 change. In the third line I had to use Currentdate-2 instead of maximum(lastfullweek) to capture the early morning Sunday times. Thanks for the help!!
Bill B

 

[CoSpringsGuy]

You should rethink that .. what if you run the report tomorrow? Will you have to manually change it to Currentdate-3?
maybe this will work on the third line

{table.CreatedDate} = maximum(LastFullWeek)+1 and {table.CreatedTime} < "070000")

you would need to change the first line accordingly
{table.CreatedDate} in minimum(LastFullWeek) _to_ maximum(LastFullWeek)+1

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[bdreed35]

Agree that there are a couple of flaws in what I posted. Definitely need to use "maximum(LastFullWeek)+1" on the last line to pick up the second Sunday. You can either do the same thing on the first line or just get rid of the second underscore. Here is what your end result might look like:

(
{Table.MoveInDate} in minimum(LastFullWeek) _to maximum(LastFullWeek)
OR
({Table.MoveInDate} = minimum(LastFullWeek) and {Table.CreatedTime} >= "070000")
OR
({Table.MoveInDate} = maximum(LastFullWeek)+1 and {Table.CreatedTime} < "070000")
)

~Brian