Implementation
- B is the Winner Table: It holds the region and a numeric as ID. Short name is left empty for privacy protection in this Demo
- A is the table of all contests with dates
- X,C are the arrays of prizes. They are projected as SQL tables. When assigned they refer to 1 winner
User guide
Start the demo in Docker from IRIS console or in Webterminal
USER>do ##class(dc.rcc.Main).Menue()You jump on top of fresh page
Welcome to OEX Contest Statistics
Select Contests, Prizes, Winners, Assign, Statistics, eXit (C,P,W,A,S,X) :Contests, Prizes, Winners have the same maintenance functions and show the
data status before and after processing.
Contest List, Edit, New, Delete, eXit (L,E,N,D,X) :
Prizes List, Edit, New, Delete, eXit (L,E,N,D,X) :
Winners List, Edit, New, Delete, eXit (L,E,N,D,X) :List, Edit, Delete ask for additional IDs of the records processed.
A typical EDIT sequence for the actual Contest:
Select Contests, Prizes, Winners, Assign, Statistics, eXit (C,P,W,A,S,X) :c
Contest List, Edit, New, Delete, eXit (L,E,N,D,X) :e
Contest ID :24
ID Start_Date End_Date Title
24 2022-05-09 2022-05-29 InterSystems Grand Prix 2022
1 Rows(s) Affected
Change Start_Date (ODBC Format)[2022-05-09] :
Change End_Date (ODBC Format)[2022-05-29] :
Change Title [InterSystems Grand Prix 2022] :
ID Start_Date End_Date Title
24 2022-05-09 2022-05-29 InterSystems Grand Prix 2022
1 Rows(s) AffectedAssign also allows Remove or (implicitly) Overload assignment of a prize.
Select Contests, Prizes, Winners, Assign, Statistics, eXit (C,P,W,A,S,X) :A
Assign or Remove Winner (A,R) :a
Winner's region (as,br,eu,ru,us) :us
ID Region RegID Short
us||1 us 1 ?
us||2 us 2 ?
us||3 us 3 ?
us||4 us 4 ?
4 Rows(s) Affected
Winner's RegID :4
Contest ID :5
ID Cat Rank Value WinrID Short Title
5 C 1 1000 br||1 ? InterSystems IRIS for Health FHIR
5 C 2 500 br||2 ? InterSystems IRIS for Health FHIR
5 X 1 1500 br||1 ? InterSystems IRIS for Health FHIR
5 X 2 1500 br||2 ? InterSystems IRIS for Health FHIR
5 X 3 500 br||5 ? InterSystems IRIS for Health FHIR
5 Rows(s) Affected
Category (C,X) :c Rank :1
ID Cat Rank Value WinrID Short Title
5 C 1 1000 br||1 ? InterSystems IRIS for Health FHIR
1 Rows(s) Affected
Assign Winner us||4 (Y,N) [N]:y
ID Cat Rank Value WinrID Short Title
5 C 1 1000 us||4 ? InterSystems IRIS for Health FHIR
1 Rows(s) Affected
Contest ID :Statistics is a collection of pre-composed queries.
You can always select the Category displayed: (C=Community, X=eXperts, *=All)
In order to inspire you for your own queries, you can also display the SQL statement used.
Select Contests, Prizes, Winners, Assign, Statistics, eXit (C,P,W,A,S,X) :s
Prepared Statistics
1 - Total prizes by contest
2 - Total prizes by region
3 - Total prizes by winners
4 - Winner's ranking in contest
5 - Winner's Profile
X - eXit
Select statistic [X]:2
Category (C=Community,X=eXperts,*=All) :*
Cat Prizes Value Region
* 88 68170 br
* 40 42425 ru
* 27 19700 eu
* 13 15825 as
* 8 9000 us
5 Rows(s) Affected
Show SQL Statement (YN) [N] :y
SELECT LPAD(cat,3) Cat, LPAD(count(*),3) Prizes, Sum(val) Value, Region
FROM ( SELECT '*' Cat,
C_value val, C_winner->Region FROM dc_rcc.Contest_C
UNION ALL SELECT '*' Cat,
X_value val, X_Winner->Region FROM dc_rcc.Contest_X
) WHERE val>1 AND NOT Region IS NULL
GROUP BY Region ORDER BY 3 DESCor
Prepared Statistics
1 - Total prizes by contest
2 - Total prizes by region
3 - Total prizes by winners
4 - Winner's ranking in contest
5 - Winner's Profile
X - eXit
Select statistic [X]:5
Category (C=Community,X=eXperts,*=All) :c
Cat Contest Best Winner Value ConCnt ContestList - - - - - - - - - - - - - - RankList
C 1 1 ru||1 4750 7 1,11,13,16,21,22,23 1,3,3,1,3,1,1
C 1 1 br||4 1770 5 1,2,15,18,19 2,2,1,3,1
C 2 1 br||1 7250 11 2,5,6,7,9,10,12,13,14,17,18 1,1,1,2,1,2,2,1,2,2,2
C 3 1 br||5 1750 3 3,4,14 2,1,4
C 3 1 eu||3 1000 1 3 1
C 4 1 br||2 4500 10 4,5,9,10,12,13,14,18,21,23 2,2,2,3,3,2,1,1,1,2
C 4 1 br||3 2950 6 4,7,9,17,18,19 3,1,3,1,4,2
C 6 2 ru||2 1075 3 6,19,22 2,3,2
C 6 3 ru||8 250 1 6 3
C 7 3 eu||1 750 3 7,12,17 3,4,3
C 10 1 as||2 4000 2 10,11 1,1
C 11 2 as||3 1500 1 11 2
C 12 1 eu||2 750 1 12 1
C 13 4 br||6 250 1 13 4
C 14 3 ru||5 500 1 14 3
C 16 2 as||5 500 1 16 2
C 16 3 ru||3 250 1 16 3
C 21 2 as||1 1000 2 21,23 2,3
C 22 3 as||4 625 1 22 3
19 Rows(s) Affected
Show SQL Statement (YN) [N] :Happy testing.