Can it (should it) be done in SQL?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Can it (should it) be done in SQL?

David Bicking-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Keith Medcalf
In reply to this post by David Bicking-2
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 <[hidden email]> On
>Behalf Of David Bicking
>Sent: Saturday, 18 January, 2020 11:20
>To: SQLite Mailing List <[hidden email]>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
 
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Keith Medcalf

On Monday, 20 January, 2020 12:42, David Bicking <[hidden email]> 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 it works.

Yes, it is a recursive CTE.  It calculates the UNK line for the current period, then for each additional period for which there is a goal it calculates the that periods UNK line.  And adjustment only needs to be applied if the previous UNK was less than 0.

>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.

You can fix this by replacing all the sum(amount) with total(amount).  sum returns null if there is no sum, total returns 0.0.  Then cast the result back to an integer at the end.

Do that with this too

>                select amount
>                  from goals
>                where period == unks.period+1

to make sure the return value is 0.0 rather than null (even though there can only be one record, it is the easiest way).

>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.

strftime('%m') is the same as strftime('%m', 'now'), the 'now' is the default if no arguments are provided for the datetime part.  However, what you probably want is strftime('%m', 'now', 'localtime') which will return the current month at your current timezone location, not at the prime meridian.

A CTE that will handle those cases might look like this (with all explicit casts and allowing NULL amounts, and data with no goals, and goals with no data):

with first (period)
  as (
      values (cast(strftime('%m', 'now', 'localtime') as integer))
     ),
     last (period)
  as (
      select max((
                  select max(period)
                    from goals
                 ), (
                   select max(period)
                     from data
                 ))
     ),
     unks (period, type, amount)
  as (
         select first.period,
                'UNK',
                (
                 select total(amount)
                   from goals
                  where period between 1 and first.period
                ) - (
                 select total(amount)
                   from data
                  where period between 1 and first.period
                )
           from first, last
          where first.period <= last.period
      union all
         select unks.period + 1,
                'UNK',
                (
                 select total(amount)
                   from goals
                  where period == unks.period + 1
                ) - (
                 select total(amount)
                   from data
                  where period == unks.period + 1
                ) + min(unks.amount, 0)
           from unks, last
          where unks.period < last.period
     )
   select period,
          type,
          amount
     from data
union all
   select period,
          type,
          cast(max(0, amount) as integer)
    from unks
order by 1, 2;

You do, of course, need an index on Data(period) to avoid all the table scans.

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.



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