Need help graphing a query

[musicman]

I need help graphing the below query. I can't seem to figure it out.

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

oRs2.ReadData.Close();

//}

oRs.ReadData.Close();

//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

Response.Write(strXML);

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;

//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 date

strQuery =

"select count(tkt_id) as TotTickets from ticket where CONVERT(VARCHAR(11),tkt_time,106)=" + oRs.ReadData["CONVERT(VARCHAR(11),tkt_time,106)"].ToString();

DbConn oRs2 = new DbConn(strQuery);

oRs2.ReadData.Read();

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

strXML +=

"<set name='" + oRs.ReadData["CONVERT(VARCHAR(11),tkt_time,106)"].ToString() + "' value='" + oRs2.ReadData["TotTickets"].ToString() + "' />";

//Close recordset

oRs2.ReadData.Close();

//}

oRs.ReadData.Close();

//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

Response.Write(strXML);

Edited April 11, 2008 by Guest

[Arindam]

Hi musicman,

I went through your program, please do this way I thing it will work for you

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

Edited April 15, 2008 by Guest

[musicman]

That worked, thank you.