1

This is going to be tricky to explain, but I am writing a small system to manage my scrum projects. The basics are: I have a project, which can have many sprints, and a sprint can be broken into a few sections. For example, Project A has 4 sprints. Each sprint is broken into a Planning, Sprinting and Stabilisation section.

I am using SQL Server 2012.

A sprint can overlap another sprint. This allows for some developers to be part of planning, for sprint B, while other developers are still working on stabilisation in Sprint A. So, Planning and Stabilisation of two sprints fall in the same week.

The application is handling this well. My problem lies in a report I am trying to create. I want it to list the dates on the y axis, and the x axis shows the resources and to what they are assigned. A resource can only be part of one sprint at a time.

At the moment, I am just trying to query the dates of the project, the sprint that that date is covered by, and the sprint section. But I only want one row returned per date value. Because of the overlap, I get repeating date values.

Here is my current query: (Calendar is simply all possible dates, with some handy data abou that date)

SELECT
    cal.DateValue,
    ps.Description,
    pss.Description
FROM calendar cal
INNER JOIN project p
    ON cal.DateValue BETWEEN p.StartDate AND p.EndDate
INNER JOIN project_sprint ps
    ON ps.ProjectId = p.Id
    AND cal.DateValue BETWEEN ps.StartDate AND ps.EndDate
    AND ps.Deleted IS NULL
INNER JOIN project_sprint_section pss
    ON pss.ProjectSprintId = ps.Id
    AND cal.DateValue BETWEEN pss.StartDate AND pss.EndDate
    AND pss.Deleted IS NULL
WHERE p.Id = 26
ORDER BY cal.DateValue

And here is what I get back, showing the issue I have:

enter image description here

From the 4th, until the 9th, planning and stabilisation overlap.

What I would like the query to do, is, using a group, possibly, return the second description as : "Planning/Stabilisation" for the overlapping dates.

But I am unsure if there is an efficient way to do this. Can this be done with a group, see if there is a count > 1, and then concatenate the two rows descriptions?

Tomas
  • 57,621
  • 49
  • 238
  • 373
Craig
  • 18,074
  • 38
  • 147
  • 248
  • you need group concat, which is in every database, but seemingly not in SQL Server. Try to search for workarounds: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 – Tomas Aug 03 '13 at 06:31
  • see also http://stackoverflow.com/questions/8868604/sql-group-concat-function-in-sql-server and http://stackoverflow.com/questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query and http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f09d4166-2030-41fe-b86e-392fbc94db53/tsql-equivalent-for-groupconcat-function – Tomas Aug 03 '13 at 06:35
  • 1
    This can be done with the help of recursive CTE or cursor. – Bogdan Sahlean Aug 03 '13 at 07:10
  • I was thinking about a CTE (over a cursor), but not sure how that can be done. An idea? – Craig Aug 03 '13 at 11:48

1 Answers1

0

Here is an example, if you want to use CTE for group concat. But I do not recommend it. You can simply use XML method to do same thing.

Community
  • 1
  • 1
AgentSQL
  • 2,810
  • 20
  • 22