Google's Annotated Timeline Chart and Sql Server 2005

I had a small project where I needed to use the functionality of the Google Timeline Annotated Chart from the Google API. The provided charts in the API are pretty nifty, but most of the data that I would want to display is kept in a DB somewhere, I needed a way to have simple SQL queries build a chart for me on the fly, below is what I came up with.

First attempt was to use php to query the DB format all my data in a way that Google wanted and then spit out the html into a div, this worked but was an extra step that was unnecessary.

For this example we'll go ahead and say I have a table of cities and their high temperatures for a given day in the last week, this will be a very simple table with 3 columns consisting of Timestamp, City, Temp. (In reality my table structure was a little more complicated, but for this example, a simple table called TempData should suffice).

The following stored procedure will get all the values needed from our database and return a varchar that consists of the html needed to display the graph, we can then put that value into a div and the graph will be displayed.

USE [DATABASENAME]

GO



SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[sp_google_temperature_trends]



AS



DECLARE

@Timestamp varchar(50),

@Year varchar(50),

@Month varchar(50),

@Day varchar(50),

@Hour varchar(50),

@Min varchar(50),

@City varchar(50),

@Temp varchar(50),

@avgTemp varchar(50),

@t int,

@html varchar(max),

@hideCityHtml varchar(max)



/* First We'll print out the top of the container info for the goolge annotated timeline graph, for google api info see:

  http://code.google.com/apis/visualization/documentation/gallery/annotatedtimeline.html

*/



SET @html = '

       

          

          

     



       

              

         


		  ' + @hideCityHtml +'           

'