CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2460074
user2460074

Reputation: 1272

add column based on a column value in one row

I've this table with the following data

user     Date        Dist     Start
 1     2014-09-03     150       12500
 1     2014-09-04     220       null
 1     2014-09-05     100       null
 2     2014-09-03     290       18000
 2     2014-09-04     90        null
 2     2014-09-05     170       null

Based on the value in Start Column i need to add another column and repeat the value if not null for the same user The resultant table should be as below

 user     Date        Dist     Start            StartR
 1     2014-09-03     150       12500          12500
 1     2014-09-04     220       null           12500
 1     2014-09-05     100       null           12500
 2     2014-09-03     290       18000          18000
 2     2014-09-04     90        null           18000
 2     2014-09-05     170       null           18000

Can someone please help me out with this query? because i don't have any idea how can i do it

Upvotes: 0

Views: 73

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460268

You can use COALESCE/ISNULL and a correlated sub-query:

SELECT [user], [Date], [Dist], [Start],
       StartR = ISNULL([Start], (SELECT MIN([Start])
                                 FROM dbo.TableName t2
                                 WHERE t.[User] = t2.[User]
                                 AND  t2.[Start] IS NOT NULL))
FROM dbo.TableName t

I have used MIN([Start]) since you haven't said what should happen if there are multiple Start values for one user that are not NULL.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

For the data you have, you can use a window function:

select t.*, min(t.start) over (partition by user) as StartR
from table t

You can readily update using the same idea:

with toupdate as (
      select t.*, min(t.start) over (partition by user) as new_StartR
      from table t
     )
update toupdate
    set StartR = new_StartR;

Note: this works for the data in the question and how you have phrased the question. It would not work if there were multiple Start values for a given user, or if there were NULL values that you wanted to keep before the first non-NULL Start value.

Upvotes: 1

Related Questions