Jump to content

Counting Days


This topic is 5702 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Newbies

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 $)

Link to comment
Share on other sites

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).

Link to comment
Share on other sites

This topic is 5702 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.