Mayhaps like this?
CREATE TABLE Goals
(
period integer primary key,
amount integer not null
);
CREATE TABLE Data
(
period integer not null references Goals(period),
type text not null,
amount integer not null
);
create index Data_Period on Data (period);
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);
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 sum(amount)
from goals
where period between 1 and unks.period+1
) - (
select sum(amount)
from data
where period between 1 and unks.period+1
) - unks.amount
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
