CODEWITHSUNDEEP
sql-servert-sqlnullcursorcommon-table-expression
StelioK
StelioK

Reputation: 1781

T-SQL Update Null Values With Previous values (not necessarily immediately before) based on seat location, ID and Time

I am using MSSMS 2008 R on win7 machine.

I am extremely new to SQL and am still trying to grasp the fundamentals, it is very different than what I have used in the past.

I am having some issues trying to get my hands on some data. Here is what it looks like after I munged the heck out of it (this is just a small set).

enter image description here

I need to search for null values in my MediaId col. (which is straight forward enough).

Once I have found these values, I need to search back (in time) for the same location and logID that occurred right before the null values' time-stamp and replace that null with the correct mediaID.

I have a solid understanding of the problem and how to approach (in most languages) but SQL is weird and giving me problems... It took me forever to get my data to this stage but I have NO IDEA where to go from here. I have researched CTE's and Cursors but am still unsure how to apply these methods.

BTW Sorry but I cannot post any of my code due to the nature of my work, I think this is enough info for you to ponder.

Upvotes: 1

Views: 1100

Answers (5)

shadow
shadow

Reputation: 1903

A full example:

-- delete test table
drop table mediaTable
go

-- create test table
create table mediaTable (
    [Time] DateTime not null,
    Location varchar(15) not null,
    logId int not null,
    mediaId int null
)
go

-- add some test data
insert into mediaTable values 
(getdate(), '12K', 234499, 2018334),
(dateadd(day,1,getdate()), '12K', 234499, 100),
(dateadd(day,1,getdate()), '58', 234499, -1),
(dateadd(day,2,getdate()), '12K', 234499, null ),
(dateadd(day,3,getdate()), 'ht66', 2001, 10 ),
(dateadd(day,4,getdate()), 'ht66', 2001, 20 ),
(dateadd(day,5,getdate()), 'ht66', 2001, 30 ),
(dateadd(day,6,getdate()), 'ht66', 2001, null )
go



-- show data before update
select * from mediaTable;

-- update data
With cte ( [Time], Location, logId, mediaId ) As (
    Select [Time], Location, logId, mediaId From mediaTable Where ( mediaId Is Null )
)
Update mediaTable Set
    mediaId = b.mediaId
From
    mediaTable As a
    Inner Join cte As c On ( a.Location = c.Location ) And ( a.logId = c.logId )
    Cross Apply (
        Select Top 1 mediaId From mediaTable Where ( Location = c.Location ) And ( logId = c.logId ) And ( MediaId Is Not Null ) Order By [Time] Desc
    ) As b
Where
    ( a.mediaId Is Null )

-- show data after update
select * from mediaTable;

Edit This seems to be faster:

Update a Set
    a.mediaId = b.mediaId
From
    #mediaTable As a    
    Cross Apply (
        Select Top 1 mediaId 
        From #mediaTable 
        Where 
            ( Location = a.Location ) 
            And ( logId = a.logId ) 
            And ( a.mediaId Is Null ) 
            And ( mediaId Is Not Null ) 
        Order By [Time] Desc
    ) As b

Upvotes: 0

dee.ronin
dee.ronin

Reputation: 1038

You can use subquery to update the column with previous value using the code below:

UPDATE t
SET t.mediaId = (
        SELECT TOP 1 
            mediaId 
        FROM
            YourTable s 
        WHERE
            s.LogId = t.LogId
            AND s.Location = t.Location 
            AND s.Time < t.Time 
        ORDER BY 
            [Time] DESC)
FROM 
    YourTable t
WHERE
    t.mediaId IS NULL

Upvotes: 1

knkarthick24
knkarthick24

Reputation: 3216

One other way of doing the same:

SELECT A.locationid, 
       A.logid, 
       A.time, 
       COALESCE(A.mediaid, B.mediaid) AS MediaID 
FROM   tbl A 
       JOIN (SELECT locationid,logid,time,mediaid    
             FROM   (SELECT Row_number() 
                              OVER(partition BY locationid, logid ORDER BY time DESC) AS rn,*                                
                     FROM   tbl 
                     WHERE  mediaid IS NOT NULL)t 
             WHERE  t.rn = 1)B 
         ON A.locationid = B.locationid 
            AND A.logid = B.logid

Upvotes: 0

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use CROSS APPLY and TOP :

SELECT
    t.*,
    NewMediaId = x.mediaId
FROM tbl t
CROSS APPLY(
    SELECT TOP 1 mediaId
    FROM tbl
    WHERE
        LogId = t.LogId
        AND Location = t.Location
        AND mediaId IS NOT NULL
    ORDER BY Time DESC
)x

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270773

If you do not have consecutive NULL values (and are using SQL Server 2012+), then you can use lag():

select time, location, logid,
       coalesce(mediaid,
                lag(mediaid) over (partition by location, logid, time) 
               ) as mediaid
from t;

If you have up to two NULL values in a row, you can use a simple modification:

select time, location, logid,
       coalesce(mediaid,
                lag(mediaid) over (partition by location, logid, time),
                lag(mediaid, 2) over (partition by location, logid, time)
               ) as mediaid
from t;

Beyond that, you then wish that SQL Server with implement the IGNORE NULLS option on lag(). Barring that (or in earlier versions of SQL Server, you can do something like:

select t.time, t.location, t.logid, tprev.mediaid
from t outer apply
     (select top 1 t2.mediaid
      from t t2
      where t2.location = t.location and t2.logid = t.logid and
            t2.time <= t.time and t2.mediaid is not null
      order by t2.time desc
     ) tprev

Upvotes: 0

Related Questions