CODEWITHSUNDEEP
sql-server
Rajesh
Rajesh

Reputation: 1620

Get the value of above row based on a condition

I get a table data like below

pcontact    ccontact    RowOrder    cdatetime               pdatetime
12345678    14121212    1           2021-03-15 10:01:11.497 2021-03-15 09:57:32.803
12345678    14121213    2           2021-03-15 10:04:51.967 2021-03-15 09:57:32.803
12345678    14121214    3           NULL                    2021-03-15 09:57:32.803

If the value of cdatetime is NULL then I am trying to take value above it, I had tried to use CASE statement for it but I am not aware how to proceed further

Please any one point me in the right direction

Upvotes: 0

Views: 29

Answers (2)

Stu
Stu

Reputation: 32579

A simple solution is to use the lag function, such as

select isnull(cdatetime, lag(cdatetime,1) over (order by RowOrder)) as cdatetime
from table

Upvotes: 1

Kuanhung Chen
Kuanhung Chen

Reputation: 1

Without knowing what you are trying to accomplish, have you tried use TOP?

Rather than: SELECT * FROM [Table] BY [ID] = 'some value'

Try: SELECT TOP (1) FROM [Table] WHERE [ID] <= 'some value' AND [cdatetime] IS NOT NULL ORDER BY [ID] DESC

Just a thought.

Upvotes: 0

Related Questions