Get the next value - 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)

Get the next value

Author

Topic

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-11-13 : 10:42:29

I have follow table:


K1 | DT                     | Value
----------------------------------------------
1  | 2010-01-01 13:00:00    | 100
2  | 2010-01-01 14:00:00    | 200
3  | 2010-01-01 15:00:00    | 300
4  | 2010-01-01 16:00:00    | 400
5  | NULL                   | NULL
6  | NULL                   | NULL
7  | NULL                   | NULL
8  | 2010-01-01 22:00:00    | 1300
9  | NULL                   | NULL
10 | 2010-01-02 13:00:00    | 900

And want to fill out the NULL value in Value-field
with the next value that is "not null".

so its looks like


K1 | DT                     | Value
----------------------------------------------
1  | 2010-01-01 13:00:00    | 100
2  | 2010-01-01 14:00:00    | 200
3  | 2010-01-01 15:00:00    | 300
4  | 2010-01-01 16:00:00    | 400
5  | NULL                   | 1300
6  | NULL                   | 1300
7  | NULL                   | 1300
8  | 2010-01-01 22:00:00    | 1300
9  | NULL                   | 900
10 | 2010-01-02 13:00:00    | 900

note:
It doesn't matter here if the LogTime field has an DateTime or not.
But the important is to get the next nearest "not null" value.

How can that been done with a UPDATE-SET query ??

Thx!

Sachin.Nand

2937 Posts

Posted - 2010-11-13 : 11:55:04

select *,colesce(t1.value,t.value)newvalue from yourtable t1
cross apply(select top 1 value from yourtable t2 where t2.id>t1.id and t2.value is not null order by t2.K1)t

PBUH

voyager838
Yak Posting Veteran

90 Posts

Posted - 2010-11-13 : 13:26:41

quote:
Originally posted by Sachin.Nand

select *,colesce(t1.value,t.value)newvalue from yourtable t1
cross apply(select top 1 value from yourtable t2 where t2.id>t1.id and t2.value is not null order by t2.K1)t

PBUH

Thanks!

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-11-14 : 19:01:09

quote:

select *,colesce(t1.value,t.value)newvalue from yourtable t1
cross apply(select top 1 value from yourtable t2 where t2.id>t1.id and t2.value is not null order by t2.K1)t

Red part may be a place for bugs....

Using max to fix bugs and better performance....

Sachin.Nand

2937 Posts

Posted - 2010-11-15 : 03:20:51

I wonder why will it be a BUG ?

PBUH

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-15 : 12:34:49

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

If you want to learn how to ask a question on a Newsgroup, look at: http:((www.catb.org(~esr(faqs(smart-questions.html

Here is my guess at your DDL.

CREATE TABLE Samplings
(sample_nbr INTEGER NOT NULL PRIMARY KEY,
sample_time DATETIME,
sample_value INTEGER);

INSERT INTO Samplings
VALUES
(1, '2010-01-01 13:00:00', 100),
(2, '2010-01-01 14:00:00', '200).
(3, '2010-01-01 15:00:00', 300),
(4, '2010-01-01 16:00:00', 400),
(5, NULL, NULL),
(6, NULL, NULL),
(7, NULL, NULL),
(8, '2010-01-01 22:00:00', 1300),
(9, NULL, NULL),
(10, '2010-01-02 13:00:00', 900);

Here is one way. It just moves the data up one row at a time. You can put it in a loop, ifyou wish or just run it until nothing is updated.

UPDATE Samplings
SET sample_value
= CASE WHEN sample_value IS NOT NULL
THEN sample_value
ELSE (SELECT S2.sample_value
FROM Samplings AS S2
WHERE Samplings.sample_nbr = S2.sample_nbr + 1)
END;

Now here is the fancy version and it is hard to follow. First creaet a VIEW that looks ahead for non-NULL values. The CTE finds all the forward non-NULL rows and the body of the VIEW filters out the first one, based on teh sample-nbr. Run it alone, so you understand it.

CREATE VIEW XXX(current_sample_nbr, sample_value)
AS
WITH X (current_sample_nbr, following_sample_nbr, sample_value)
AS
(SELECT S1.sample_nbr, S2.sample_nbr, S2.sample_value
FROM Samplings AS S1, Samplings AS S2
WHERE S1.sample_nbr < S2.sample_nbr
AND S1.sample_value IS NULL
AND S2.sample_value IS NOT NULL)

SELECT X1.current_sample_nbr, X1.sample_value
FROM X AS X1
WHERE X1.following_sample_nbr
= (SELECT MIN(following_sample_nbr)
FROM X AS X2
WHERE X1.current_sample_nbr = X2.current_sample_nbr)
GO

Finally use a MERGE to update the rows that showed up in XXX.

MERGE INTO Samplings
USING XXX
ON XXX.current_sample_nbr = Samplings.sample_nbr
WHEN MATCHED
THEN UPDATE
SET sample_value = XXX.sample_value;
GO

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources