1

This question already has an answer here:

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;

marked as duplicate by marc_s, Paul White Sep 27 '15 at 1:16

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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.

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