Hello all,I am trying to write a query for a burndown chart that will generate the following result table for me that I can bind to a chart.Date Ideal Burndown Actual Burndown9/16/2012 5 59/17/2012 4 59/18/2012 3 49/19/2012 2 39/20/2012 1 39/21/2012 0 0 I am able to extract how many days of work were completed on any of these days from my tables. For e.g. in the above scenario, the actual completed work would like this:Date Actual Completed Work9/16/2012 09/17/2012 09/18/2012 19/19/2012 19/20/2012 09/21/2012 3with setrowid (anchordate, completedwork, rowid)as(select anchordate, isnull(completedwork,0) as completedwork,ROW_NUMBER() OVER (ORDER BY anchordate DESC) As rowidfrom(SELECT CONVERT(Varchar, fnGetDatesInRange_1.Dt, 101) AS anchordate, burndowndays.completedworkFROM dbo.fnGetDatesInRange('09/1/2012', '12/04/2012') AS fnGetDatesInRange_1 LEFT OUTER JOIN burndowndays ON CONVERT(Varchar, fnGetDatesInRange_1.Dt, 101) = CONVERT(varchar, burndowndays.completiondate, 101)) dt)select * from setrowid order by rowid descIn the above query I am able to get the 'Ideal Burndown' using ROW_NUMBER(). How do I now get the 'Actual Burndown' along with this information? I tried inserting into a temp table using a loop, but it became very slow.I am new to SQL server programming. Any help you can provide me is appreciated.
Query for Burndown chart
Posted - 2012-09-16 : 10:23:19
