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

 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 <[email protected]> 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 <[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
  
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users