1

I'm stuck on the following problem.

I have a start range date and end range date calculated by a query and I have three columns also produced by another query, the outcome of the last query is:

PRJ_ID | START_DATE | END_DATE

I want to produce an output using a stored procedure that considering all the months between start range date and end range date produces an output like this:

PRJ_ID | MONTHS1-YEAR | MONTHS2-YEAR | ... | MONTHSN-YEAR
XXXX   | YES     | YES     | ... | NO

The yes or no is obtained checking if the current month is in between the start_date and end_date of the project (table obtained above).

I'm able to produce all the months between two dates with this code:

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;

SELECT   @StartDate = '20110501'        
        ,@EndDate   = '20110801';

SELECT  
    DATENAME(MONTH, DATEADD(MONTH, x.number, @StartDate)) AS MonthName
FROM    
    master.dbo.spt_values x
WHERE   
    x.type = 'P'        
    AND x.number <= DATEDIFF(MONTH, @StartDate, @EndDate);

But I'm stuck on producing the last result.

Here is my try, but I'm getting an error on @prjdates format that is not datetime:

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME,
        @minStart    DATETIME,
        @orig_estimated_MaxEnd    DATETIME,
        @maxEnd    DATETIME,
        @curr_planned_MaxEnd         DATETIME;


SELECT   @StartDate = '20110501'        
        ,@EndDate   = '20150801';

SET @minStart = (SELECT MIN(start_date)
                 FROM [PPM].[dbo].[prj_details]
                 WHERE start_date IS NOT NULL 
                   AND start_date <> '1900-01-01') ;

SET @orig_estimated_MaxEnd = (SELECT MAX( DATEADD(day,orig_estimated_duration*7, start_date)) As FinishDate
                              FROM [PPM].[dbo].[prj_details]
                              WHERE start_date IS NOT NULL);

SET @curr_planned_MaxEnd = (SELECT MAX(curr_planned_LF)
                            FROM [PPM].[dbo].[status_and_updates] 
                            WHERE curr_planned_LF IS NOT NULL);

SET @maxEnd = (SELECT
                   CASE 
                      WHEN @orig_estimated_MaxEnd > @curr_planned_MaxEnd 
                        THEN @orig_estimated_MaxEnd 
                      ELSE @curr_planned_MaxEnd 
                   END AS MinValue);                

print @minStart;
print @orig_estimated_MaxEnd;
print @curr_planned_MaxEnd;
print @maxEnd;

DECLARE @prjDates VARCHAR(MAX);

DECLARE @prjDatesTable TABLE
(
    [prjDate] DATETIME
);

DECLARE  @from datetime,
@to datetime;

SET @from = '20110501';
SET @to = '20150501';

;WITH Numbers (Number) AS
(
    SELECT  
        row_number() over (order by object_id)
    FROM 
        sys.all_objects)
INSERT INTO @prjDatesTable 
    SELECT 
        dateadd(month, number, @from) AS months
    FROM Numbers
    WHERE number <= datediff(month, @from, @to);

SELECT @prjDates = COALESCE(@prjDates + '],[', '') +
      (LEFT(CONVERT(VARCHAR, [prjDate], 101),6) + RIGHT(YEAR([prjDate]),2))
FROM @prjDatesTable ORDER BY [prjDate] DESC;

DECLARE @projects TABLE(PRJ_ID varchar(200),  START_DATE DATEtime, END_DATE datetime)
INSERT INTO @projects 
    SELECT p.id,start_date,curr_planned_LF as END_DATE
    from [PPM].[dbo].[prj_details]as p
    left join [PPM].[dbo].[status_and_updates] as s 
    on p.id=s.id
    where curr_planned_LF is not null 
    and start_date IS NOT NULL;

--select * from @projects;
select *
from
    @projects
pivot
(
  count(prj_id)
  for start_date in  ([' + @prjDates + '])
) piv;
2

I would approach this by first creating a permanent dates table I can use to help create the desired output. Then I would create a pivot table using dynamic T-SQL to allow for varying column names in the output.

Setup the test-bed in TEMPDB:

USE tempdb;

IF EXISTS (SELECT 1 FROM sys.objects o WHERE o.name = 'DatesTable')
BEGIN
    DROP TABLE dbo.DatesTable;
END

CREATE TABLE dbo.DatesTable 
(
    dDate DATETIME
        CONSTRAINT PK_DatesTable
        PRIMARY KEY CLUSTERED
    , iDay TINYINT
    , iMonth TINYINT
    , iYear SMALLINT
);

INSERT INTO DatesTable (dDate, iDay, iMonth, iYear)
SELECT TOP(90000) DatesList.dDate, DATEPART(DAY, DatesList.dDate), DATEPART(MONTH, DatesList.dDate), DATEPART(YEAR, DatesList.dDate)
FROM (
    SELECT dDate = DATEADD(DAY, -1, ROW_NUMBER() OVER (ORDER BY o.object_id))
    FROM sys.objects o, sys.objects o1, sys.objects o2
    ) DatesList;

IF EXISTS (SELECT 1 FROM sys.objects o WHERE o.name = 't')
BEGIN
    DROP TABLE dbo.t;
END

CREATE TABLE dbo.t
(
    PRJ_ID INT NOT NULL
    , [START_DATE] DATETIME NOT NULL
    , END_DATE DATETIME NOT NULL
);

INSERT INTO dbo.t (PRJ_ID, START_DATE, END_DATE)
VALUES (1, '2015-08-01', '2015-08-03')
    , (2, '2015-07-01', '2015-08-03')
    , (3, '2015-10-01', '2015-10-03');

Here's the good bit:

DECLARE @PivotStmt NVARCHAR(MAX);
DECLARE @PivotCols NVARCHAR(MAX);

DECLARE @StartDate  DATETIME,
        @EndDate    DATETIME;

SELECT   @StartDate = '20150501'        
        ,@EndDate   = '20151101';

SET @PivotCols = '';

/* Here we are dynamically creating the month-columns for the pivot */
SELECT @PivotCols = @PivotCols + CASE WHEN @PivotCols = '' THEN '' ELSE ', ' END + '[' + STUFF(CONVERT(VARCHAR(30), CONVERT(DATETIME, CONVERT(VARCHAR(4), dt.iYear) + '-' + CONVERT(VARCHAR(2), dt.iMonth) + '-01 00:00:00'), 106),1,3,'') + ']'
FROM dbo.DatesTable dt
WHERE dt.dDate >= (SELECT MIN(t.START_DATE) FROM dbo.t)
    AND dt.dDate <= (SELECT MAX(t.START_DATE) FROM dbo.t)
GROUP BY dt.iMonth
    , dt.iYear
ORDER BY dt.iYear
    , dt.iMonth;

SET @PivotStmt = 'SELECT PRJ_ID
    , ' + @PivotCols + '
FROM
(
    SELECT t.PRJ_ID
        , MonthYear = STUFF(CONVERT(VARCHAR(30), CONVERT(DATETIME, CONVERT(VARCHAR(4), dt.iYear) + ''-'' + CONVERT(VARCHAR(2), dt.iMonth) + ''-01 00:00:00''), 106),1,3,'''')
        , [Yes] = ''YES''
    FROM dbo.t
        INNER JOIN DatesTable dt ON t.START_DATE <= dt.dDate AND t.END_DATE >= dt.dDate
    GROUP BY t.PRJ_ID
        , dt.iMonth
        , dt.iYear
) SourceTable
PIVOT (
    MIN([Yes])
    FOR MonthYear IN (' + @PivotCols + ')
) as pvt
ORDER BY PRJ_ID;';

/* print out the generated pivot statement for debugging */
PRINT (@PivotStmt);
/* execute the dynamically created pivot statement */
EXEC (@PivotStmt);

My example code above returns the following result set:

enter image description here

Notice, Sept 2015 is included in the output even though none of the data in dbo.t contains projects in September 2015.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.