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 Burndown
9/16/2012 5 5
9/17/2012 4 5
9/18/2012 3 4
9/19/2012 2 3
9/20/2012 1 3
9/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 Work
9/16/2012 0
9/17/2012 0
9/18/2012 1
9/19/2012 1
9/20/2012 0
9/21/2012 3
with setrowid (anchordate, completedwork, rowid)
as
(
select anchordate, isnull(completedwork,0) as completedwork,ROW_NUMBER() OVER (ORDER BY anchordate DESC) As rowid
from
(
SELECT CONVERT(Varchar, fnGetDatesInRange_1.Dt, 101) AS anchordate,
burndowndays.completedwork
FROM 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 desc
In 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 - 09/16/2012 : 10:23:19
