Display a row for each month in a range defined by two columns This is a example of the table I have:

Part No  | Start Date | End Date  |Cost
ABCD1    | 2014-01-01 | 2014-04-01|50.99
BDE2     | 2014-01-01 | 2014-03-01|14.59
ABCD1    | 2014-04-13 | 2014-05-01|13.99


And this is the output I want to see

Part No  | Start Date | End Date  |Cost   |Period
ABCD1    | 2014-01-01 | 2014-04-01|50.99  |2014/01
ABCD1    | 2014-01-01 | 2014-04-01|50.99  |2014/02
ABCD1    | 2014-01-01 | 2014-04-01|50.99  |2014/03
BDE2     | 2014-01-01 | 2014-03-01|14.59  |2014/01
BDE2     | 2014-01-01 | 2014-03-01|14.59  |2014/02
ABCD1    | 2014-04-13 | 2014-05-01|13.99  |2014/04


I want to add certain number of rows depending of the range of dates. Also, in each row add the column period that is basically a month and year.
I would suggest creating a dates table and cross-joining that table to your parts table, something like the following example.

Do this in tempdb to not step on anything else.

USE tempdb;


Create a Months table, with the Year, Month, and a starting date for each:

CREATE TABLE dbo.Months
(
    iYear int NOT NULL
    , iMonth int NOT NULL
    , dDate date NOT NULL
    , CONSTRAINT pk_Months
        PRIMARY KEY CLUSTERED
        (iYear, iMonth)
);


Use a CTE to populate rows into the table above.  This example creates years/months in the range of 1900-01-01 to 2173-10-01:

;WITH cte AS
(
    SELECT v.Num
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(Num)
)
, cteDates AS 
(
    SELECT dDate = DATEADD(DAY, 0, ROW_NUMBER() OVER (ORDER BY c1.Num))
    FROM cte c1
        CROSS APPLY cte c2
        CROSS APPLY cte c3
        CROSS APPLY cte c4
        CROSS APPLY cte c5
)
, cteMonths AS
(
    SELECT iYear = DATEPART(YEAR, cteDates.dDate)
        , iMonth = DATEPART(MONTH, cteDates.dDate)
    FROM cteDates
)
INSERT INTO dbo.Months(iYear, iMonth, dDate)
SELECT cteMonths.iYear
    , cteMonths.iMonth
    , DATEFROMPARTS(cteMonths.iYear, cteMonths.iMonth, 1)
FROM cteMonths
GROUP BY cteMonths.iYear
    , cteMonths.iMonth
ORDER BY cteMonths.iYear
    , cteMonths.iMonth;


Create the table with the sample "Parts" data:

CREATE TABLE dbo.PartCosts
(
    PartNum varchar(10) NOT NULL
    , StartDate date NOT NULL
    , EndDate date NOT NULL
    , Cost decimal(10,2) NOT NULL
);

INSERT INTO dbo.PartCosts(PartNum, StartDate, EndDate, Cost)
VALUES ('ABCD1', '2014-01-01', '2014-04-01', 50.99)
    , ('BDE2', '2014-01-01', '2014-03-01', 14.59)
    , ('ABCD1', '2014-04-01', '2014-05-01', 13.99);


Run the cross-join to get the results you're after:

SELECT pc.PartNum
    , pc.StartDate
    , pc.EndDate
    , pc.Cost
    , Period = CONVERT(varchar(7), m.dDate, 111)
FROM dbo.PartCosts pc
    CROSS JOIN dbo.Months m
WHERE pc.StartDate &lt;= m.dDate
    AND pc.EndDate &gt; m.dDate;


The results:

+---------+------------+------------+-------+---------+
| PartNum | StartDate  |  EndDate   | Cost  | Period  |
+---------+------------+------------+-------+---------+
| ABCD1   | 2014-01-01 | 2014-04-01 | 50.99 | 2014/01 |
| ABCD1   | 2014-01-01 | 2014-04-01 | 50.99 | 2014/02 |
| ABCD1   | 2014-01-01 | 2014-04-01 | 50.99 | 2014/03 |
| BDE2    | 2014-01-01 | 2014-03-01 | 14.59 | 2014/01 |
| BDE2    | 2014-01-01 | 2014-03-01 | 14.59 | 2014/02 |
| ABCD1   | 2014-04-01 | 2014-05-01 | 13.99 | 2014/04 |
+---------+------------+------------+-------+---------+