SQL Server stored procedure producing data related to months between two dates
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 &gt;= (SELECT MIN(t.START_DATE) FROM dbo.t)
    AND dt.dDate &lt;= (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 &lt;= dt.dDate AND t.END_DATE &gt;= 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:



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