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())