Hi and welcome to the forum!

There are several ways of achieving this, here is one example of a cross-tab type solution, should get you passed this hurdle.

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @table TABLE

(

PropCode INT NOT NULL

,PropStartDate DATE NOT NULL

,PropEndDate char(10) NULL

,PropRentStartDate DATE NOT NULL

,PropRentEndDate DATE NOT NULL

,MarketRent NUMERIC(12,2) NOT NULL

)

INSERT INTO @table (PropCode,PropStartDate, PropEndDate,PropRentStartDate,PropRentEndDate, MarketRent)

VALUES (2718, '2013-01-30', 'NULL', '2012-11-29', '2013-07-21', 289.20)

,(2718, '2013-01-30', 'NULL', '2013-07-22', '2013-11-24', 289.20)

,(2718, '2013-01-30', 'NULL', '2013-11-25', '2014-06-14', 289.20)

,(2718, '2013-01-30', 'NULL', '2014-06-15', '2014-11-30', 299.18)

,(2718, '2013-01-30', 'NULL', '2014-12-01', '2015-01-02', 299.18)

,(2718, '2013-01-30', 'NULL', '2015-01-03', '2050-01-01', 310.00)

,(3901, '2014-05-27', 'NULL', '2014-06-09', '2014-11-30', 400.00)

,(3901, '2014-05-27', 'NULL', '2014-12-01', '2050-01-01', 400.00)

,(3960, '2014-10-31', 'NULL', '2014-11-05', '2016-11-05', 470.00)

;

DECLARE @START_DATE_01 DATE = '2014-07-01';

DECLARE @START_DATE_02 DATE = '2014-07-31';

;WITH REPORT_MONTH(MNO) AS (SELECT MNO FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)) AS X(MNO))

SELECT

RX.PropCode

,RX.RateType

,ISNULL(MAX(CASE WHEN RM.MNO = 0 THEN RX.MarketRent END),0) AS [July]

,ISNULL(MAX(CASE WHEN RM.MNO = 1 THEN RX.MarketRent END),0) AS [August]

,ISNULL(MAX(CASE WHEN RM.MNO = 2 THEN RX.MarketRent END),0) AS [September]

,ISNULL(MAX(CASE WHEN RM.MNO = 3 THEN RX.MarketRent END),0) AS [October]

,ISNULL(MAX(CASE WHEN RM.MNO = 4 THEN RX.MarketRent END),0) AS [November]

,ISNULL(MAX(CASE WHEN RM.MNO = 5 THEN RX.MarketRent END),0) AS [December]

,ISNULL(MAX(CASE WHEN RM.MNO = 6 THEN RX.MarketRent END),0) AS [January]

,ISNULL(MAX(CASE WHEN RM.MNO = 7 THEN RX.MarketRent END),0) AS [February]

,ISNULL(MAX(CASE WHEN RM.MNO = 8 THEN RX.MarketRent END),0) AS [March]

,ISNULL(MAX(CASE WHEN RM.MNO = 9 THEN RX.MarketRent END),0) AS [April]

,ISNULL(MAX(CASE WHEN RM.MNO = 10 THEN RX.MarketRent END),0) AS [May]

,ISNULL(MAX(CASE WHEN RM.MNO = 11 THEN RX.MarketRent END),0) AS [June]

FROM REPORT_MONTH RM

CROSS APPLY

(

SELECT

T.PropCode

,'MarketRent' AS RateType

,T.MarketRent

FROM @table T

WHERE T.PropRentStartDate < DATEADD(MONTH,RM.MNO,@START_DATE_02)

AND T.PropRentEndDate >= DATEADD(MONTH,RM.MNO,@START_DATE_01)

) AS RX

GROUP BY RX.PropCode

,RX.RateType

;

Results

PropCode RateType July August September October November December January February March April May June

----------- ---------- ------- -------- ----------- --------- ---------- ---------- --------- ---------- -------- -------- -------- --------

2718 MarketRent 299.18 299.18 299.18 299.18 299.18 299.18 310.00 310.00 310.00 310.00 310.00 310.00

3901 MarketRent 400.00 400.00 400.00 400.00 400.00 400.00 400.00 400.00 400.00 400.00 400.00 400.00

3960 MarketRent 0.00 0.00 0.00 0.00 470.00 470.00 470.00 470.00 470.00 470.00 470.00 470.00