Arranging date information into a timeline

[supermatchgame]

Hello,

I have a table that contains data like this:

ProjectTimeID | ProjectID | PersonID | StartDate | EndDate
--------------|-----------|----------|-----------|--------
1 |1 |1 |2009-01-01 |2009-01-02
2 |2 |1 |2009-01-03 |2009-01-04
3 |1 |2 |2009-01-01 |2009-01-02

and I want to display the information like this:

Person | 01-01-2009 | 02-01-2009 | 03-01-2009
-------|------------|------------|------------
1 |1 |1 |2
2 |1 |1 |

Can someone suggest a link or some code which will show me how to achieve this?

Thanks.

 

[NathanGriffiths]

Hi, this should get you started. The script creates a temporary table #temp1 to simulate the table you describe. It then builds a temp table of all dates between the earliest Start Date and latest End Date in the source table, which is then joined to the source table to produce a matrix of all days , Persons and ProjectIDs in a vertical timeline.
Then a PIVOT is used to translate this into a horizontal timeline, using a dynamically generated list of dates as the column names.

The source table looks like this (I added a few extra bits of data):

ProjectTimeID ProjectID   PersonID    StartDate               EndDate
------------- ----------- ----------- ----------------------- -----------------------
1             1           1           2009-01-01 00:00:00.000 2009-01-02 00:00:00.000
2             2           1           2009-01-03 00:00:00.000 2009-01-04 00:00:00.000
3             1           2           2009-01-01 00:00:00.000 2009-01-02 00:00:00.000
4             3           2           2009-01-04 00:00:00.000 2009-01-05 00:00:00.000
5             2           3           2009-01-03 00:00:00.000 2009-01-06 00:00:00.000

This is the SQL:

/* Set up temp table */
CREATE TABLE #temp1
    (
      ProjectTimeID INT IDENTITY(1, 1),
      ProjectID INT,
      PersonID INT,
      StartDate DATETIME,
      EndDate DATETIME
    )

INSERT  INTO #temp1 (ProjectID, PersonID, StartDate, EndDate)
VALUES  (1, 1, '2009-01-01', '2009-01-02')
        
INSERT  INTO #temp1 (ProjectID, PersonID, StartDate, EndDate)
VALUES  (2, 1, '2009-01-03', '2009-01-04')

INSERT  INTO #temp1 (ProjectID, PersonID, StartDate, EndDate)
VALUES  (1, 2, '2009-01-01', '2009-01-02')


INSERT  INTO #temp1 (ProjectID, PersonID, StartDate, EndDate)
VALUES  (3, 2, '2009-01-04', '2009-01-05')

INSERT  INTO #temp1 (ProjectID, PersonID, StartDate, EndDate)
VALUES  (2, 3, '2009-01-03', '2009-01-06')
	
	
/*--- Code to pivot dates ---*/	

DECLARE 
	@MinStartDate DATETIME,
    @MaxEndDate DATETIME,
    @CurrentDate DATETIME,
    @PivotColumns VARCHAR(1000) 

SELECT  @MinStartDate = MIN(StartDate)
FROM    #temp1
	
SELECT  @MaxEndDate = MAX(EndDate)
FROM    #temp1
	
-- create a table list of all dates between the first start date 
-- and last end date in the original table
CREATE TABLE #dateslist ( CalDate DATETIME )
	
SET @CurrentDate = @MinStartDate
	
SET @PivotColumns = ''
	
WHILE @CurrentDate <= @MaxEndDate
    BEGIN
		
		-- add date to table
        INSERT  INTO #dateslist ( CalDate )
        VALUES  ( @CurrentDate )
		
		-- build a list of dates to use as column headings
        SET @PivotColumns = @PivotColumns + '['
            + CONVERT(VARCHAR(10), @CurrentDate, 120) + '], '
		
		-- increment date
        SET @CurrentDate = DATEADD(dd, 1, @CurrentDate)	
		
    END
	
-- remove trailing comma 	
SET @PivotColumns = LEFT(@PivotColumns, LEN(@PivotColumns) - 1)
	
-- build pivot sql statement
SET @PivotColumns = 'SELECT * FROM 
	(SELECT d.CalDate, n.PersonID, n.ProjectID 
			FROM #dateslist d LEFT JOIN #temp1 n ON d.CalDate BETWEEN n.StartDate AND n.EndDate) a
			PIVOT (SUM(ProjectID) FOR CalDate IN (' + @PivotColumns
    + ')) pvt'

-- execute pivot sql			
EXEC ( @PivotColumns )

The results look like this:

PersonID    2009-01-01  2009-01-02  2009-01-03  2009-01-04  2009-01-05  2009-01-06
----------- ----------- ----------- ----------- ----------- ----------- -----------
1           1           1           2           2           NULL        NULL
2           1           1           NULL        3           3           NULL
3           NULL        NULL        2           2           2           2