Counting Distinct Records Withing a Time Range

[mjjks]

I need to count distinct date records within a group.
Created formula but it doesn't work. All I get is 1 or 0. Can anyone advise? Thanks in advance.

numberVar Count3AM := 0 ;
stringVar CurrDate  := ''; 
stringVar PrevDate := '';

WhilePrintingRecords;

// extract dates without time part so we can compare
CurrDate := ToText(DatePart("m",{RD_COND_HIST.ENTRY_DTTM}),0)
& "/" & ToText(DatePart("d",{RD_COND_HIST.ENTRY_DTTM}),0)
& "/" & ToText(DatePart("yyyy",{RD_COND_HIST.ENTRY_DTTM}),0);

PrevDate := ToText(DatePart("m",Previous({RD_COND_HIST.ENTRY_DTTM})),0)
& "/" & ToText(DatePart("d",Previous({RD_COND_HIST.ENTRY_DTTM})),0)
& "/" & ToText(DatePart("yyyy",Previous({RD_COND_HIST.ENTRY_DTTM})),0);


IF Hour({RD_COND_HIST.ENTRY_DTTM}) >=2 AND Hour({RD_COND_HIST.ENTRY_DTTM}) <=4 
AND CurrDate <> PrevDate
THEN Count3AM := Count3AM + 1;

Count3AM;

 

[synapsevampire]

You overcomplicated it:

datevar currdate;
datevar prevdate;
// extract dates without time part so we can compare
CurrDate := date({RD_COND_HIST.ENTRY_DTTM});
PrevDate := date(Previous({RD_COND_HIST.ENTRY_DTTM}));
IF Hour({RD_COND_HIST.ENTRY_DTTM}) =3
AND
CurrDate <> PrevDate THEN
Count3AM := Count3AM + 1;
Count3AM

No idea if this formula will provide the right answer though, you aren't asking for archiectural help, which I suspect you should, instead you're stating the way this muct be done and supplying nothing about the data.

-k

-k

 

[mjjks]

Thanks synapsevampire. Last time I touched Crystal was two yrs. ago, so little rusty here.
What I'm trying to do is to count distinct records within a time range for a group. Here's my data in simplified form:

Region   Station   Entry_DateTime
------   -------   ------------------
  1      Stat. A   01/2/2007 03:34:00
 [highlight] 1      Stat. A   01/5/2007 03:12:00
  1      Stat. A   01/5/2007 04:28:00
  1      Stat. A   01/5/2007 04:55:00[/highlight]
  ....
  2      Stat. B   01/6/2007 04:28:00
  2      Stat. B   01/7/2007 03:55:00

I have 4 time periods to count, between 3-5,5-8,9-11,3:30-5:30. I was thinking to create a formula for each period, count distinct records in a group and then derive percentage using count. If you look at 3 highlighted row, for "3-5" formula, those need to be counted only once for the same day. That would yield 1 for '01/2/2007', 1 for '01/5/2007', 1 for '01/6/2007' and 1 for '01/7/2007', totaling to 4.

Hope I explained it clear enough. I tried to get all data using stored procedure, but because for different time periods I get different records, I abandoned it.

Thank you.

 

[lbass]

If you create a formula:

date({RD_COND_HIST.ENTRY_DTTM})

...you can insert a distinctcount on it and get the correct result.

-LB

 

[mjjks]

Ok. I use formula below to filter date and then count distinct values on that formula, but count is not correct. I have a SQL query that I run against database to verify count. Thanks.

if Hour({RD_COND_HIST.ENTRY_DTTM}) >=2 AND Hour({RD_COND_HIST.ENTRY_DTTM}) <=4 then
    Date({RD_COND_HIST.ENTRY_DTTM});

 

[lbass]

Sorry, I missed the time requirements. Are your time periods overlapping or is 3-5 in the AM and 3:30 -5:30 in the PM or something like that? If they are not overlapping, you could insert a group on a time period formula and then simply do a distinct count on the formula I suggested earlier. Otherwise, you could get the correct inserted distinctcount by first creating a formula {@null}, where you open a new formula and save and close without entering anything. Then change your formula to:

if Hour({RD_COND_HIST.ENTRY_DTTM}) >=2 AND
Hour({RD_COND_HIST.ENTRY_DTTM}) <=4 then
Date({RD_COND_HIST.ENTRY_DTTM}) else
date({@null})

-LB