Selecting records between specific hours in a date range 1

[INTP]

It seems like I may have asked this same question a few years ago... but I can't find it. Date/Time issues always get the best of me. Anyway...

I need to find out how many records are processed at our call center between the hours of midnight and 8am by month for the last three months (April-June).

I tried the following, but as you probably guessed, it was not correct:

IF
{SALES_DATA.REC_TM}
in DateTime (2007, 04, 01, 00, 00, 00)
to DateTime (2007, 06, 30, 08, 00, 00)
THEN
COUNT ({SALES_DATA.REC_ID})

The rec_tm field is the time that the record would have been entered into the software and the rec_id field is the transaction number for the sale.

What am I doing wrong? Thanks

 

[Madawc]

I'd do it as two separate formula fields:

DateDiff("m", {SALES_DATA.REC_TM}, currentdate) <= 3

DatePart("h", {SALES_DATA.REC_TM}) <= 8

These will come out as boolians, displaying True or False if put in the detail line. Do this to check the test is OK. Then use them in the count, formula values in a running total.

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[INTP]

Thanks Madawc,

I am using CR 9.2 with XP Pro.

I used your suggested formulas and tested them by placing them in the details. They all returned a true value.

Now, if I understand you correctly, I need to use the count formula against these formulas? so...

COUNT @({formula 1))

and do the same with the 2nd formula?

I tried that and it appears to have given me a ridiculous number. I know anecdotally that We probably had roughly 10,000 sales during April - June between midnight and 8am... but this is returning something like 45,000.

What could I be doing wrong?

 

[lbass]

Use a formula like:

if {table.datetime} in
dateserial(year(currentdate),month(currentdate)-3,1) to
dateserial(year(currentdate),month(currentdate),1)-1 and
time({table.datetime}) in time(0,0,0) to
time(7,59,59) then 1

Right click on this formula (placed in the detail section) and insert a summary (sum, NOT count). If the number is inflated, it means you have row inflation, and you would have to adjust for that by using a running total or a different version of this formula. Let us know if that is the case.

-LB

 

[INTP]

Yes- that worked perfectly- thanks!