×

Please authenticate us with your Groundspeak account to get access to a lot more features.

×

To be able to write in the forum you need to authenticate. Meanwhile it's read-only.

Change History

Once a checker request has been fulfilled and the challenge owner (or checker requester) is satisfied the thread should be moved here by a moderator. Please allow some time before moving the thread, to allow feedback from the requester.

Message: Re: 10 Checker requests, mostly similar, but daunting

Forum List Message List
Changed By: thebruce0
Change Date: August 10, 2016 04:55PM

Re: 10 Checker requests, mostly similar, but daunting
This is the SQL I use to produce a readable result in gsak sqlite:
[code]select State, Country, sum(case when Finds='--' then 0 else 1 end) Sums, min(case when Finds='--' then 0 else Finds end) Mag, group_concat(Finds,char(9)) [b]DTFinds[/b]

from (select State, Country, S, (select case when count(*)=0 then '--' else count(*) end from caches where difficulty+terrain=S and State=c1.State and Country=c1.Country) Finds from (select distinct State, Country from Caches) c1
join (with recursive dt(s,n) as (values(2.0,0) union all select s+0.5, n+0.5 from dt where n<8.0) select S from dt) order by Country, State, S)
group by State, Country order by Sums desc, Country, State[/code]

This produce the rows of State and Country, with Sums listing how many D+T's have at least 1 find, Mag showing the lowest D+T count, and DTFinds as a tab-delimited list of D+T counts. So, for this challenge, if there's one row with Mag>=20 then that State qualifies.

I really wish I could be a script writer :P I'd love to see how SQL can be adapted to PGC scripts.

Does this challenge require a new script?

Original Message

Author: thebruce0
Date: August 10, 2016 04:55PM

Re: 10 Checker requests, mostly similar, but daunting
This is the SQL I use to produce a readable result in gsak sqlite:
[code]select State, Country, sum(case when Finds='--' then 0 else 1 end) Sums, min(case when Finds='--' then 0 else Finds end) Mag, group_concat(Finds,char(9)) [b]DTFinds[/b]
from (select State, Country, S, (select case when count(*)=0 then '--' else count(*) end from caches where difficulty+terrain=S and State=c1.State and Country=c1.Country) Finds from (select distinct State, Country from Caches) c1
join (with recursive dt(s,n) as (values(2.0,0) union all select s+0.5, n+0.5 from dt where n<8.0) select S from dt) order by Country, State, S)
group by State, Country order by Sums desc, Country, State[/code]

This produce the rows of State and Country, with Sums listing how many D+T's have at least 1 find, Mag showing the lowest D+T count, and DTFinds as a tab-delimited list of D+T counts. So, for this challenge, if there's one row with Mag>=20 then that State qualifies.

I really wish I could be a script writer :P I'd love to see how SQL can be adapted to PGC scripts.

Does this challenge require a new script?