SQL: working time task: debriefing

SQL Radio is on the air again! Today we have a very short issue devoted to summing up the results of solving the problem by the participants of the habrosoboobschestva. I promised to play a small prize, so take stock is better than a small, but still article. To add a line to the original article (which I, incidentally, did, too) was obviously not enough, interested persons can skip this summing up. Therefore, adjust your lodgements and pull out ommatofors, we begin!

Debriefing

In those days, souls were bold, the stakes were high, men were real men, women were real women, and furry little animals from Alpha Centauri - real furry little animals from Alpha Centauri. And everyone went towards obscurity, towards terrible dangers, great accomplishments, and the definition of indefinite forms of the verb, which they had never done before.

Douglas Adams, The Hitchhiker's Guide to the Galaxy

The article with the condition of the problem was published almost at noon , the first comments with working requests appeared after a couple of hours with a tail (I apologize for uh ... some freedom in expressions to the tailed inhabitants of the Universe), and the first correctly working solution by the evening! Someone else would have said that here, they say, they are lucky for some - they do nothing at work, they just read Habr, but they solve puzzles ... But we don’t say that! We will say that there are correct admins in nature, in which everything is set up and rebuilt, and does not require manual intervention during normal operation, allowing the kneading of the mind in the freed time! And we also say that the representatives of the Western Curl of the Galaxy showed unprecedented interest in the above problem (according to unverified data, the deviation was more than three sigma)! The total number of individuals who wrote requests turned out to be almost two dozen, the number of comments surely exceeded one hundred. And this (count it up!) Without any political policy, without flames, without trolling and practically without a draft ... Of course, we hoped for a response in the souls of earthmen, it was about their office slavery and the task was formulated, but this resonance ...
')
But, back to the topic. Some details of the decisions that are remembered.

The first working solution from the_unbridled_goose appeared two hours after the publication of the problem. The decision was beautiful: decompose the initial periods into hours, throw out the non-working ones and calculate the sum remaining, but, alas, incomplete. Alas, it did not work out to the end. The first fully working solution appeared at the end of the working day of the agglomeration of the Moscow region of the third planet of the solar system ( XareH 18:17). The approach turned out to be interesting when the working time of the period was defined as follows: we count the total number of days, subtract weekends and holidays, add additional working days and multiply the result by the working day duration in hours ( OrmEugensson ). There were also solutions for MS SQL ( uaggster ), for Oracle ( Mazdik ) with the subsequent transfer to PostgreSQL ( Mazdik , StrangerInTheKy ). There was an option with parsing and automatic formation of a working calendar ( valery1707 ), there were homeworks ( Megacinder ). At least three individuals have registered to publish their decisions (but this is inaccurate, only guesses), and a few more came out of the shadows (they finally wrote their first comments on Habré).

I do not list the rest by name (all decisions are present in the comments to the original article), but thank you very much for your interest and participation. And also a special thank you for the emphasis placed on those who did not succeed in correctly and completely solving the task set the first time, but who found the strength to reach the end. Work on your mistakes and the ability to complete what you started - these are the most valuable qualities!

And finally, the promised winner, who will receive an invitation to PGConf.Russia 2020 , is eranthis (please go to the cashier, you will be surprised by personal messages). Perhaps it was his decision ( link ) that seemed to me the most interesting in terms of compactness and expressiveness.

Thanks again to all participants! Stay tuned! ..

PS Analysis of the problem with the solution, as I promised, will be, but a little later. Already writing, but not enough time.