Counting number of work assignments on Sundays

[j.s]

Hi all

Given:

* Table "Employees"

- Field: EmployeeID (unique)

etc.

* Table "WorkEntries"

- Field: EmployeeID

- Field: Date

- Field: TimeFrom

- Field: TimeTo

etc

Wanted:

* Table "Employees"

- Field: NrOfSundaysWorked (calculation)

Remarks:

* There can be more than 1 work entry per employee and date. I guess this makes the problem non-standard.

* The wanted information must be available for all employees concurrently (e.g. in list view) and in real-time (i.e. no manual refresh triggering)

Example:

Employee ID 3 worked as follows:

Sun, Nov 6th 2011, 10:00 - 12:00

Sun, Nov 6th 2011, 14:00 - 18:00

Tue, Nov 8th 2011, 14:00 - 18:00

Sun, Nov 13th 2011, 10:00 - 12:00

Sun, Nov 13th 2011, 14:00 - 18:00

Sun, Nov 13th 2011, 20:00 - 22:00

Then Employees::NrOfSundaysWorked for employee ID 3 is supposed to contain value 2, because the employee worked on 2 sundays.

More remarks:

* I know this problem could be got rid of by optimizing the data structure. However, im curious on a solution without this pain.. :-)

* The wanted information must be available for all employees concurrently (e.g. in list view) and in real-time (i.e. no manual refresh triggering)

* I found a solution that seems to work for small amounts of data. However, i cant manage to be proud of it and im also afraid that it wont work with growing data. Last but not least, im just very curious to know if there's a smooth way to get it done.. :-)

Attachments:

* sundays.fp7: provides tables, fields and sample data

* mySundays.fp7: provides the solution that i found so far

Thank you very much for your time and goodwill!

Sincerely, Jürg from Switzerland

sundays.zip

[comment]

Can you explain the purpose here? One would think you'd want the total number of Sundays within a given period (say the month of November 2011, like in your example) - not the total number of Sundays the employee has worked since time immemorial.

[j.s]

Can you explain the purpose here? One would think you'd want the total number of Sundays within a given period (say the month of November 2011, like in your example) - not the total number of Sundays the employee has worked since time immemorial.

Indeed, at the end, i need to know the number of Sundays for a given calendar year (e.g. stored in a global variable). However, i assumed that this constraint will not be crucial for any solution, that's why i decided to omit it here. Was i wrong?

Unbelievable: There is a law in Switzerland that claims additional salary payment for Sunday work, unless there were MORE than 6 Sunday assignments per calendar year. In the latter case, the employee will only get normal pay instead.

Thanks for your concern, comment.. I remember you helping me out of a former crux (and i still owe you my address, so you can send me a bill.. :-) )

[comment]

Well, the thing is that whenever you count or sum, you should ask if you're working with a found set or with a related set. Both are possible - but since relationships ignore found sets, mixing the two can lead to unexpected results.

In this case, you's probably want to find the work entries of a given year and summarize them by employee. To calculate the number of unique Sundays, you'd use a method somewhat similar to yours, but not depending on any relationships:

http://fmforums.com/forum/topic/61158-number-of-employees-from-payroll-report/page__view__findpost__p__289204

[j.s]

Thanks for your fast and solid reply.

Indeed, im still struggling about which way to take: found set or self-join-relationship.

I originally started with a sorted found set and two nested GetSummaries(), a somewhat doubled version of your linked post, since i need a Sunday counter for each employee concurrently. However, in order to be useful to the assignment scheduler guy, all these personal counters must be displayed compactly and embedded into the planning tool and I didnt know how to get this done with the GetSummary() version. Thats why I switched over to the self-joins that allow me to display all the personal counters in e.g. an employee list view or portal.

The time period constraint is easy to maintain adding it as an additional criterium to the relation from Employees to WorkEntries. It is automatically carried on through the following self-join relationship. Yet, your worries are justifiable: Thinking of e.g. reversal, holiday and absence entries which are also stored in WorkEntries, I may easily get stuck with this approach.

Either way, I'm wondering, if there will be rounding errors when summing up over many hundreds or thousands of fractions (cInverseCount)? I admit I was hoping there was a 'nobler' way.. :-)

Thanks again. I'm truly amazed by your service.

Jürg

[comment]

To do the calculation in the Employees table, I would start by generating a list of Sundays in the given year. Then filter this by a list of dates worked and count the resulting values.

This should work with the existing relationship - though it would work faster with a relationship filtered by year (and even faster if filtered by DayOfWeek too). In any case, I don't see a need for self-joins.

---

BTW, I don't see why you need "two nested GetSummaries()" either.

[j.s]

To do the calculation in the Employees table, I would start by generating a list of Sundays in the given year. Then filter this by a list of dates worked and count the resulting values.

You mean on text level using List(), FilterValues(), ValuesCount() & co? --> Wow!! I doubt i would have ever figured that out on my own.. :-)

Thank you very much for your advice, your posts read like detective stories :-)

Jürg from Zürich