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.
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.
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.
David
On Saturday, January 18, 2020, 04:32:52 PM EST, Keith Medcalf
<kmedc...@dessus.com> wrote:
Ooops. Wrong query pasted, should be this one:
with p (period) as (
values (cast(strftime('%m') as integer))
),
unks (period, type, amount) as (
select p.period,
'UNK',
(
select sum(amount)
from goals
where period between 1 and p.period
) - (
select sum(amount)
from data
where period between 1 and p.period
)
from p
where p.period <= (select max(period) from goals)
union all
select period+1,
'UNK',
(
select amount
from goals
where period == unks.period+1
) - (
select sum(amount)
from data
where period == unks.period+1
) + case when unks.amount < 0 then unks.amount else 0 end
from unks
where period < (select max(period) from goals)
)
select period,
type,
amount
from data
union all
select period,
type,
max(0, amount)
from unks
order by 1, 2;
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of David Bicking
>Sent: Saturday, 18 January, 2020 11:20
>To: SQLite Mailing List <sqlite-users@mailinglists.sqlite.org>
>Subject: [sqlite] Can it (should it) be done in SQL?
>
>I suspect the answer is that it is best to do this in the application
>program. However, the platform I want to use is dumb as a brick.
>It basically can call sqlite3_get_table, mildly reformat the data
>and send it to the display.
>
>Anyway, there are two tables
>CREATE TABLE Goals (period integer primary key, amount int);
>CREATE TABLE Data (period int, type text, amount int);
>
>periods above are 1 to 12, and amounts are zero or positive.
>
>INSERT INTO Goals Values (1,10), (2,10), (3,10);
>INSERT INTO Data Values (1,'A',3), (2, 'A', 5) (2, 'B', 6), (3, 'A', 2);
>
>Okay, last time I tried to send a list to the group it came out
>unreadable. Crossing fingers:
>
>PERIOD | TYPE | AMOUNT
>1 | A | 3
>1 | UNK | 7 -- the goal of 10 - data of 3
>2 | A | 5
>2 | B | 6
>2 | UNK | 0 -- goals of 1&2 = 20 - previous lines of 21
> -- with the negative result changed to 0
>3 | A | 2
>3 | UNK | 7 -- goals 1-3 = 30 - previous lines of 23
>
>Hopefully that makes sense. I need to insert a row into the output
>that ends each period at or above the accumulated goals.
>
>A further complication. The "UNK" row only gets inserted for the
>current or future periods. (i.e in two weeks it will be February,
>so the January UNK will be removed and the Feb UNK would
>become 6 (20 - 3 - 11).
>
>The best I got is
>
>select period, type, amt from Data
>UNION
>select period, 'UNK', (select sum(amount) from Goals G
> where g.period <= goals.period)
> -(select sum(amount) from Data D
> where d.period <= goals.period)
>from Goals;
>
>But it doesn't pick up the calculated "UNK" values from the prior
>periods.
>nor does it deal with negatives or not calculating UNK from periods in
>the past.
>
>So can this report be done within a query?
>
>Thanks,
>David
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
