j'ai un code qui génère mes 130 requ^tes, je me demandais s'il n'étais pas possible de simplifier un peu, en mettant des group by par exemple, mais je ne voit pas très bien comment.
voici mon code qui génère :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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 protected void Page_Load(object sender, EventArgs e) { IList[] yValues = new ArrayList[13]; for (int i = 0; i < 13; i++) yValues[i] = new ArrayList(); DebugBox.Text = ""; const String SELECT_FROM = "SELECT count(t.ticketid) FROM ticket t "; String qSelect = ""; IEnumerator it; ISession session = new SessionScopeWrapper(); for (int i = 0; i < 10; i++) { /*créés dans le mois :*/ qSelect = SELECT_FROM + "WHERE " + inMonth("t.createdate", i - 10) ; DebugBox.Text += qSelect + "\n"; it= session.CreateSQLQuery(qSelect).List().GetEnumerator(); if (it.MoveNext()) yValues[0].Add(it.Current); /* créé et résolu dans le mois*/ qSelect = SELECT_FROM + "WHERE " + inMonth("t.createdate", i - 10) + "AND " + inMonth("t.completeddate", i - 10) ; DebugBox.Text += qSelect + "\n"; it = session.CreateSQLQuery(qSelect).List().GetEnumerator(); if (it.MoveNext()) yValues[1].Add(it.Current); for (int j = 1; j < 11; j++) { qSelect = SELECT_FROM + "WHERE " + inMonth("t.createdate", i - j - 10) + "AND NOT " + lowerMonth("t.completeddate", i - 10 + 1) ; DebugBox.Text += qSelect + "\n"; it = session.CreateSQLQuery(qSelect).List().GetEnumerator(); if (it.MoveNext()) yValues[1+j].Add(it.Current); } qSelect = SELECT_FROM + "WHERE " + lowerMonth("t.createdate", i - 10) + "AND " + inMonth("t.completeddate", i - 10) ; DebugBox.Text += qSelect + "\n"; it = session.CreateSQLQuery(qSelect).List().GetEnumerator(); if (it.MoveNext()) yValues[12].Add(it.Current); } for (int i = 0; i < 13; i++) Chart1.Series.FindByName("Series" + (i + 1)).Points.DataBindY(yValues[i]); } private string inMonth(String field, int number) { return greaterMonth(field, number) + "AND " + lowerMonth(field, number + 1); } private string greaterMonth(String field, int number) { return field+">dateadd(\"mm\","+number+",getdate()) "; } private string lowerMonth(String field, int number) { return field + "<dateadd(\"mm\"," + number + ",getdate()) "; }
et voici une partie du sql généré :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13 SELECT count(t.ticketid) FROM ticket t WHERE t.createdate>dateadd("mm",-10,getdate()) AND t.createdate<dateadd("mm",-9,getdate()) SELECT count(t.ticketid) FROM ticket t WHERE t.createdate>dateadd("mm",-10,getdate()) AND t.createdate<dateadd("mm",-9,getdate()) AND t.completeddate>dateadd("mm",-10,getdate()) AND t.completeddate<dateadd("mm",-9,getdate()) SELECT count(t.ticketid) FROM ticket t WHERE t.createdate>dateadd("mm",-11,getdate()) AND t.createdate<dateadd("mm",-10,getdate()) AND NOT t.completeddate<dateadd("mm",-9,getdate()) SELECT count(t.ticketid) FROM ticket t WHERE t.createdate>dateadd("mm",-12,getdate()) AND t.createdate<dateadd("mm",-11,getdate()) AND NOT t.completeddate<dateadd("mm",-9,getdate()) SELECT count(t.ticketid) FROM ticket t WHERE t.createdate>dateadd("mm",-13,getdate()) AND t.createdate<dateadd("mm",-12,getdate()) AND NOT t.completeddate<dateadd("mm",-9,getdate()) SELECT count(t.ticketid) FROM ticket t WHERE t.createdate>dateadd("mm",-14,getdate()) AND t.createdate<dateadd("mm",-13,getdate()) AND NOT t.completeddate<dateadd("mm",-9,getdate()) SELECT count(t.ticketid) FROM ticket t WHERE t.createdate>dateadd("mm",-15,getdate()) AND t.createdate<dateadd("mm",-14,getdate()) AND NOT t.completeddate<dateadd("mm",-9,getdate()) SELECT count(t.ticketid) FROM ticket t WHERE t.createdate>dateadd("mm",-16,getdate()) AND t.createdate<dateadd("mm",-15,getdate()) AND NOT t.completeddate<dateadd("mm",-9,getdate()) SELECT count(t.ticketid) FROM ticket t WHERE t.createdate>dateadd("mm",-17,getdate()) AND t.createdate<dateadd("mm",-16,getdate()) AND NOT t.completeddate<dateadd("mm",-9,getdate()) SELECT count(t.ticketid) FROM ticket t WHERE t.createdate>dateadd("mm",-18,getdate()) AND t.createdate<dateadd("mm",-17,getdate()) AND NOT t.completeddate<dateadd("mm",-9,getdate()) SELECT count(t.ticketid) FROM ticket t WHERE t.createdate>dateadd("mm",-19,getdate()) AND t.createdate<dateadd("mm",-18,getdate()) AND NOT t.completeddate<dateadd("mm",-9,getdate()) SELECT count(t.ticketid) FROM ticket t WHERE t.createdate>dateadd("mm",-20,getdate()) AND t.createdate<dateadd("mm",-19,getdate()) AND NOT t.completeddate<dateadd("mm",-9,getdate()) SELECT count(t.ticketid) FROM ticket t WHERE t.createdate<dateadd("mm",-10,getdate()) AND t.completeddate>dateadd("mm",-10,getdate()) AND t.completeddate<dateadd("mm",-9,getdate())
Partager