select CONVERT(VARCHAR(11),tkt_time,106) AS 'Time', count(tkt_id) as TotTickets from ticket

group by CONVERT(VARCHAR(11),tkt_time,106)

order by CONVERT(VARCHAR(11),tkt_time,106) desc

Gives me an output of:

28 Feb 2008 6
27 Mar 2008 1
26 Mar 2008 5
24 Mar 2008 1
19 Mar 2008 2
18 Mar 2008 1
08 Apr 2008 1
07 Apr 2008 1
06 Mar 2008 2
05 Mar 2008 6
04 Mar 2008 11
04 Apr 2008 2

The reason for the Convert is because tkt_time is stored as a TIMESTAMP and of course since I want a count of tickets for that day not that specific time thus it had to be converted.

DbConn oRs; string strQuery;

//strXML will be used to store the entire XML document generated

string strXML;

//Generate the graph element

strXML = "<graph caption='Number of Trouble Tickets' subCaption='By Day' decimalPrecision='0' showNames='1' formatNumberScale='0'>";

//Iterate through each date

strQuery = "select * from Ticket";

oRs = new DbConn(strQuery);

while (oRs.ReadData.Read())

// {

//Now create second recordset to get details for this datestrQuery = "select count(tkt_id) as TotTickets from ticket where tkt_time=" + oRs.ReadData["tkt_time"].ToString();

DbConn oRs2 = new DbConn(strQuery);

oRs2.ReadData.Read();

//Generate <set name='..' value='..' />

strXML += "<set name='" + oRs.ReadData["tkt_time"].ToString() + "' value='" + oRs2.ReadData["TotTickets"].ToString() + "' />";

//Close recordset

//}

//Finally, close <graph> element

strXML += "</graph>";

//Set Proper output content-type

Response.ContentType = "text/xml";

//Just write out the XML data

//NOTE THAT THIS PAGE DOESN'T CONTAIN ANY HTML TAG, WHATSOEVER

I would think that the above code would give me:

1 2008-02-28 14:41:47.187
1 2008-02-28 14:42:15.623
1 2008-02-28 14:43:24.310
1 2008-02-28 14:54:28.260
1 2008-02-28 15:22:55.530

Same as above, just not converted nor grouped. But it doesn't even work. I have tried the original query just by itself and I get the correct data for the first date in the table, but of course it stops at that because I'm not iterating through each date. I'm at a loss for what to do. These are the kind of queries that we need graphed. I'm very new to asp.net and programming in general, so any help would be much appreciated.

Here's what I've tried:

DbConn oRs;

string strQuery;

string strXML;

strXML = "<graph caption='Number of Trouble Tickets' subCaption='By Day' decimalPrecision='0' showNames='1' formatNumberScale='0'>";

strQuery = "select CONVERT(VARCHAR(11),tkt_time,106) AS cTime, count(tkt_id) as TotTickets from ticket group by CONVERT(VARCHAR(11),tkt_time,106) order by CONVERT(VARCHAR(11),tkt_time,106) desc ";

oRs = new DbConn(strQuery);

//Iterate through each Record

while (oRs.ReadData.Read()){

strXML += "<set name='" + oRs.ReadData["cTime"].ToString() + "' value='" + oRs.ReadData["TotTickets"].ToString() + "' />";

}

oRs.ReadData.Close();

strXML += "</graph>";

Response.ContentType = "text/xml";

Response.Write(strXML);

Thanks,
Arindam

FusionCharts Team
www.fusioncharts.com