Date time groupings

[Jazztpt]

Hi - I need to create groups based on time.
Crystal 8.5 & SQL 2000

The report needs to show all transactions between set hours:
i.e.
9am-11.59am
12-3.59pm
4pm-10pm

My problem is the date and time are in the field together and I also need to choose a range of dates.

Can I set up a parameter to select the dates and then a grouping formula for the times? On the time grouping formula, how would I ignore the date part of the field?

Hope this is understandable , any help would be appreciated.
Jazztpt

 

[synapsevampire]

Sure, create a DATE parameter and use that against the datetime field. If you have difficulties getting it to pass to the database let us know.

As for the time formula, create a formula of:

if hour({table.datetime}) >= 9
and
hour({table.datetime}) < 12
then
9
else
if hour({table.datetime}) >= 12
and
hour({table.datetime}) < 16
then
12
else
if hour({table.datetime}) >= 16
and
hour({table.datetime}) <= 22
then
16

Group on this field, and then you'll probably want a display formula to show the hours, so use the same logic, as in:

if hour({table.datetime}) >= 9
and
hour({table.datetime}) < 12
then
"9am-11.59am"
else
...you get the idea...

The reason you wouldn't just use the display formula for grouping is that it is a text field and "12" would sort before the "9".

-k

 

[Jazztpt]

Hey thanks for the quick reply, I'll give that a go first thing tomorrow and let you know how I got on. regards Jazztpt.

 

[synapsevampire]

It dawned on me that you'll also need a final if in the formula for all other times, or you need to filter out other times in the record selection, which would prove best.

if hour({table.datetime}) >= 9
and
hour({table.datetime}) < 12
then
9
else
if hour({table.datetime}) >= 12
and
hour({table.datetime}) < 16
then
12
else
if hour({table.datetime}) >= 16
and
hour({table.datetime}) <= 22
then
16
else
999999

-k

 

[Jazztpt]

Thanks Again - I was tied up all day today so didn't get to try this. Will let you know how I get on. Really appreciate your help.
Jazztpt

 

[Jazztpt]

Hi - finally found some time to try this:

if hour {vwTransactDetail.TransDate} >= 9 and
hour {vwTransactDetail.TransDate} < 12 then 9 else
if hour {vwTransactDetail.TransDate} >= 12 and
hour {vwTransactDetail.TransDate} < 16 then 12 else
if hour {vwTransactDetail.TransDate} >= 16 and
hour {vwTransactDetail.TransDate} < 22 then 16 else 9999

I am getting an error
"The key word THEN is missing" - I can't see where that would be - I know Crystal 8.5 is sometimes a bit crptic with its error messages. Any ideas what could be wrong?
Once again much appreciate the help.
Jazztpt

 

[synapsevampire]

Try:

if (hour{vwTransactDetail.TransDate} >= 9 and
hour {vwTransactDetail.TransDate} < 12) then
9
else
if (hour{vwTransactDetail.TransDate} >= 12 and
hour {vwTransactDetail.TransDate} < 16) then
12
else
if (hour{vwTransactDetail.TransDate} >= 16 and
hour {vwTransactDetail.TransDate} < 22) then
16
else
9999

Not sure why it's erroring, but make sure that you have Crystal Syntax specified, not Basic.

-k

 

[lbass]

Is that literally your code? Because you have left out all the parens around the fields. Should be:

hour ({vwTransactDetail.TransDate})

...in every case.

-LB

 

[synapsevampire]

Ahhh, good catch, LB.

-k

 

[Jazztpt]

Thanks for the replies - I did try with parens like this:
hour ({vwTransactDetail.TransDate}) etc
but not like this:
if (hour{vwTransactDetail.TransDate} >= 9 and
hour {vwTransactDetail.TransDate} < 12) then

I'll give this another go tomorrow and post back how I got on.

Cheers
Jazztpt

 

[synapsevampire]

If you do have difficulties, post what you'd tried.

Note my formula was correctly formed in the first go around, unfortunately I didn't look closely and jujst copied and pasted from your formula with the outer parens, but your eliminating the parens would cause an error.

The original formula, with parens around the hour using Crystal syntax was fine.

-k

 

[Jazztpt]

Got this all working - thanks again for all help.