[Resolved] 10 Checker requests, mostly similar, but daunting August 09, 2016 07:31PM |
Registered: 7 years ago Posts: 53 |
select group_concat(Finds,char(9)) Finds from (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, (select count(*) from caches where difficulty+terrain=s) Finds from dt order by s)Result: 1 column tab-delimited list of the 17 D+T sum totals. min(Finds) would determine qualification
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)) DTFinds from (select State, Country, S, cast(sum(Finds) as int) Finds from ( select County, State, Country, S, (select case when count(*)=0 then '--' else count(*) end from caches where difficulty+terrain=S and County=c1.County and State=c1.State and Country=c1.Country) Finds from (select distinct County, State, Country from Caches where County IN ('Waterloo','Wellington','Perth','Oxford','Brant','Brantford') and State='Ontario') 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, S) group by State, Country order by Sums desc, Country, StateResult: Tab-delimited list of D+T sum totals. Column "Sums" shows how many spots have at least 1 find (17 is a complete set). Column "Mag" shows the minimum count in a D+T spot (0 implies an incomplete set having at least one spot with 0 finds)
select Y, sum(case when Caches='--' then 0 else 1 end) Sums, min(case when Caches='--' then 0 else Caches end) Mag, group_concat(Caches,char(9)) DTFinds from (SELECT Y, S, CASE when Num=0 then '--' else Num END as Caches FROM (with recursive ys(y,s,n) as (values(2000,2.0,1) union all select cast((n)/17 as int)+2000,2.0+((n)%17)*0.5,n+1 from ys where n<=18*(strftime('%Y',date('now'))-2000)) select (select count(*) as Num FROM caches c WHERE Y = 0+strftime('%Y',PlacedDate) and Difficulty+Terrain = S) as Num, Y, S from ys) order by Y, S) group by YResult: Sorted by year, summary of how many D+T spots have at least one find (Sums), the minimum count across the set (Mag), and the tab-delimited list of grouped D+T totals (DTFinds).
select M, D, Caches Finds from (SELECT M, D, Num as Caches FROM (with recursive yd(m,d,n) as (values('01','01',1) union all select strftime('%m',2451544.5+n),strftime('%d',2451544.5+n),n+1 from yd where n<366) select (select count(*) as Num FROM caches c WHERE strftime('%m%d',PlacedDate)=M||D and (Difficulty+Terrain>=3.5)) as Num, M, D from yd) order by M, D)Result: List of all MDs (366 rows) with their count of caches having D+T >=3.5
Re: 10 Checker requests, mostly similar, but daunting August 09, 2016 08:50PM |
Moderator Registered: 8 years ago Posts: 21,449 |
Re: 10 Checker requests, mostly similar, but daunting August 09, 2016 10:45PM |
Registered: 7 years ago Posts: 53 |
Re: 10 Checker requests, mostly similar, but daunting August 09, 2016 10:53PM |
Moderator Registered: 8 years ago Posts: 21,449 |
Re: 10 Checker requests, mostly similar, but daunting August 09, 2016 11:18PM |
Registered: 7 years ago Posts: 53 |
Re: 10 Checker requests, mostly similar, but daunting August 09, 2016 09:44PM |
Moderator Registered: 8 years ago Posts: 21,449 |
Re: 10 Checker requests, mostly similar, but daunting August 09, 2016 11:19PM |
Registered: 7 years ago Posts: 53 |
Re: 10 Checker requests, mostly similar, but daunting August 09, 2016 11:41PM |
Registered: 8 years ago Posts: 21,449 |
Re: 10 Checker requests, mostly similar, but daunting August 09, 2016 10:50PM |
Moderator Registered: 8 years ago Posts: 21,449 |
Re: 10 Checker requests, mostly similar, but daunting August 09, 2016 11:21PM |
Registered: 7 years ago Posts: 53 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 01:46PM |
Moderator Registered: 8 years ago Posts: 21,449 |
Re: 10 Checker requests, mostly similar, but daunting August 09, 2016 11:34PM |
Registered: 7 years ago Posts: 53 |
Re: 10 Checker requests, mostly similar, but daunting August 09, 2016 11:44PM |
Moderator Registered: 8 years ago Posts: 21,449 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 11:47AM |
Moderator Registered: 8 years ago Posts: 659 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 12:46PM |
Moderator Registered: 8 years ago Posts: 21,449 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 02:35PM |
Registered: 8 years ago Posts: 21,449 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 03:10PM |
Registered: 7 years ago Posts: 53 |
Re: 10 Checker requests, mostly similar, but daunting August 09, 2016 11:36PM |
Moderator Registered: 8 years ago Posts: 21,449 |
Re: 10 Checker requests, mostly similar, but daunting August 09, 2016 11:44PM |
Registered: 7 years ago Posts: 53 |
Re: 10 Checker requests, mostly similar, but daunting August 09, 2016 11:46PM |
Moderator Registered: 8 years ago Posts: 21,449 |
Re: 10 Checker requests, mostly similar, but daunting August 09, 2016 11:48PM |
Registered: 7 years ago Posts: 53 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 12:12AM |
Moderator Registered: 8 years ago Posts: 21,449 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 12:11AM |
Registered: 7 years ago Posts: 53 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 12:18AM |
Moderator Registered: 8 years ago Posts: 21,449 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 12:13AM |
Registered: 7 years ago Posts: 53 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 02:03PM |
Moderator Registered: 8 years ago Posts: 21,449 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 03:12PM |
Registered: 7 years ago Posts: 53 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 06:42PM |
Registered: 8 years ago Posts: 659 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 06:55PM |
Registered: 7 years ago Posts: 53 |
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)) DTFinds 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
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 07:51PM |
Moderator Registered: 8 years ago Posts: 659 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 08:29PM |
Registered: 7 years ago Posts: 53 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 08:52PM |
Registered: 8 years ago Posts: 659 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 08:39PM |
Moderator Registered: 8 years ago Posts: 659 |
Re: 10 Checker requests, mostly similar, but daunting August 10, 2016 09:14PM |
Registered: 7 years ago Posts: 53 |
Re: 10 Checker requests, mostly similar, but daunting September 06, 2016 05:31AM |
Registered: 7 years ago Posts: 53 |
Re: 10 Checker requests, mostly similar, but daunting August 20, 2016 04:55AM |
Registered: 7 years ago Posts: 59 |
Re: 10 Checker requests, mostly similar, but daunting August 20, 2016 05:14AM |
Registered: 7 years ago Posts: 147 |