Calling all hardcore SQL experts! A challenge

[Spacecadet]

Thanks for the advice folks. Using a sequence table to join on to expand the date range I managed to get the whole calculation done in a single SELECT (look no loops required!!!).

Lovely stuff this SQLite!



PS. The permies haven't got a clue how this works. Which is nice.

Well done!

Although I would have used an outer join rather than the NOT EXISTS but given the size of the data sets you're querying I doubt that there would be that big an impact on performance

[lilelvis2000]

Well done!

Although I would have used an outer join rather than the NOT EXISTS but given the size of the data sets you're querying I doubt that there would be that big an impact on performance

That depends on the plan - which would depend on the statistics and indexes and all sorts.....

[NickFitz]

Looping?

SQL was originally defined as a declarative language; declarative languages don't have loops. Loops lead to side effects, which are a Bad Thing.

It always annoys me when people refer to XSLT as having loops (which it doesn't).