Replace NULL value with proper date value from next row

Question

I have one table with 3 columns,
Here is the table structure

ID Int  ,Description Varchar(10),  Date datetime

ID  Description EffectiveDate
1       XYZ        Null
1       ABC        02/12/2001
2       DEF        03/14/2002

I need output to be , remove null and assign 2nd row date in first row

ID  Description EffectiveDate
1       XYZ        02/12/2001
2       DEF        03/14/2002

Its very urgent , please send me SQL code for it.

Thanks in Advance,

RH

---

sql

Answer 1

What about that data would cause you to select XYZ instead of ABC as Description?

---

Chuck Pedretti | Magenic – North Region | magenic.com

Answer 2

Sorry, will doing copy/paste I forgot second row.

Yes I need both rows for ID=1 and also ID=2

ID  Description EffectiveDate
1       XYZ        02/12/2001
1       ABC        02/12/2001
2       DEF        03/14/2002

Thanks,

RH

---

sql

Answer 3

Hi,

This will do it for you - but you may need to tweak it depending on a few things (For example, what if there are 3 ID 1 rows, 1 null, and 2 with 2 different dates, which date should be used - the min or the max?)

;WITH CTE AS (
SELECT 1 ID, 'XYZ' [Description], NULL DateTimeVal UNION ALL
SELECT 1 ID, 'ABC' [Description], '2001-02-12' DateTimeVal UNION ALL
SELECT 2 ID, 'DEF' [Description], '2002-03-14' DateTimeVal)

SELECT ID,
	   [Description],
	   ISNULL(DateTimeVal, (SELECT MIN(DateTimeVal) FROM CTE WHERE ID = ID))

FROM CTE

---

Zach Stagers
MCITP: Database Developer 2008
Remember to Mark as Answer and Vote as Helpful

Answer 4

I think this could resolve your problem...

Try it:

declare @t table ( id int, description varchar(10), date datetime )

insert into @t
select 1, 'XYZ', NULL UNION
select 1, 'ABC', '20011202' UNION
select 2, 'DEF', '20020214' 


select distinct a.id, a.description, isnull(a.date,b.date ) as dat
from @t a
inner join @t b on a.id = b.id 
where isnull(a.date,b.date ) is not null

---

Sergio Sánchez Arias
AYÚDANOS A AYUDARTE

Answer 5

Hello,

Is this what you want?

With AggData
As
(
Select ID,MAX(EffectiveDate) As EffectiveDate
From YourTable
Group By ID)

Select A.ID,A.Description,ISNULL(A.EffectiveDateB=,B..EffectiveDate) As EffectiveDate
From YourTable A Join AggData B
On A.ID = B.ID

Answer 6

SELECT T.Id, T.[Description], ISNULL(T.DateTimeVal,T1.DateTimeVal) as DateTimeVal
FROM test T
OUTER APPLY (select top (1) DateTimeVal from test T1 where T1.Id = T.Id
and T1.[Description] < T.[Description] and T1.DateTimeVal IS NOT NULL
ORDER BY [Description] DESC) T1

Answer 7

Hi sql9

Not to come from out of no where but if you want lets day all "ID_1" with a "NULL" to be updated to a certain date. Then maybe:

declare @table table (id int , description varchar(10), dt datetime)
insert into @table values
(1,'XYZ',NULL),
(1,'ABC','02/12/2001'),
(2,'DEF','03/14/2002')

update @table set dt = '02/12/2001' where id = 1
-- you can also set date = max date of the id using a join
-- ill let you play with that or maybe ill include it later

select * from @table

---

Pérez

Answer 8

select ID, max(Description) as Description, Max(EffectiveDate) as EffectiveDate

from myTable

GROUP BY Id

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 9

In this case:

SELECT T.Id, T.Description, COALESCE(T.EffectiveDate, NextRow.EffectiveDate) as EffectiveDate
FROM myTable T 
CROSS APPLY (select top (1) EffectiveDate from myTable T2 
where T2.ID = T.ID and T2.EffectiveDate IS NOT NULL 
ORDER BY EffectiveDate) NextRow

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog