Trying to get a query to work that's getting a bit complex. The quick
and dirty fix would be to use a SELECT foo1, foo2 INTO TEMP TABLE bar
FROM blah WHERE summat [MORE arcane stuff]. Then query that.

Only the front end doesn't allow writing anything to the linked database.

Three nested SELECTs, but the last one really needs to an INNER JOIN
using MAX and GROUP BY.

Can't get my head round merging it in. Given an existing (temporary)
table it's a piece of piss.

Point me to a noddy guide out there in the aether of the internet with
exceedingly simple examples.

--
Moto Morini 2C/375, Moto Morini 2C/350
Gilera 175 Sport, Husqvarna 401 Svartpilen
1937 Sunbeam Model 14 250 Sports
"Do not adjust your mind, there is a fault in the reality"

On 26/12/2021 12:07, Pete Fisher wrote:
> Trying to get a query to work that's getting a bit complex. The quick
> and dirty fix would be to use a SELECT foo1, foo2 INTO TEMP TABLE bar
> FROM blah WHERE summat [MORE arcane stuff]. Then query that.
>
> Only the front end doesn't allow writing anything to the linked database.
>
> Three nested SELECTs, but the last one really needs to an INNER JOIN
> using MAX and GROUP BY.
>
> Can't get my head round merging it in. Given an existing (temporary)
> table it's a piece of piss.
>
> Point me to a noddy guide out there in the aether of the internet with
> exceedingly simple examples.

You could try https://www.w3schools.com/sql/ - you can experiment with
code there.

On 26/12/2021 12:07, Pete Fisher wrote:
> Trying to get a query to work that's getting a bit complex. The quick
> and dirty fix would be to use a SELECT foo1, foo2 INTO TEMP TABLE bar
> FROM blah WHERE summat [MORE arcane stuff]. Then query that.
>
> Only the front end doesn't allow writing anything to the linked database.
>
> Three nested SELECTs, but the last one really needs to an INNER JOIN
> using MAX and GROUP BY.
>
> Can't get my head round merging it in. Given an existing (temporary)
> table it's a piece of piss.
>
> Point me to a noddy guide out there in the aether of the internet with
> exceedingly simple examples.

Most nested type queries can be flattened but difficult to do
generically so you might have to post your actual example.

One approach might be to union your queries and then restrict on that. E.g.:

SELECT *
FROM (SELECT colA, colB FROM tableA
UNION
SELECT colA, colB FROM tableB)
WHERE colA > 1

--
Bruce Horrocks
FJR1300AS

On 27/12/2021 00:09, Bruce Horrocks wrote:
> On 26/12/2021 12:07, Pete Fisher wrote:
>> Trying to get a query to work that's getting a bit complex. The quick
>> and dirty fix would be to use a SELECT foo1, foo2 INTO TEMP TABLE bar
>> FROM blah WHERE summat [MORE arcane stuff]. Then query that.
>>
>> Only the front end doesn't allow writing anything to the linked database.
>>
>> Three nested SELECTs, but the last one really needs to an INNER JOIN
>> using MAX and GROUP BY.
>>
>> Can't get my head round merging it in. Given an existing (temporary)
>> table it's a piece of piss.
>>
>> Point me to a noddy guide out there in the aether of the internet with
>> exceedingly simple examples.
>
> Most nested type queries can be flattened but difficult to do
> generically so you might have to post your actual example.
>
> One approach might be to union your queries and then restrict on that.
> E.g.:
>
> SELECT *
>   FROM (SELECT colA, colB FROM tableA
>         UNION
>         SELECT colA, colB FROM tableB)
>  WHERE colA > 1
>
>

OK. Note field names and parameters simplified and changed to protect
the innocent. Longer more descriptive names are used in the real thing.

myview unsurprisingly is a view of a table.
In case you hadn't guessed it the back end database is Postgres.

nnncl etc. represent integer values.
'Something' is a descriptive concatenated string version of H and Date.

On a production version it would be constructed on the fly at query run
time. This is just a very quick and dirty proof of concept presently.

SELECT "en","H", "Mrn","Mc","Rfn", "Rln" , "Rft",c_r,rdate, Pos, Rec,
CASE WHEN Pos <=6 THEN CASE WHEN Rec THEN 7-Pos+2 ELSE 7-Pos END ELSE
0 END AS P
FROM(
SELECT "en","H", "Mrn","Mc", "Rfn", "Rln" ,c_r, cr."Rs" AS rdate,
"Rft", Rec, rank() Over (ORDER BY "Rft") AS Pos
FROM
(SELECT "en", myview."H", myview."Mrn",myview."Rfn",
myview."Mc", myview."Rln", myview."Rft", Recs."Rec" AS c_r, Recs."Rs",
CASE WHEN "Rft" < Recs."Rec" THEN true ELSE false END AS Rec,
rank() OVER (PARTITION BY CONCAT(myview."Rfn", ' ',
myview."Rln") ORDER BY "Rft" ASC)
FROM
myview
INNER JOIN Recs ON myview."H" = Recs."H" AND "C" =
'nnncl' AND myview."Mc" <= nnncl AND myview."Mc" > nnncb AND myview.en
= 'Something' AND myview."Rt" = 'T' AND myview."Flag" is null AND
Recs."Rs"< myview."Date" ORDER BY "Rft") cr
WHERE rank = 1) crr

I've probably bollixed things up doing the simplification but you may be
able to get the idea.

The innermost select really needs to retrieve records on the fly from a
table (recs) that is volatile. Essentially to find the most recent value
of Rs that is < myview."Date" , GROUPED BY C and H

So stand alone:

SELECT *

FROM Recs tbl
INNER JOIN (
SELECT "C" AS Cl
,MAX("Rs") AS MxDt
FROM Recs
GROUP BY "C","H"
) sub
ON tbl."C"= sub.Cl
AND tbl."Rs" = sub.MxDt;

The first version works OK when the table only contains one row for each
C,H,Rs

Problem is that master working Recs table has multiple values of Rs for
any C,H combination.

The dirty way that came to mind is to construct a TEMP 'current' Recs
table (but Db is read only).

A back end job to do that automagically from time to time could work -
assuming that can be set up on the server hosting the database.

Will have a look at UNION rather than INNER JOIN.

I confess that I'm not a whizz at it and hate SQL but it keeps the grey
cells firing.

My natural inclination would be to fire up PHP and a database framework
like PDO but that's not an option in this scenario.

--
Moto Morini 2C/375, Moto Morini 2C/350
Gilera 175 Sport, Husqvarna 401 Svartpilen
1937 Sunbeam Model 14 250 Sports
"Do not adjust your mind, there is a fault in the reality"

On 27/12/2021 00:09, Bruce Horrocks wrote:
> On 26/12/2021 12:07, Pete Fisher wrote:
>> Trying to get a query to work that's getting a bit complex. The quick
>> and dirty fix would be to use a SELECT foo1, foo2 INTO TEMP TABLE bar
>> FROM blah WHERE summat [MORE arcane stuff]. Then query that.
>>
>> Only the front end doesn't allow writing anything to the linked database.
>>
>> Three nested SELECTs, but the last one really needs to an INNER JOIN
>> using MAX and GROUP BY.
>>
>> Can't get my head round merging it in. Given an existing (temporary)
>> table it's a piece of piss.
>>
>> Point me to a noddy guide out there in the aether of the internet with
>> exceedingly simple examples.
>
> Most nested type queries can be flattened but difficult to do
> generically so you might have to post your actual example.
>
> One approach might be to union your queries and then restrict on that.
> E.g.:
>
> SELECT *
>   FROM (SELECT colA, colB FROM tableA
>         UNION
>         SELECT colA, colB FROM tableB)
>  WHERE colA > 1
>
>

If there any serious offers of help (it's all in a good cause) then I
can grant access if someone wants to have a closer look.

We are using Metabase (www.metabase.com) to generate the results for
hillclimb events. Ultimately we want to recreate this table
https://www.nhca.co.uk/_files/ugd/595cf5_28a72809ee284c65b9342564b8c51912.pdf
To date these have been done 100% manually using a spreadsheet and it's
a Very Tedious Thing and sometimes prone to errors.

I chose Metabase because a) it's free b) it has an aarch64 docker
implementation available c) seemed to do the job

However it does have this (fairly recently imposed) limitation in not
allowing any writes (eg temporary tables) to the database.

If anyone else is aware of any other dashboard/reporting tool that meets
the criteria above then I'm all eyes. I have just recently seen Apache
Superset that warrants a closer look - anyone use that?

--
/Simon

On 27/12/2021 11:18, siwilson wrote:
> On 27/12/2021 00:09, Bruce Horrocks wrote:
>> On 26/12/2021 12:07, Pete Fisher wrote:
>>> Trying to get a query to work that's getting a bit complex. The quick
>>> and dirty fix would be to use a SELECT foo1, foo2 INTO TEMP TABLE bar
>>> FROM blah WHERE summat [MORE arcane stuff]. Then query that.
>>>
>>> Only the front end doesn't allow writing anything to the linked
>>> database.
>>>
>>> Three nested SELECTs, but the last one really needs to an INNER JOIN
>>> using MAX and GROUP BY.
>>>
>>> Can't get my head round merging it in. Given an existing (temporary)
>>> table it's a piece of piss.
>>>
>>> Point me to a noddy guide out there in the aether of the internet
>>> with exceedingly simple examples.
>>
>> Most nested type queries can be flattened but difficult to do
>> generically so you might have to post your actual example.
>>
>> One approach might be to union your queries and then restrict on that.
>> E.g.:
>>
>> SELECT *
>>    FROM (SELECT colA, colB FROM tableA
>>          UNION
>>          SELECT colA, colB FROM tableB)
>>   WHERE colA > 1
>>
>>
>
> If there any serious offers of help (it's all in a good cause) then I
> can grant access if someone wants to have a closer look.
>
> We are using Metabase (www.metabase.com) to generate the results for
> hillclimb events. Ultimately we want to recreate this table
> https://www.nhca.co.uk/_files/ugd/595cf5_28a72809ee284c65b9342564b8c51912.pdf
> To date these have been done 100% manually using a spreadsheet and it's
> a Very Tedious Thing and sometimes prone to errors.
>
> I chose Metabase because a) it's free b) it has an aarch64 docker
> implementation available c) seemed to do the job
>
> However it does have this (fairly recently imposed) limitation in not
> allowing any writes (eg temporary tables) to the database.
>
> If anyone else is aware of any other dashboard/reporting tool that meets
> the criteria above then I'm all eyes. I have just recently seen Apache
> Superset that warrants a closer look - anyone use that?
>

Seems Superset doesn't support aarch64 "standard_init_linux.go:228: exec
user process caused: exec format error". Boo.

--
/Simon

On 27/12/2021 11:49, siwilson wrote:
> On 27/12/2021 11:18, siwilson wrote:
>> On 27/12/2021 00:09, Bruce Horrocks wrote:
>>> On 26/12/2021 12:07, Pete Fisher wrote:
>>>> Trying to get a query to work that's getting a bit complex. The
>>>> quick and dirty fix would be to use a SELECT foo1, foo2 INTO TEMP
>>>> TABLE bar FROM blah WHERE summat [MORE arcane stuff]. Then query that.
>>>>
>>>> Only the front end doesn't allow writing anything to the linked
>>>> database.
>>>>
>>>> Three nested SELECTs, but the last one really needs to an INNER JOIN
>>>> using MAX and GROUP BY.
>>>>
>>>> Can't get my head round merging it in. Given an existing (temporary)
>>>> table it's a piece of piss.
>>>>
>>>> Point me to a noddy guide out there in the aether of the internet
>>>> with exceedingly simple examples.
>>>
>>> Most nested type queries can be flattened but difficult to do
>>> generically so you might have to post your actual example.
>>>
>>> One approach might be to union your queries and then restrict on
>>> that. E.g.:
>>>
>>> SELECT *
>>>    FROM (SELECT colA, colB FROM tableA
>>>          UNION
>>>          SELECT colA, colB FROM tableB)
>>>   WHERE colA > 1
>>>
>>>
>>
>> If there any serious offers of help (it's all in a good cause) then I
>> can grant access if someone wants to have a closer look.
>>
>> We are using Metabase (www.metabase.com) to generate the results for
>> hillclimb events. Ultimately we want to recreate this table
>> https://www.nhca.co.uk/_files/ugd/595cf5_28a72809ee284c65b9342564b8c51912.pdf
>> To date these have been done 100% manually using a spreadsheet and
>> it's a Very Tedious Thing and sometimes prone to errors.
>>
>> I chose Metabase because a) it's free b) it has an aarch64 docker
>> implementation available c) seemed to do the job
>>
>> However it does have this (fairly recently imposed) limitation in not
>> allowing any writes (eg temporary tables) to the database.
>>
>> If anyone else is aware of any other dashboard/reporting tool that
>> meets the criteria above then I'm all eyes. I have just recently seen
>> Apache Superset that warrants a closer look - anyone use that?
>>
>
> Seems Superset doesn't support aarch64 "standard_init_linux.go:228: exec
> user process caused: exec format error". Boo.
>

Hiss.

Still fiddling with Metabase on my Ubuntu MATE box, but not in docker.
Getting it working as a service was 'interesting'. Mainly because of an
abortive attempt at a docker installation. Linux does my head in with
..conf files scattered seemingly willy nilly.

<Checks> yup still 'up'.

When I find a tuit I will clone your version of the database so I can
play in even more safety than on the NHCA instance.

--
Moto Morini 2C/375, Moto Morini 2C/350
Gilera 175 Sport, Husqvarna 401 Svartpilen
1937 Sunbeam Model 14 250 Sports
"Do not adjust your mind, there is a fault in the reality"

Pete Fisher wrote:

> > The dirty way that came to mind is to construct a TEMP 'current'
Recs
> table (but Db is read only).
>

<snip>

Bit flat out today but could you do it using a WITH?

--
Krusty

On 27/12/2021 13:41, Krusty wrote:
> Pete Fisher wrote:
>
>>> The dirty way that came to mind is to construct a TEMP 'current'
> Recs
>> table (but Db is read only).
>>
>
> <snip>
>
> Bit flat out today but could you do it using a WITH?
>

Dunno.

Took me ages to get it working.

I think I tried it, but the RANK OVER stuff did my head in too.

Will have a go.

--
Moto Morini 2C/375, Moto Morini 2C/350
Gilera 175 Sport, Husqvarna 401 Svartpilen
1937 Sunbeam Model 14 250 Sports
"Do not adjust your mind, there is a fault in the reality"

On 27/12/2021 11:18, siwilson wrote:
> On 27/12/2021 00:09, Bruce Horrocks wrote:
>> On 26/12/2021 12:07, Pete Fisher wrote:
>>> Trying to get a query to work that's getting a bit complex. The quick
>>> and dirty fix would be to use a SELECT foo1, foo2 INTO TEMP TABLE bar
>>> FROM blah WHERE summat [MORE arcane stuff]. Then query that.
>>>
>>> Only the front end doesn't allow writing anything to the linked
>>> database.
>>>
>>> Three nested SELECTs, but the last one really needs to an INNER JOIN
>>> using MAX and GROUP BY.
>>>
>>> Can't get my head round merging it in. Given an existing (temporary)
>>> table it's a piece of piss.
>>>
>>> Point me to a noddy guide out there in the aether of the internet
>>> with exceedingly simple examples.
>>
>> Most nested type queries can be flattened but difficult to do
>> generically so you might have to post your actual example.
>>
>> One approach might be to union your queries and then restrict on that.
>> E.g.:
>>
>> SELECT *
>>    FROM (SELECT colA, colB FROM tableA
>>          UNION
>>          SELECT colA, colB FROM tableB)
>>   WHERE colA > 1
>>
>>
>
> If there any serious offers of help (it's all in a good cause) then I
> can grant access if someone wants to have a closer look.
>
> We are using Metabase (www.metabase.com) to generate the results for
> hillclimb events. Ultimately we want to recreate this table
> https://www.nhca.co.uk/_files/ugd/595cf5_28a72809ee284c65b9342564b8c51912.pdf
> To date these have been done 100% manually using a spreadsheet and it's
> a Very Tedious Thing and sometimes prone to errors.

If the layout of the spreadsheet already matches the PDF then do it in
the spreadsheet. The total scores and best eight is simple enough. The
only bit that can't easily be done with formulas is to re-sort each
class into order as the results are updated. But that just needs one
macro to do them all at once.

If you have the data as comma or tab separated values in a Unix file
then a short Perl or AWK script can easily generate the results as an
HTML table which you can combine with your own CSS style sheet to make
it look how you like.

Email me a copy if you want. The 07.013 address works.

>
> I chose Metabase because a) it's free b) it has an aarch64 docker
> implementation available c) seemed to do the job
>
> However it does have this (fairly recently imposed) limitation in not
> allowing any writes (eg temporary tables) to the database.
>
> If anyone else is aware of any other dashboard/reporting tool that meets
> the criteria above then I'm all eyes. I have just recently seen Apache
> Superset that warrants a closer look - anyone use that?
>

--
Bruce Horrocks
FJR1300AS

On 28/12/2021 16:53, Bruce Horrocks wrote:
> On 27/12/2021 11:18, siwilson wrote:
>> On 27/12/2021 00:09, Bruce Horrocks wrote:
>>> On 26/12/2021 12:07, Pete Fisher wrote:
>>>> Trying to get a query to work that's getting a bit complex. The
>>>> quick and dirty fix would be to use a SELECT foo1, foo2 INTO TEMP
>>>> TABLE bar FROM blah WHERE summat [MORE arcane stuff]. Then query that.
>>>>
>>>> Only the front end doesn't allow writing anything to the linked
>>>> database.
>>>>
>>>> Three nested SELECTs, but the last one really needs to an INNER JOIN
>>>> using MAX and GROUP BY.
>>>>
>>>> Can't get my head round merging it in. Given an existing (temporary)
>>>> table it's a piece of piss.
>>>>
>>>> Point me to a noddy guide out there in the aether of the internet
>>>> with exceedingly simple examples.
>>>
>>> Most nested type queries can be flattened but difficult to do
>>> generically so you might have to post your actual example.
>>>
>>> One approach might be to union your queries and then restrict on
>>> that. E.g.:
>>>
>>> SELECT *
>>>    FROM (SELECT colA, colB FROM tableA
>>>          UNION
>>>          SELECT colA, colB FROM tableB)
>>>   WHERE colA > 1
>>>
>>>
>>
>> If there any serious offers of help (it's all in a good cause) then I
>> can grant access if someone wants to have a closer look.
>>
>> We are using Metabase (www.metabase.com) to generate the results for
>> hillclimb events. Ultimately we want to recreate this table
>> https://www.nhca.co.uk/_files/ugd/595cf5_28a72809ee284c65b9342564b8c51912.pdf
>> To date these have been done 100% manually using a spreadsheet and
>> it's a Very Tedious Thing and sometimes prone to errors.
>
> If the layout of the spreadsheet already matches the PDF then do it in
> the spreadsheet. The total scores and best eight is simple enough. The
> only bit that can't easily be done with formulas is to re-sort each
> class into order as the results are updated. But that just needs one
> macro to do them all at once.
>
> If you have the data as comma or tab separated values in a Unix file
> then a short Perl or AWK script can easily generate the results as an
> HTML table which you can combine with your own CSS style sheet to make
> it look how you like.
>
> Email me a copy if you want. The 07.013 address works.
>

Really want to stay away from Excel - the inhouse timing system uses it
(only for a couple of events) and it's an absolute nightmare trying to
figure out how it works and maintain it.

Pete and I have found a reasonable way forward to get the future data
into a proper (Postgres) db which allows for lots of nice things like
working out people's personal bests and other stuff. The ultimate goal
would be to have the tables update in real time as the results come in
but we're some way away from that. We've loaded all of 2021 events into
it but would now like to automate the results calculations. The example
I linked is just for the main "open" class, there are a number of other
classes that need a similar table.

Web based means people don't have to worry about having the right
software etc. and anyway it's the modern way to do it. I'm hosting it
myself at the moment but I reckon it would be pretty cheap to run on AWS
or similar.

--
/Simon

On 28/12/2021 17:08, siwilson wrote:
> On 28/12/2021 16:53, Bruce Horrocks wrote:
>> On 27/12/2021 11:18, siwilson wrote:
>>> On 27/12/2021 00:09, Bruce Horrocks wrote:
>>>> On 26/12/2021 12:07, Pete Fisher wrote:
>>>>> Trying to get a query to work that's getting a bit complex. The
>>>>> quick and dirty fix would be to use a SELECT foo1, foo2 INTO TEMP
>>>>> TABLE bar FROM blah WHERE summat [MORE arcane stuff]. Then query that.
>>>>>
>>>>> Only the front end doesn't allow writing anything to the linked
>>>>> database.
>>>>>
>>>>> Three nested SELECTs, but the last one really needs to an INNER
>>>>> JOIN using MAX and GROUP BY.
>>>>>
>>>>> Can't get my head round merging it in. Given an existing
>>>>> (temporary) table it's a piece of piss.
>>>>>
>>>>> Point me to a noddy guide out there in the aether of the internet
>>>>> with exceedingly simple examples.
>>>>
>>>> Most nested type queries can be flattened but difficult to do
>>>> generically so you might have to post your actual example.
>>>>
>>>> One approach might be to union your queries and then restrict on
>>>> that. E.g.:
>>>>
>>>> SELECT *
>>>>    FROM (SELECT colA, colB FROM tableA
>>>>          UNION
>>>>          SELECT colA, colB FROM tableB)
>>>>   WHERE colA > 1
>>>>
>>>>
>>>
>>> If there any serious offers of help (it's all in a good cause) then I
>>> can grant access if someone wants to have a closer look.
>>>
>>> We are using Metabase (www.metabase.com) to generate the results for
>>> hillclimb events. Ultimately we want to recreate this table
>>> https://www.nhca.co.uk/_files/ugd/595cf5_28a72809ee284c65b9342564b8c51912.pdf
>>> To date these have been done 100% manually using a spreadsheet and
>>> it's a Very Tedious Thing and sometimes prone to errors.
>>
>> If the layout of the spreadsheet already matches the PDF then do it in
>> the spreadsheet. The total scores and best eight is simple enough. The
>> only bit that can't easily be done with formulas is to re-sort each
>> class into order as the results are updated. But that just needs one
>> macro to do them all at once.
>>
>> If you have the data as comma or tab separated values in a Unix file
>> then a short Perl or AWK script can easily generate the results as an
>> HTML table which you can combine with your own CSS style sheet to make
>> it look how you like.
>>
>> Email me a copy if you want. The 07.013 address works.
>>
>
> Really want to stay away from Excel - the inhouse timing system uses it
> (only for a couple of events) and it's an absolute nightmare trying to
> figure out how it works and maintain it.
>
> Pete and I have found a reasonable way forward to get the future data
> into a proper (Postgres) db which allows for lots of nice things like
> working out people's personal bests and other stuff. The ultimate goal
> would be to have the tables update in real time as the results come in
> but we're some way away from that. We've loaded all of 2021 events into
> it but would now like to automate the results calculations. The example
> I linked is just for the main "open" class, there are a number of other
> classes that need a similar table.
>
> Web based means people don't have to worry about having the right
> software etc. and anyway it's the modern way to do it. I'm hosting it
> myself at the moment but I reckon it would be pretty cheap to run on AWS
> or similar.
>

What Mr Wilson said.

The existing excel system rather grew like topsy. One argument being
that the world and his wife all have excel for free.
The most complex bit of that is reading the serial data output from the
precision timing clocks and parsing in to results, while allowing for
flagged runs etc. 'Knuckles', who started its development freely admits
that the core is totally 'black box' blagged from an excel gurus web
page somewhere.

I knocked up a VB.net (yes shudder - but also free) add on that reads
the excel worksheet to display live results on a large monitor at the
event. Recently added is update of that data to a webpage (Javascript,
Jquery, PHP mashup) by FTP (where a good mobile data signal is
available). That needs streamlining before next season - if I can
remember how it works.

Link for the terminally bored (no live data stream obviously):
https://www.text2kml.co.uk/LiveTiming/

A couple of years ago I spent more time than is healthy working on a
VB.net front end mySQL database version of the timing system. All a
question of UI bells and whistles to cope with the hill control side of
things and feeding the bolt on display really. Problem is that pesky
RS232 data feed though. There is apparently a glitch in the drivers that
mean you can totally lose the link and VB.Net won't be aware. A
'heartbeat' approach works on the excel version, but the timing clock
box only outputs that on one port. Not robust enough yet to try running
that for real. Must revisit it. The clocks guru would really like it in
Java so that he can flog it as OS independent. I took one look at Java
classes for serial communication and bottled it! What it really needs is
proper LAN connectivity twixt clocks and processor. Clocks are arduino
based but could eventually even have its own web server apparently - so
you never know.

The Personal Best thing is already working well on Metabase. One major
challenge was that a lot of the NHCA results are not timed by the NHCA,
so extracting them from other timing provider websites or even csv
kindly extracted from their databases was a bit of a labour of [insert
suitable word].

The same consistent data entry feed problem will apply going forward.
What we most want to avoid though is hand keying and associated errors,
but we should at least be able to use consistent output from the
creaking excel system.

As Knuckles says - we've come a long way from flags, stop watches,
notebook and pencil.

--
Moto Morini 2C/375, Moto Morini 2C/350
Gilera 175 Sport, Husqvarna 401 Svartpilen
1937 Sunbeam Model 14 250 Sports
"Do not adjust your mind, there is a fault in the reality"

On 28/12/2021 17:08, siwilson wrote:
> Really want to stay away from Excel - the inhouse timing system uses it
> (only for a couple of events) and it's an absolute nightmare trying to
> figure out how it works and maintain it.

Fair does staying away from Excel.

> Pete and I have found a reasonable way forward to get the future data
> into a proper (Postgres) db which allows for lots of nice things like
> working out people's personal bests and other stuff. The ultimate goal
> would be to have the tables update in real time as the results come in
> but we're some way away from that. We've loaded all of 2021 events into
> it but would now like to automate the results calculations. The example
> I linked is just for the main "open" class, there are a number of other
> classes that need a similar table.

Can you post the current schema that you are using to store results in,
please, as the column and table names in the sample SQL posted earlier
don't match-up with the column names in the sample PDF.

Also you'll need to explain the scoring system as the example PDF has
points not times.

> Web based means people don't have to worry about having the right
> software etc. and anyway it's the modern way to do it. I'm hosting it
> myself at the moment but I reckon it would be pretty cheap to run on AWS
> or similar.

There are loads of options but do split the generation of the results /
classification tables from the web access.

So, for the first part, use one set of tables to store the raw race
results (the schema I'm asking for above); have a separate set of tables
to hold the scores and classifications; and use database triggers to run
stored procedures that update the classification tables whenever the raw
results tables change.

Then you can use Metabase to query the results / classification tables
with no need for temporary tables or database writes.

Be careful that Metabase doesn't re-generate the webpage every time the
end-user presses refresh in the browser. You don't really want people
pressing refresh...refresh...refresh... while waiting for the next
results to come in causing you to run out of CPU or bandwidth.

--
Bruce Horrocks
FJR1300AS

On 28/12/2021 23:23, Bruce Horrocks wrote:
> On 28/12/2021 17:08, siwilson wrote:
>> Really want to stay away from Excel - the inhouse timing system uses
>> it (only for a couple of events) and it's an absolute nightmare trying
>> to figure out how it works and maintain it.
>
> Fair does staying away from Excel.
>
>> Pete and I have found a reasonable way forward to get the future data
>> into a proper (Postgres) db which allows for lots of nice things like
>> working out people's personal bests and other stuff. The ultimate goal
>> would be to have the tables update in real time as the results come in
>> but we're some way away from that. We've loaded all of 2021 events
>> into it but would now like to automate the results calculations. The
>> example I linked is just for the main "open" class, there are a number
>> of other classes that need a similar table.
>
> Can you post the current schema that you are using to store results in,
> please, as the column and table names in the sample SQL posted earlier
> don't match-up with the column names in the sample PDF.
>

They won't. The PDF is of the Open Championship results, for which as
yet there is no table.

> Also you'll need to explain the scoring system as the example PDF has
> points not times.
>

Simon can amplify, but essentially:

Points for places in each capacity class 6 for 1st down to 1 for 6th - easy.

Points for the Top Ten run off 10 down to 1.

Extra 2 points for breaking a record - eligibility of run for record
setting gets complex. Plus need to check for a new record - hence the
SQL snippet which is a proof of concept for just one class against
current records.

>> Web based means people don't have to worry about having the right
>> software etc. and anyway it's the modern way to do it. I'm hosting it
>> myself at the moment but I reckon it would be pretty cheap to run on
>> AWS or similar.
>
> There are loads of options but do split the generation of the results /
> classification tables from the web access.
>
> So, for the first part, use one set of tables to store the raw race
> results (the schema I'm asking for above); have a separate set of tables
> to hold the scores and classifications; and use database triggers to run
> stored procedures that update the classification tables whenever the raw
> results tables change.
>
> Then you can use Metabase to query the results / classification tables
> with no need for temporary tables or database writes.
>
> Be careful that Metabase doesn't re-generate the webpage every time the
> end-user presses refresh in the browser. You don't really want people
> pressing refresh...refresh...refresh... while waiting for the next
> results to come in causing you to run out of CPU or bandwidth.
>
>

I'm sure much of the back end data manipulation can be done at database
level. Once the results can actually be added to it from consistently
structured timing system exports. Naturally if there was always a stable
internet connection the timing system could theoretically update a
remote database directly. Not going to happen at venues like Hartland
Quay unless we ask the Hotel to let us piggyback on theirs and the
timing caravan is unfortunately sited just a bit too far out of reach of
the wifi in the bar.

I think Simon isn't looking as far ahead as live web results yet.

--
Moto Morini 2C/375, Moto Morini 2C/350
Gilera 175 Sport, Husqvarna 401 Svartpilen
1937 Sunbeam Model 14 250 Sports
"Do not adjust your mind, there is a fault in the reality"

On 28/12/2021 23:23, Bruce Horrocks wrote:
> On 28/12/2021 17:08, siwilson wrote:
>> Really want to stay away from Excel - the inhouse timing system uses
>> it (only for a couple of events) and it's an absolute nightmare trying
>> to figure out how it works and maintain it.
>
> Fair does staying away from Excel.
>
>> Pete and I have found a reasonable way forward to get the future data
>> into a proper (Postgres) db which allows for lots of nice things like
>> working out people's personal bests and other stuff. The ultimate goal
>> would be to have the tables update in real time as the results come in
>> but we're some way away from that. We've loaded all of 2021 events
>> into it but would now like to automate the results calculations. The
>> example I linked is just for the main "open" class, there are a number
>> of other classes that need a similar table.
>
> Can you post the current schema that you are using to store results in,
> please, as the column and table names in the sample SQL posted earlier
> don't match-up with the column names in the sample PDF.
>
> Also you'll need to explain the scoring system as the example PDF has
> points not times.
>
>> Web based means people don't have to worry about having the right
>> software etc. and anyway it's the modern way to do it. I'm hosting it
>> myself at the moment but I reckon it would be pretty cheap to run on
>> AWS or similar.
>
> There are loads of options but do split the generation of the results /
> classification tables from the web access
>
> So, for the first part, use one set of tables to store the raw race
> results (the schema I'm asking for above); have a separate set of tables
> to hold the scores and classifications; and use database triggers to run
> stored procedures that update the classification tables whenever the raw
> results tables change.
>
> Then you can use Metabase to query the results / classification tables
> with no need for temporary tables or database writes.
>
> Be careful that Metabase doesn't re-generate the webpage every time the
> end-user presses refresh in the browser. You don't really want people
> pressing refresh...refresh...refresh... while waiting for the next
> results to come in causing you to run out of CPU or bandwidth.
>

Thanks for this.

You should have email(s).

We are thinking along the same lines. I've installed sqlpad with the
intention of using that for the complex queries - and for the use of the
various class secretaries. The we can use the Metabase frontend for the
simple queries for end users.

As Pete said, the tables for the results output don't yet exist.

--
/Simon