Incorrect query to calculate a project's monthly cost

[lukaseder]

trafficstars

Thanks for doing this comparison.

I've stumbled upon this query here: https://github.com/bwajtr/java-persistence-frameworks-comparison/blob/f6b33960c911e19790044f99cd44326ebe0e79c7/src/main/java/com/clevergang/dbtests/repository/impl/jooq/JooqDataRepositoryImpl.java#L227-L263

I'm sure there's an easier way to write this with jOOQ (or with all other solutions, as the query does contain a bit of redundancy), but first, I'd like to point out that your using a many to many relationship between employee and project seems to hint at the query being wrong as an employee who is assigned to several projects will produce more than their actual costs. I suspect a weighted average should be used instead.

Once this has been done, I guess that by using window functions, you will achieve a much more straightforward (and faster!) solution in all frameworks.

I hope this helps