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
