Need non blank,non null value from table to update same table.

Question

Post reply

Need non blank,non null value from table to update same table.

raghava_tg

Ten Centuries

Points: 1014
More actions
March 13, 2015 at 9:08 am

#295134

hi sql folks,
here is the sample data for table.
create table #sample (emp varchar(max),data1 varchar(max),data2 varchar(max), Rdate date)
insert into #sample (emp,data1,data2,rdate)
values('john','','',getdate())
insert into #sample (emp,data1,data2,rdate)
values('tony','','',getdate())
insert into #sample (emp,data1,data2,rdate)
values('john','','',getdate()-1)
insert into #sample (emp,data1,data2,rdate)
values('tony','','',getdate()-1 )
insert into #sample (emp,data1,data2,rdate)
values('john','c','d',getdate()-2)
insert into #sample (emp,data1,data2,rdate)
values('tony','a','b',getdate()-2 )
select * from #sample
order by rdate desc
-----------------------------------------------------------------------
i need to update the data1, data2 from yesterday or before but they should be non blank and non null and update to todays data.
it means, i can't update to 2015-03-12 values to 2015-03-13, as they are blanks or nulls.
so i need to go 2015-03-11 and check the values, if they are not blank , i need to update with todays values.
Before the update :
empdata1data2Rdate
john3/13/2015
tony3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015
After the update:
empdata1data2Rdate
johncd3/13/2015
tonyab3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

-- Check

WITH SequencedData AS (

SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY emp ORDER BY rdate DESC)

FROM #sample

)

SELECT *

FROM SequencedData s

CROSS APPLY (SELECT TOP 1 * FROM #sample si WHERE si.emp = s.emp AND data1 > '' AND data2 > '' ORDER BY rdate DESC) x

WHERE rn = 1;

-- Update

WITH SequencedData AS (

SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY emp ORDER BY rdate DESC)

FROM #sample

)

UPDATE s SET

data1 = x.data1, data2 = x.data2

FROM SequencedData s

CROSS APPLY (SELECT TOP 1 * FROM #sample si WHERE si.emp = s.emp AND data1 > '' AND data2 > '' ORDER BY rdate DESC) x

WHERE rn = 1;

SELECT * FROM #sample

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

raghava_tg Ten Centuries Points: 1014 More actions · Answer 2

hi ,

it fullfilled my requirement. but not able to understand cross apply with x symbol .

pls explain if dont mind.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

APPLY has been a part of TSQL since 2005. Read more here:

http://www.sqlservercentral.com/articles/APPLY/69953/[/url]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

raghava_tg Ten Centuries Points: 1014 More actions · Answer 4

checking chris.

thanks for the quick help.

See you soon..