-
Notifications
You must be signed in to change notification settings - Fork 1
/
bigQueryQueries.txt
100 lines (96 loc) · 2.77 KB
/
bigQueryQueries.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
queteurAverageTroncPerYear
const queryStr = ['select avg(tq.euro2*2 +',
'tq.euro1*1 +',
'tq.cents50*0.5 +',
'tq.cents20*0.2 +',
'tq.cents10*0.1 +',
'tq.cents5*0.05 +',
'tq.cents2*0.02 +',
'tq.cent1*0.01 +',
'tq.euro5*5 +',
'tq.euro10*10 +',
'tq.euro20*20 +',
'tq.euro50*50 +',
'tq.euro100*100 +',
'tq.euro200*200 +',
'tq.euro500*500 +',
'tq.don_cheque +',
'tq.don_creditcard),',
'EXTRACT(YEAR from tq.depart) as year',
'from `redcrossquest.tronc_queteur` as tq',
'where tq.queteur_id = @queteur_id',
'and tq.deleted = false',
'group by year ',
'order by year asc'].join('\n');
queteurNumberOfTroncPerYear
const queryStr = ['select count(1), EXTRACT(YEAR from tq.depart) as year',
'from `redcrossquest.tronc_queteur` as tq',
'where tq.queteur_id = @queteur_id',
'AND tq.deleted = false',
'group by year ',
'order by year asc'].join('\n');
ULRankingByAmount
const queryStr = ['select ',
'tq.queteur_id,',
'SUM(',
' tq.euro2 * 2 ',
' tq.euro1 * 1 ',
' tq.cents50 * 0.5 ',
' tq.cents20 * 0.2 ',
' tq.cents10 * 0.1 ',
' tq.cents5 * 0.05 ',
' tq.cents2 * 0.02 ',
' tq.cent1 * 0.01 ',
' tq.euro5 * 5 ',
' tq.euro10 * 10 ',
' tq.euro20 * 20 ',
' tq.euro50 * 50 ',
' tq.euro100 * 100 ',
' tq.euro200 * 200 ',
' tq.euro500 * 500 ',
' tq.don_cheque ',
' tq.don_creditcard ',
') as amount,' ,
' q.first_name,',
' q.last_name' ,
'from `redcrossquest.tronc_queteur` as tq,',
' `redcrossquest.queteur` as q' ,
'where tq.ul_id = @ul_id',
'AND tq.queteur_id = q.id' ,
'AND q.active = true' ,
'AND tq.deleted = false' ,
'group by tq.queteur_id, q.first_name, q.last_name',
'order by amount desc'].join('\n');
ULRankingByAmountCurrentYear
const queryStr = ['select ',
'tq.queteur_id,',
'SUM(',
' tq.euro2 * 2 ',
' tq.euro1 * 1 ',
' tq.cents50 * 0.5 ',
' tq.cents20 * 0.2 ',
' tq.cents10 * 0.1 ',
' tq.cents5 * 0.05 ',
' tq.cents2 * 0.02 ',
' tq.cent1 * 0.01 ',
' tq.euro5 * 5 ',
' tq.euro10 * 10 ',
' tq.euro20 * 20 ',
' tq.euro50 * 50 ',
' tq.euro100 * 100 ',
' tq.euro200 * 200 ',
' tq.euro500 * 500 ',
' tq.don_cheque ',
' tq.don_creditcard ',
') as amount,' ,
' q.first_name,',
' q.last_name' ,
'from `redcrossquest.tronc_queteur` as tq,',
' `redcrossquest.queteur` as q' ,
'where tq.ul_id = @ul_id',
'AND tq.queteur_id = q.id' ,
'AND q.active = true' ,
'AND tq.deleted = false' ,
'AND EXTRACT(YEAR from tq.depart) = EXTRACT(YEAR from CURRENT_DATE())',
'group by tq.queteur_id, q.first_name, q.last_name',
'order by amount desc'].join('\n');