Hi Ryan, Thanks for the excellent and comprehensive answer; it seems like CTE's are the way to go for this. I did a quick google and this tutorial was very helpful (for anyone else newly interested in CTE's - https://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/ ) - they're a lot simpler than I was expecting based on the code snippets I've posted here over time. I think I can even get my head around them, though it may take a while to fully grok your response!
Incidentally, you mentioned that this solution didn't have the "fewest iterations" - can I trouble to ask what that would look like? Then I can try working backwards and seeing what the differences are. (Note: having my data type of "DATE" - it worked just fine as you suspected it would.) Many thanks again, Jonathan On 13/05/2016 23:25, R Smith wrote: > > > On 2016/05/13 4:56 PM, Jonathan Moules wrote: >> Hi Simon, >> Sorry, maybe we're crossing wires, but I'm not sure to what you're >> referring. How is defining the type as DATE impeding my attempt to >> get a value of 0 for non-existent rows? >> Ryan's response with a CTE seems to probably be what I want (not had >> the opportunity to test it yet - CTE's are entirely new to me), but >> looking at it quickly now I see that the type is actually NUMERIC >> there rather than my DATE - perhaps that's to what you're referring. > > The CTE will work great and there is some literature about CTEs in > general we could point you to. If you just want a quick fix for your > situation, just use my example, if you want to understand CTE in > general, feel free to ask or google, it's really worth learning since > it can do some real magic for you. > > As to the date type in my CTE example - apologies, I hadn't even > noticed you had it as DATE, I just used NUMERIC since that's what I > always do for dates. You can still use it as type DATE and achieve the > same results with the CTE (I'm 99% sure - haven't tested it). > > Your confusion about what Simon said might be that (I think) perhaps > Simon misunderstood what you found weird about the results in the > original post and tried to explain why you see that weirdness while > you were on about a different weirdness - so you are simply not on the > same page. > > Either way, good luck with the implementation. One note: The CTE > solution will only work after SQLite version 8.3 I think, so if you > are using a very old version, it might not work. > > > Cheers, > Ryan > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
