May 14, 2002 at 1:33 pm
How would you go about adding a value to records with missing information.
What I’m trying to do is fill in a delta value for missing values over time.
For example, say I have a list of records (sorted by date) as follows…
NAME ............ YEAR_MONTH ....... VALUE
Product_1 ....... 200101 .............. $1.00
Product_1 ....... 200102 .............. $1.10
Product_1 ....... 200103 .............. $1.25
Product_1 ....... 200104 .............. $1.44
Product_1 ....... 200105
Product_1 ....... 200106
Product_1 ....... 200107 .............. $1.78
Product_1 ....... 200108 .............. $1.65
Product_1 ....... 200109 .............. $1.52
Product_1 ....... 2001010
Product_1 ....... 2001011 .............. $1.40
Product_1 ....... 2001012 .............. $1.45
To do this, you find the first Null value (200105), then look at the previous month’s value, THEN find the next record with a value and use it’s value to calculate a delta which will be used to fill in the blank cells.
Using my example, Product_1 for 200105 is the first null value. The 200104 value is 1.44. The next record with a value is 200107 with a value of 1.78. Delta is defined as (x2-x1) / (n+1), thus (1.78 – 1.44) / (2 + 1) = .113333333. Now take the first non-null value (1.44) and add the delta to it to fill in the first blank, thus 200105’s value becomes 1.553333333. Add another delta value (.113333333) to the current value of 1.553333333 to get the value to place into 200106 which is 1.66666667. If you add another delta to this figure, you will come up with the actual value contained in 200107 (1.78).
How would I do this type of procedure in SQL Server?
Thanks,
Brian
May 14, 2002 at 3:36 pm
So I can make sure I get this right the expression defined by
(x2-x1) / (n+1)
what does n represent? Is it the number of missing days, which I assume but want to be sure.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 14, 2002 at 4:08 pm
Delta = (x2-x1) / (n+1)
where x2 = value in next non-null record;
x1 = value in previous record before null value record;
n = number of records with null values. The null values in these records will be filled with a calculated value by adding the delta to the value in the previous record.
May 15, 2002 at 7:09 am
This should do the trick. There are comments thru the code to explain what is happening and 2 notes you need to look at before running.
---------------------------------BEGIN CODE---------------------------------
/* Output the original data for testing to check before and after transaction and rollback. */
SELECT * FROM xtest
/* Delcare or variables. */
DECLARE @cntmissINT
DECLARE @curmissINT
DECLARE @curdateINT
DECLARE @enddateINT
DECLARE @x1MONEY
DECLARE @x2MONEY
DECLARE @premoneyMONEY
/* Set a transaction start pojnt so we can rollback for testing. */
BEGIN TRANSACTION
/* Loop until all records are filled in. */
WHILE EXISTS(SELECT [NAME] FROM xtest WHERE VALUE IS NULL)
BEGIN
/* Default the varibales @curmiss will occurr later. */
SET @cntmiss = 0
SET @curdate = 0
SET @enddate = 0
SET @x1 = 0
SET @x2 = 0
SET @premoney = 0
/* Get the first date that is null. */
SELECT TOP 1 @curdate = YEAR_MONTH FROM xtest WHERE VALUE IS NULL
/* Get the last null value before the first non-null value after the first null value. */
SELECT TOP 1 @enddate = YEAR_MONTH FROM xtest WHERE YEAR_MONTH < (SELECT TOP 1 YEAR_MONTH FROM xtest WHERE VALUE IS NOT NULL AND YEAR_MONTH > @curdate) ORDER BY YEAR_MONTH DESC
/* Determine how many null records in a row we have. */
SELECT @cntmiss = COUNT(*) FROM xtest WHERE YEAR_MONTH BETWEEN @curdate AND @enddate
/* Get the values of the first non-null value after our first null. */
SELECT TOP 1 @x1 = VALUE FROM xtest WHERE VALUE IS NOT NULL AND YEAR_MONTH > @curdate
/* NOTE: You will want to put a check for @x1 here to make sure an after date exists for the last null found or handkey the last null before running script. */
/* Get the values of the first non-null value before our first null. */
SELECT TOP 1 @x2 = VALUE FROM xtest WHERE VALUE IS NOT NULL AND YEAR_MONTH < @curdate ORDER BY YEAR_MONTH DESC
/* NOTE: You will want to put a check for @x2 here to make sure a before date exists for the first null found or handkey the first value before running script. */
/* @Premoney will help use calc the value set equal to the @x2 variable to begin with. */
SET @premoney = @x2
/* Default @curmiss. */
SET @curmiss = 1
/* Loop for number of nulls in a row. */
WHILE @curmiss <= @cntmiss
BEGIN
/* Check if the value increased or decreased. */
IF @x1 > @x2
BEGIN
/* Increase found so calc an increased delta value. */
SET @premoney = @premoney + ((@x1 - @x2) / (@cntmiss + 1))
END
ELSE
BEGIN
/* Decrease found so calc a decreased delta value. */
SET @premoney = @premoney - ((@x2 - @x1) / (@cntmiss + 1))
END
/* Update our null record with the delta value. */
UPDATE xTest SET VALUE = @premoney WHERE YEAR_MONTH = @curdate
/* Move our @curdate value to the next null value in a row. */
SELECT @curdate = YEAR_MONTH FROM xTest WHERE YEAR_MONTH > @curdate AND YEAR_MONTH <= @enddate
/* Increase our @curmiss so we don't exceed the @cntmiss in the loop. */
SET @curmiss = @curmiss + 1
END
END
/* See the results. */
SELECT * FROM xtest
/* Undo everything we just did, this is a test saftey. */
ROLLBACK TRANSACTION
/* Make sure original records are back (take a look). */
SELECT * FROM xtest
/*
(x2-x1) / (n+1)
*/
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 15, 2002 at 7:10 am
Sorry xTest is where you should sub your table name.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply