Replace NULLS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Replace NULLS

Author

Topic

VijayV
Starting Member

4 Posts

Posted - 2011-11-09 : 14:18:39

There is a requirement to replace the NULLS in the table and the it has to be done only if certain conditions meet.

The data in the table is for every 15 minutes in a given day and it looks somewhat like this

HGWY DISTANCE 0000 0015 0030 0045 0100 0115......
1 69.27 45 68 65
2 69.6 68 NULL 45
3 69.98 60 NULL 55
4 70.34 NULL 65 35
5 70.68 67 72 56
6 70.97 65 62 NULL
7 74.41 70 80 75

*The NULL value needs to be replaced only if the difference in distance between previous and next NON NULL value is less than 2.

*The NULL value should be replaced by the average of NEXT and PREVIOUS NON NULL Values.

so for the above the output should look like this
HGWY DISTANCE 0000 0015 0030
1 69.27 45 68 65
2 69.6 68 66.5(68+65/2) 45
3 69.98 60 66.5 (68+65/2) 55
4 70.34 63.5(60+67/2) 65 35
5 70.68 67 72 56
6 70.97 65 62 NULL
7 74.41 70 80 75

The NULL value in column 0030 was not replaced because the difference in distance between HGWY 7 and 5 was > 2(74.41-70.98 = 3.43)

Since in a given day there are 96 15 minutes intervals or 96 columns in total, the performance is really an issue.

Thanks for help in advance

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 16:16:11

What do you mean by Previous? That is not a database concept. The order of rows in a database is meaningless, unless you can relate them somehow to each other

Define Previous

And for pete's sake, normalize your data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

VijayV
Starting Member

4 Posts

Posted - 2011-11-09 : 18:30:02

Brett,

The rows are ordered in ascending order of Distance.


HGWY  DISTANCE   0000   0015 0030....
1        69.27    45
2        69.6     68
3        69.98    60
4        70.34    NULL ---(60+67/2)=63.5
5        70.68    67
6        70.8     65
7        70.97    65
8        71.5     NULL --Should not be replaced since distance (75.0 - 70.97 > 2)
9        73.0     NULL --Should not be replaced since distance (75.0 - 70.97 > 2)
10       75.0     70

The NULL in 0000 has to be replaced with Average of previous NON NULL value in the same column (60) and NEXT NON NULL value (67), so 60 + 67/2 = 63.5.

Also the difference in distances should be less than 2. For the first NULL the difference in Distance is 70.68 - 69.98 < 2. So, should be replaced

The NULLS in second case should not be replaced since difference in Distances for NON NULL values 75.0 - 70.97 > 2.

I hope i was clear.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-09 : 20:44:47

This is meaningless..but I'm sure you could do it

So find all the null values, then for each null row, find the first non null row that is less than your miles, then find the first non null value that is above that..right?

Then applyyor less than 2 row and apply you r calculation

Right???

No freaking meaning

Who is asking you to do this, or is it a class homework assignment, or did you think of this on your own?

What Value does this derived data have by the way??

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 00:57:39

[code]
UPDATE t
SET [0000]= (t1.[0000] + t2.[0000])/2.0
FROM table t
CROSS APPLY (SELECT TOP 1 DISTANCE,[0000]
FROM table
WHERE DISTANCE < t.DISTANCE
AND [0000] IS NOT NULL
ORDER BY DISTANCE DESC)t1
CROSS APPLY (SELECT TOP 1 DISTANCE,[0000]
FROM table
WHERE DISTANCE > t.DISTANCE
AND [0000] IS NOT NULL
ORDER BY DISTANCE ASC)t2
WHERE [0000] IS NULL
AND (t2.DISTANCE - t1.DISTANCE) < 2.00
[/code]
similar logic can be applied for others too

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

VijayV
Starting Member

4 Posts

Posted - 2011-11-10 : 09:30:49

Thanks Visakh that worked for me. Appreciate your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 09:39:10

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-10 : 11:52:13

quote:
Originally posted by visakh16

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Ask him what value that has?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

VijayV
Starting Member

4 Posts

Posted - 2011-11-10 : 13:41:19

Brett,

The values captured in that table are the average speeds along a highway and the distances are the exists. The restriction distance < 2 was applied not to replace NULL with an average value from exits far apart.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources