August 4, 2016 at 7:46 pm
Hey Experts,
This recently came up at work and the solution to the problem was utilizing a loop. I was trying to figure out how to achieve this but was struggling.
I have SQL below which is very simple example.
The NULL Rents are not know and what I would like to do is use the previous TimePeriodDate value and growth to get a good estimate for that missing Time frame.
Calc - Previous Row Rent * Growth
For PropertyID =1
Known Rent - 2016-03-01 00:00:00.000 Rent=1200 * Growth = 1.2
Unknown Rent for next month = Previous record (Rent=1200 * Growth = 1.2 ) = 1400
And keep doing this for the unknown values. I am not sure this can be done without loop, data would need to be stored somewhere to get the next row value.
For TimePeriodDate = 6/1/2016 I do not know the value of the previous month until I get the data for the 1 before.
I was thinking somehow that can be created in memory.
This problem has since been solved and rewritten differently than how it was before, but I still am very curious to see if set base is possible here. For my learning.
This is very small set only 4 records, but image 200 records with NULL rent but the very first observation is known.
IF OBJECT_ID('dbo.RentGrowth', 'u') IS NOT NULL
DROP TABLE dbo.RentGrowth
CREATE TABLE dbo.RentGrowth
(
PropertyID INT
,TimePeriodDate DATETIME
,Rent INT
,Growth DECIMAL(6,2)
)
INSERT INTO dbo.RentGrowth
SELECT 1, '20160301', 1200, 1.2
UNION
SELECT 1, '20160401', NULL, 1.3
UNION
SELECT 1, '20160501', NULL, 1.5
UNION
SELECT 1, '20160601', NULL, 1.6
SELECT *
FROM dbo.RentGrowth
/*****expected output**************
13/1/201612001.2
14/1/201614401.3
15/1/201618201.5
16/1/201629121.6
***********************************/
August 4, 2016 at 11:27 pm
Due to the way you have to calculate the current rent, I think your only option is to use a cursor (Yeah, I know, I should probably wash my own mouth out with soap for saying that!).
Try this and see what you get (For a second, it looks like it will work, but you can't carry the values forward, so it fails.)
SELECT a.PropertyID
, a.TimePeriodDate
, a.PrevRent * a.PrevGrowth As ThisRight
FROM (SELECT PropertyID
, TimePeriodDate
, LAG(Rent,1,0) OVER (ORDER BY TimePeriodDate) AS PrevRent
, LAG(Growth,1,0) OVER (ORDER BY TimePeriodDate) AS PrevGrowth
FROM dbo.RentGrowth) a;
A cursor (yeah, dirty word) would at least let you accumulate the growth etc and multiply (kind of like Excel). My little mind can't figure out how to do it, though. (For a second I was thinking of pivoting the Growth values and then multiplying, but I don't think that would work either.
August 5, 2016 at 5:20 am
I'm no sure but i believe there is a calculation issue in your expected output.
/*****expected output**************
13/1/201612001.2
14/1/201614401.3
15/1/201618201.5 ----- This Should be 1440 * 1.3 = 1872
16/1/201629121.6 ----- This Should be 1872 * 1.5 = 2808
***********************************/
or is there something which i am missing?
August 5, 2016 at 5:50 am
Or dust off your school maths book and use logarithms!WITH Logs AS (
SELECT
PropertyID
,TimePeriodDate
,MIN(Rent) OVER (PARTITION BY PropertyID) AS StartRent
,Growth
,SUM(LOG(Growth)) OVER (
PARTITION BY PropertyID
ORDER BY TimePeriodDate
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS LogofGrowth
FROM dbo.RentGrowth
)
SELECT
PropertyID
,TimePeriodDate
,COALESCE(StartRent * EXP(LogofGrowth),StartRent)
,Growth
FROM Logs
John
August 5, 2016 at 6:24 am
There is a way to do this, assuming a specific clustered index can be added to the table. It relies on the infamous 'Quirky Update':
USE Testing;
GO
IF OBJECT_ID('dbo.RentGrowth', 'u') IS NOT NULL
DROP TABLE dbo.RentGrowth;
CREATE TABLE dbo.RentGrowth
(
PropertyID INT
,TimePeriodDate DATETIME
,Rent INT
,Growth DECIMAL(6, 2)
);
INSERT INTO dbo.RentGrowth
SELECT 1
, '20160301'
, 1200
, 1.2
UNION
SELECT 1
, '20160401'
, NULL
, 1.3
UNION
SELECT 1
, '20160501'
, NULL
, 1.5
UNION
SELECT 1
, '20160601'
, NULL
, 1.6;
CREATE CLUSTERED INDEX ix_PropertyId_TimePeriodDate ON dbo.RentGrowth(PropertyID, TimePeriodDate);
GO
SELECT *
FROM dbo.RentGrowth;
/*****expected output**************
13/1/201612001.2
14/1/201614401.3
15/1/201618201.5
16/1/201629121.6
***********************************/
DECLARE @Rent INT;
DECLARE @PreviousPropertyId INT = -1;
DECLARE @PreviousGrowth DECIMAL(6, 2) = 1;
UPDATE rg
SET @Rent = rg.Rent = CASE WHEN rg.PropertyID = @PreviousPropertyId THEN @Rent * @PreviousGrowth
ELSE rg.Rent
END
, @PreviousGrowth = rg.Growth
, @PreviousPropertyId = rg.PropertyID
FROM dbo.RentGrowth rg WITH (TABLOCKX)
OPTION (MAXDOP 1);
SELECT *
FROM dbo.RentGrowth;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 5, 2016 at 8:46 am
Thank you all!
Love the EXP of the Log solution John. I do need to brush up on my math:)
The Query update works too, actually this one I attempted but failed so glad to see you provide a working solution Phil!
Sorry about the horrible expected results. They are wrong, but luckily that was not really the point.
On a side note, going to be prototyping some R with SQL Server 2016 to see how that fairs with predicting rents and filling in unknown values.
Will see how that goes.
August 7, 2016 at 11:58 pm
Sorry about the horrible expected results. They are wrong, but luckily that was not really the point.
Well, Most of the time that is really the point.
On a side note, going to be prototyping some R with SQL Server 2016 to see how that fairs with predicting rents and filling in unknown values.
Will see how that goes.
Best of luck with it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply