On Monday, 20 January, 2020 12:42, David Bicking <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
