SQL Help - sum items based on max but also smaller than...?

GlobX

Wow, not even sure how to word the question... Here goes: Basically, I'm trying to build a custom burn-down with TFS, Excel and SQL. Don't ask me why I'm doing a custom one when I shouldn't be, that's a question for my boss :( Anyhow, I have a query that's set up a few tables and now I want to join them. Here's an example of my two temp tables: Tickets:

ID Changed date Points State
61 2011-02-25 06:38:07.090 0 For Production
61 2011-02-26 03:46:02.577 0 In Production
61 2011-03-04 03:22:32.620 0 Done
499 2011-03-04 04:26:10.060 0 New
623 2011-02-28 00:25:45.250 0.5 In Production
708 2011-03-03 00:55:31.407 3 In Development
708 2011-03-03 00:57:27.497 3 In Development
708 2011-03-03 03:55:17.390 3 In QA/UAT
708 2011-03-03 23:05:56.020 3 In QA/UAT
708 2011-03-04 05:21:43.133 3 In QA/UAT
738 2011-02-28 05:04:04.250 5 In Development
738 2011-02-28 22:56:58.053 5 In Development
738 2011-03-01 00:50:28.037 5 In Development
738 2011-03-01 21:06:35.550 5 In Development
738 2011-03-03 22:54:17.137 5 In QA/UAT
894 2011-02-28 00:29:04.183 0 To Do

Historical data in TFS is stored as a copy of the WorkItem row as it was BEFORE the change, along with a Changed Date field. In other words, the work item with id 3 has multiple copies in the WorkItemsWere table, each with a set of different properties, as can be seen above. and Sprint Days:

Day Day of year
Mon 1 51
Tue 1 52
Wed 1 53
Thu 1 54
Fri 1 55
Mon 2 58
Tue 2 59
Wed 2 60
Thu 2 61
Fri 2 62

I am achieving my burn-down by joining these two tables. My results so far look like this:

Day Points Day of year
Mon 1 0 51
Tue 1 0 52
Wed 1 0 53
Thu 1 0 54
Fri 1 0 55
Mon 2 0 58
Tue 2 0 59
Tue 2 5 59
Wed 2 0 60
Wed 2 10 60
Thu 2 0 61
Thu 2 10 61
Fri 2 0 62
Fri 2 3 62
Fri 2 15 62

See, the problem is I am joining each row in my sprint days table against EVERY work item row with a changed date <= the sprint day, and then summing. Here's my query:

SELECT
[Day] AS [Sprint Day],
SUM(Points) AS Points,
[Day of year] AS [Order]

FROM
#sprintDays d

LEFT JOIN
	(
		SELECT
			Id,
			Points,
			DATEPART(DAYOFYEAR, \[Changed Date\]) AS \[Changed Date\],
			ROW\_NUMBER() OVER
			(
				PARTITION BY
					Id,
					DATEPART(DAYOFYEAR, \[Changed Date\])
				ORDER BY
					DATEPART(DAYOFYEAR, \[Changed Date\]) DESC
			) AS Row
			
		FROM
			#tickets

		WHERE
			(\[State\] = 'Committed' OR
			 \[State\] = 'In Development' OR
			 \[State\] = 'In Q

Wendelius

Hi, First I must admit that I didn't follow the whole sequence, but if I understood correctly, you want to have each sprint date and the for every sprint date the sum of points for that and earlier dates. If that's correct, could you simply use scalar in your select list to calculate a running total. Something like:

SELECT d.Day,
d.[Day Of Year],
(SELECT SUM(Points)
FROM #tickets t
WHERE t.[Changed Date] <= d.[Day Of Year]
...other possible restrictions...) AS Points
FROM #sprintDays d

The need to optimize rises from a bad design.My articles[^]

GlobX

Mika Wendelius wrote:

you want to have each sprint date and the for every sprint date the sum of points for that and earlier dates

Aha! That's the rub, see - not just the sum of points for all earlier dates, but the one, latest date found per individual id in a list of earlier dates... I will give your suggestion a go and see if I can tweak it to my needs. Thanks for your help! If you have any more suggestions, throw them my way :) It's really hard to explain, I think EDIT: At the moment it feels like I need to partition again, but I need to partition on the [Changed Date] of the sprintDays table on "each loop" of the select in order to find the highest ranked item... Maybe I should use a FOR loop or something? I want to avoid it, though, surely there's a way to do this in "Plain Old SQL".

modified on Monday, March 7, 2011 6:22 PM

GlobX

Fixed - I bit the bullet and used a cursor. Although, funny story, I couldn't figure out why the cursor was taking forever and the query plan was saying it was <2% of my processing time... I wasn't fetching inside the loop. It was update the first row over... and over... and over... *bashes head into desk*

Wendelius · modified on Monday, March 7, 2011 6:22 PM

Hi, I noticed that you solved the problem by using cursor, which is fine but fot performance reasons I think that if we could find a set-based solution it'd be best. So if I understood correctly you need each individual ID in your result set and sums for them, so could the query be something like:

SELECT d.Day,
d.[Day Of Year],
(SELECT SUM(Points)
FROM #tickets t
WHERE t.[Changed Date] <= d.[Day Of Year]
AND t.Id = sub1.Id) AS Points
FROM #sprintDays d,
(SELECT DISTINCT t.Id
FROM #tickets t) sub1

Just correct me if If I'm going to wrong direction.

The need to optimize rises from a bad design.My articles[^]