TeamDesk calculates work time without any coding

I am not sure who should take credit for that - MS SQL server itself, or was it TeamDesk’s natural ability to perform calculations, but TeamDesk almost completely managed to implement "end date and duration calculation" task, accounting working time and holidays, while using only a built-in functionality without any coding.

First of all, special thanks to Kirill Bondar from TeamDesk for implementing a sample application and explaining its functionality in details.

Here is the sample data which is going to be used for End Date and Duration calculations.

Working Times table:


Holiday’s table:


"Calendar" is a key table which is going to be used for all the calculations.

Here is how Kirill explained its structure:

"We've created Calendar table that lists all the dates and linked it to Working Time and Holidays tables. The only data column there is the date; Start and End Time columns draw the information from the Working Time or calculate as 0:00 for holidays. The Hours formula calculates the duration between Start and End Date. This table acts as a uniform source of information for any given date."

Here is how this table’s data looks like:


Let’s review End Date calculation function implementation (Test 1) first.

Here are Kirill’s comments describing this function implementation details.

"End date calculation would cause some difficulties in TeamDesk; the task is similar to calculating the running balance capped by Working Duration specified by the user; we can't do that directly. So we've made one assumption: the working hours for each day are either eight or zero.

Then we calculate number of working hours that fall on start date, calculate the number of working days (with an eight hours assumption) and use Calendar table and Index function to get the End Date from Nth working day. Remaining hours are added to the day's Start Date to determine End Time."

Here is how End Date calculation results look like:


I would especially outline the way how End Date is selected from the calendar.

Here is how Summary Column parameters look like:


Here is a many-to-many relation that it utilizes:


Now let’s move to the Duration calculation function implementation (Test 2).

Here are Kirill’s comments describing this function implementation details:

"Calculation of the working duration seems to be a simple math: one relation between Working Duration to Calendar records that fall between Start and End Dates to calculate the total of working hours between these two dates. Then we have to take in account partial days on Start and End Dates: two more relationships and some math to subtract the duration (if any) between Working Duration's From (Time) and the Start Time from Calendar; and the same for the End Date."

Here is how Working Duration calculation results look like:


As you might guess, the summary column does the main job here as well.

Here are Summary column parameters (this column calculates full working hours between two calendar dates):


…and many-to-many relation used for its calculations:


Now working duration calculation, accounting partial days, is nothing but a simple math and may be easily calculated using TeamDesk’s formula-column:

Excluding To (Hours)	Max([To (End Time)] - Max([To (Time)], [To (Start Time)]), 0)
Excluding From (Hours)	Max(Min([From (End Time)], [From (Time)]) - [From (Start Time)], 0)
Working Duration	[Full Days] - [Excluding From (Hours)] - [Excluding To (Hours)]

Conclusions

As you can see, TeamDesk almost completely managed to implement our task. The system’s only shortcoming was End Date calculation if working hours fluctuate every day.

It is nice to see that there are still some systems capable to implement various business tasks without coding, even though I would mention that configuring Relation and Summary , as well as writing formulas takes some efforts from the user.

Nevertheless, it is the best result so far among the vendors pretending for a "no-coding" technique.