Counting Days

[fmsavey]

I have records in a table and one of the fields is a date. I want to be able to count the number of days for the records that I have selected. For example, in the 6 records below there are 3 days. Is there a calculation that will determine this or do I have to loop through all the records to figure this out. As simple as it seems I cannot come up with a solution. Thanks for your help!

1/12/07

1/12/07

1/12/07

1/15/07

1/16/07

1/16/07

[Genx]

Your example isn't clear enough.. What if it's a different month?

[fmsavey]

I don't care if the records I have choosen have dates with different months or years. If I have 25 records for 1/26/2007 and 1 record for 11/5/2006 I want to calculate that these records comprise 2 different days.

A little more to chew on. Thanks for your help!

[mr_vodka]

I am still a little unclear on what you are trying to achieve but if want a count of how many other records share the same date that your current record is on, you can create a self join relationship based off the date field. Then use a count in a calc field.

Count (SelfJoin::Datefield)

[fmsavey]

Sorry, let me fill in some more. The records I am referring to are employee time records. Amoung other things the record holds information about the employee, the client and job, the date worked, the number of hours worked and where the work was performed or juisdiction code. I was trying to create a report that showed the employee (subsummary by employee id) and then all of their timerecords (body) for the found set of records. In the subsummary by employee id I wanted to show the count of days like in the example that follows:

John Smith 2 days

1/12/2007 0.5 hrs company x

1/12/2007 3.0 hrs company y

1/12/2007 5.0 hrs company z

2/9/2007 9.0 hrs company b

The intended purpose of this report is to be able find records for a jurisdiction code and be able to tell who worked there on what clients for how many days.

Again, thanks for any help you can give.

[mr_vodka]

As my previous post stated, create a selfjoin relationship of the child table, but instead of keying only on the Date field, also key it on the employee id field as well. Keying on both these field and still using a calc field of Count (SelfJoin::Datefield) will return the count of all unique dates for each employee id.

[fmsavey]

Unfortunately this counts all the records that match the relationship including those records not in the current found set, so this will not work. I believe the desired results can only be achieved by a looping script or a recursive custom function which I am trying to educate myself about now.

Thanks for trying.

[comment]

I believe the desired results can only be achieved by a looping script or a recursive custom function

Not really - see here.

[fmsavey]

What I was thinking when I said using a script was...when I run the report on the current found set of records I would sort on timekeeper id and work date and then loop through all the records keeping track of dates while counting them for each timekeeper.

[comment]

I didn't mean to say a script wouldn't work - only that is was not the only (and probably also not the best) option.

[fmsavey]

I agree with you completely but until I figure out a better way I am using the attached script to count the days.

timecard_diary.pdf

[comment]

I thought I have suggested a better way in the thread I have linked to.

I haven't examined your script very closely, but it seems it could benefit from the technique described here.

[ovrdrvn]

I'm working on something similar in terms of counting. These steps aren't working for me. I need to count the number of "unique" (or distinct in sql terms) IDs in the parent table that are associated with the found set in the child table. Patients is the parent table and visits is the child table. A found set over a time period of 42 visits might only mean 15 patients. My report in the child table needs to show how many unique patients were visited over the period (so the funders/govn't can appropriate the $)

[comment]

These steps aren't working for me.

I am not sure which steps you are referring to. This problem is somewhat easier to handle in a printed report, because sub-summaries can be used. Counting a sub-summarized group is very easy in version 8.5 (and, I believe, in version 8 too).

Even easier way to handle your example would be to find the visits in the required date range, then go to related record in Patients, showing only related records (match found set).