June 26, 2019 at 14:18SQL: Time Attendance Problem: Debriefing
Radio SQL is on the air again! Today we have a very brief issue devoted to summarizing solutions puzzles participants habrosoobschestva. I promised to play a small prize, so summing up is better than a small one, but still an article. To add a line to the original article (which I did, by the way) was clearly not enough; interested parties could skip such a debriefing. So tune your lodgements and pull out the ommatophores, we are starting!
An article with the condition of the problem was published at almost noon, the first comments with working requests appeared a couple of hours later with a tail (I apologize for uh ... some liberties in expressions to the tailed inhabitants of the Universe), and the first correctly working solution by evening! Who else would say that now, they say, some are lucky - they don’t do anything at work, they just read it, and they solve problems ... But we won’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 us to knead the mind in the free time! And let's also say that the representatives of the Western Curl of the Galaxy showed an unprecedented interest in the given task (according to unverified data, the deviation was more than three sigma)! The total number of individuals who wrote queries turned out to be almost two dozen, the number of comments confidently exceeded a hundred. And this (count!) Without any politota, without flames, without trolling and practically without drafts ... We certainly hoped for a response in the souls of earthlings, it was about their office slavery that the task was formulated, but such a 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 task. The solution was beautiful: decompose the initial periods into hours, throw out non-working hours from them and calculate the amount of the remaining ones, but, alas, incomplete. Alas, it did not work to bring it 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). An approach turned out to be interesting when the working hours of a period were defined as follows: we calculate the total number of days, subtract weekends and holidays, add additional working days, and multiply the result by the working day in hours (OrmEugensson ). There were also solutions for MS SQL ( uaggster ), for Oracle ( Mazdik ), followed by a transfer to PostgreSQL ( Mazdik , StrangerInTheKy ). There was an option with parsing and automatic generation of a working calendar ( valery1707 ), there were homework ( Megacinder ). At least three individuals registered to publish their decisions (but this is inaccurate, only guesses), and a few more left the shadows (they finally wrote their first comments on Habré).
I don’t list the others by name (all decisions are in the comments to the original article), but thank you very much for your interest and participation. And also special thanks for the emphasisResponsibility to those who did not succeed in correctly and completely solving the task on the first try, but who found the strength to reach the end. Work on your mistakes and the ability to complete what you started 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, a surprise will be waiting for you in private 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.
Debriefing
In those days, souls were bold, stakes were high, men were real men, women were real women, and furry animals from Alpha Centauri were real furry animals from Alpha Centauri. And everyone went towards the unknown, towards terrible dangers, great accomplishments, and the definition of indefinite forms of the verb, which they had never done before.
Douglas Adams, Hitchhiker's Guide to the Galaxy
An article with the condition of the problem was published at almost noon, the first comments with working requests appeared a couple of hours later with a tail (I apologize for uh ... some liberties in expressions to the tailed inhabitants of the Universe), and the first correctly working solution by evening! Who else would say that now, they say, some are lucky - they don’t do anything at work, they just read it, and they solve problems ... But we won’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 us to knead the mind in the free time! And let's also say that the representatives of the Western Curl of the Galaxy showed an unprecedented interest in the given task (according to unverified data, the deviation was more than three sigma)! The total number of individuals who wrote queries turned out to be almost two dozen, the number of comments confidently exceeded a hundred. And this (count!) Without any politota, without flames, without trolling and practically without drafts ... We certainly hoped for a response in the souls of earthlings, it was about their office slavery that the task was formulated, but such a 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 task. The solution was beautiful: decompose the initial periods into hours, throw out non-working hours from them and calculate the amount of the remaining ones, but, alas, incomplete. Alas, it did not work to bring it 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). An approach turned out to be interesting when the working hours of a period were defined as follows: we calculate the total number of days, subtract weekends and holidays, add additional working days, and multiply the result by the working day in hours (OrmEugensson ). There were also solutions for MS SQL ( uaggster ), for Oracle ( Mazdik ), followed by a transfer to PostgreSQL ( Mazdik , StrangerInTheKy ). There was an option with parsing and automatic generation of a working calendar ( valery1707 ), there were homework ( Megacinder ). At least three individuals registered to publish their decisions (but this is inaccurate, only guesses), and a few more left the shadows (they finally wrote their first comments on Habré).
I don’t list the others by name (all decisions are in the comments to the original article), but thank you very much for your interest and participation. And also special thanks for the emphasis
And finally, the promised winner who will receive an invitation to PGConf.Russia 2020 is eranthis (please go to the cashier, a surprise will be waiting for you in private 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.