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

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 <[email protected]> On
>Behalf Of David Bicking
>Sent: Saturday, 18 January, 2020 11:20
>To: SQLite Mailing List <[email protected]>
>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
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users