Re: [sqlite] Can it (should it) be done in SQL?

2020-01-20 Thread Keith Medcalf
On Monday, 20 January, 2020 12:42, David Bicking 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

Re: [sqlite] Can it (should it) be done in SQL?

2020-01-20 Thread David Bicking
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

Re: [sqlite] Can it (should it) be done in SQL?

2020-01-18 Thread Keith Medcalf
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

Re: [sqlite] Can it (should it) be done in SQL?

2020-01-18 Thread Keith Medcalf
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

[sqlite] Can it (should it) be done in SQL?

2020-01-18 Thread David Bicking
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