Spreading value across financial year

Question

Spreading value across financial year

jaggy99

Ten Centuries

Points: 1308
More actions
February 28, 2015 at 1:02 am

#295583

Hi All,
I have a table which can be downloaded from the link below. The table contains property Market Rent and period the new rent is applicable. I need to generate a report with parameter ( year and month) so when the user inputs year and month the associated market rent amount for that month is listed.
For example if the 1st market rent update was done in June 2014 ( $300) and the 2nd in Dec 2014 $(350), the property market rent from June to Nov should be $300 and from Dec 2014 till the next rent update $350. So if user inputs year 2014 and month August, the amount is $300 and if the user enters the year 2015 and month March amount is $350
https://app.box.com/s/d4gbqpyuejx0319cm4jijly37qyozpqw
Below is the table with sample data
DECLARE @table TABLE ( PropCode INT ,PropStartDate DATE ,PropEndDate char(10) ,PropRentStartDate DATE ,PropRentEndDate DATE ,MarketRent INT)
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)
Select * from @table
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

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

jaggy99 Ten Centuries Points: 1308 More actions · Answer 2

Thanks Eirikur. What if I just need the view in normal columns please.

PropCodeYearMonthMarket Rent

27182014July299.18

27182014August299.18

27182014September299.18

27182014October299.18

27182014November299.18

27182014December299.18

27182015January310

27182015February310

27182015March310

27182015April310

27182015May310

27182015June310

39012014July400

39012014August400

39012014September400

39012014October400

39012014November400

39012014December420

39012015January420

39012015February420

39012015March420

39012015April420

39012015May420

39012015June420

39602014November470

39602014December470

39602015January470

39602015February470

39602015March470

39602015April470

39602015May470

39602015June470

Jag

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

That would be the result set prior to the cross-tab or in other words, just remove the cross-tab case statements