On Monday, 20 January, 2020 12:42, David Bicking <dbic...@yahoo.com> wrote:
> Thanks. I figured the solution would use CTE (this is a CTE, isn't it??) >Unfortunately, they were neither in Sqlite, nor mentioned in any of the >sql stuff I read when I taught myself to do SQL.so it took me a while to >figure out how it works. Yes, it is a recursive CTE. It calculates the UNK line for the current period, then for each additional period for which there is a goal it calculates the that periods UNK line. And adjustment only needs to be applied if the previous UNK was less than 0. >Unfortunately, I extend the goals to cover all 12 months, leavings >actuals for just periods 1 to 3. The result has UNK lines for periods 4 >to 12, with a null for the aount. I am guessing that this is because the >in the unks cte it is subtracting the sum from data, but there is nothing >in data, so it is subtracting a null resulting in a null. You can fix this by replacing all the sum(amount) with total(amount). sum returns null if there is no sum, total returns 0.0. Then cast the result back to an integer at the end. Do that with this too > select amount > from goals > where period == unks.period+1 to make sure the return value is 0.0 rather than null (even though there can only be one record, it is the easiest way). >I was able to put a coalesce around the data sum, and that does work. >The line strftime('%m') seemed very strange. I guess without a time >string, sqlite defaults the the current date and time. the sqlite docs >don't mention this. strftime('%m') is the same as strftime('%m', 'now'), the 'now' is the default if no arguments are provided for the datetime part. However, what you probably want is strftime('%m', 'now', 'localtime') which will return the current month at your current timezone location, not at the prime meridian. A CTE that will handle those cases might look like this (with all explicit casts and allowing NULL amounts, and data with no goals, and goals with no data): with first (period) as ( values (cast(strftime('%m', 'now', 'localtime') as integer)) ), last (period) as ( select max(( select max(period) from goals ), ( select max(period) from data )) ), unks (period, type, amount) as ( select first.period, 'UNK', ( select total(amount) from goals where period between 1 and first.period ) - ( select total(amount) from data where period between 1 and first.period ) from first, last where first.period <= last.period union all select unks.period + 1, 'UNK', ( select total(amount) from goals where period == unks.period + 1 ) - ( select total(amount) from data where period == unks.period + 1 ) + min(unks.amount, 0) from unks, last where unks.period < last.period ) select period, type, amount from data union all select period, type, cast(max(0, amount) as integer) from unks order by 1, 2; You do, of course, need an index on Data(period) to avoid all the table scans. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users