Spreading Amount across financial year based on Start and End Date

Hi Jaggy,



To achieve the output like the view1 and view2 in your uploaded excel, you can reference the below.

    DECLARE　@table TABLE(PropCode INT
    ,PropStartDate DATE
    ,PropEndDate char(10)
    ,PropRentStartDate DATE
    ,PropRentEndDate DATE
    ,MarketRent Money)

    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',420.00)
    ,(3960,      '2014-10-31','NULL','2014-11-05','2016-11-05',470.00)

        DECLARE @Year DATE
        SET @Year = '2014-07-31' --set the finance year

        --view1 output
        ;WITH cte AS
        (
        SELECT @Year AS finMonth, 1 as level
        UNION ALL
        SELECT DATEADD(Month,level,@Year), level+1 FROM cte
        WHERE level<12
        )
        SELECT t.PropCode,DATENAME(YYYY,c.finMonth) [Year],DATENAME(MM,c.finMonth) [Month],t.MarketRent FROM @table t
                                  JOIN cte c
                                 ON c.finMonth BETWEEN t.PropRentStartDate AND t.PropRentEndDate
        ORDER BY t.PropCode,c.finMonth

        --view2 output 
        ;WITH cte AS
        (
        SELECT @Year AS finMonth, 1 as level
        UNION ALL
        SELECT DATEADD(Month,level,@Year), level+1 FROM cte
        WHERE level<12
        )
        ,cte2 AS
        (
        SELECT t.PropCode,DATENAME(MM,c.finMonth) [Month],t.MarketRent FROM @table t
                                  JOIN cte c
                                 ON c.finMonth BETWEEN t.PropRentStartDate AND t.PropRentEndDate
        )
        SELECT PropCode,ISNULL([July],0),ISNULL([August],0),ISNULL([September],0),ISNULL([October],0),ISNULL([November],0),ISNULL([December],0),ISNULL([January],0),ISNULL([February],0),ISNULL([March],0),ISNULL([April],0),ISNULL([May],0),ISNULL([June],0)  FROM cte2
        PIVOT 
        (MAX(MarketRent) FOR [Month] IN([July],[August],[September],[October],[November],[December],[January],[February],[March],[April],[May],[June])) pvt

If you have any question, feel free to let me know.

---

Eric Zhang

TechNet Community Support