How to Update a field that using the most recent value to replace the null value?
-
For example:
PersonID Date1 A A 01/01/2004 A 01/15/2004 1 A 01/19/2004 A 01/30/2004 0 A 02/05/2004 B 01/03/2004 0 B 01/09/2004 B 01/19/2004 The updated table should be like this:
PersonID Date1 A A 01/01/2004 A 01/15/2004 1 A 01/19/2004 1 A 01/30/2004 0 A 02/05/2004 0 B 01/03/2004 0 B 01/09/2004 0 B 01/19/2004 0 -
Please try this :
set nocount on
create table dbo.tst(PersonID varchar(30) not null, adate datetime not null, anint int null )
insert dbo.tst (PersonID,adate) values ('A','20040101')
insert dbo.tst (PersonID,adate,anint) values ('A','20040115',1)
insert dbo.tst (PersonID,adate) values ('A','20040119')
insert dbo.tst (PersonID,adate,anint) values ('A','20040130',1)
insert dbo.tst (PersonID,adate) values ('A','20040205')
insert dbo.tst (PersonID,adate,anint) values ('B','20040103',0)
insert dbo.tst (PersonID,adate) values ('B','20040109')
insert dbo.tst (PersonID,adate) values ('B','20040119')
select * from dbo.tst order by PersonID,adate,anint
update dbo.tst
set anint = t2.anint
from dbo.tst , dbo.tst t2
where dbo.tst.PersonID = t2.PersonID
and t2.adate < dbo.tst.adate
and t2.anint is not null
and dbo.tst.anint is null
select * from dbo.tst order by PersonID,adate,anint
-
This doesn't work according to the example.
Note that the 'A-person' id has both 1 and 0 as values for 'A', but on different dates. This probably means that each column A that is null, should get the value of column A with the most recent date before said null, but only for rows (dates) until the value of A changes.
A 01/15/2004 1
A 01/19/2004 <= update to 1
A 01/30/2004 0 <= leave as is - marks a new "time-boundry"
A 02/05/2004 <= update to 0
At least this is how I read the intentions of the poster. I'm not quite clear if this is correct, however...
/Kenneth
-
For me it works fine
INPUT :
PersonID adate anint A 2004-01-01 00:00:00.000 A 2004-01-15 00:00:00.000 1 A 2004-01-19 00:00:00.000 A 2004-01-30 00:00:00.000 2 A 2004-02-05 00:00:00.000 B 2004-01-03 00:00:00.000 0 B 2004-01-09 00:00:00.000 B 2004-01-19 00:00:00.000 and after the update :
PersonID adate anint A 2004-01-01 00:00:00.000 A 2004-01-15 00:00:00.000 1 A 2004-01-19 00:00:00.000 1 A 2004-01-30 00:00:00.000 2 A 2004-02-05 00:00:00.000 2 B 2004-01-03 00:00:00.000 0 B 2004-01-09 00:00:00.000 0 B 2004-01-19 00:00:00.000 0 -
Well, again, I'm not sure that I have understood the entire problem posed here, I'm so far just assuming what the poster's intentions are.
In any case, your input/output doesn't correlate to your example script. (there are no 2's in that)
If the intention was to replicate the original example data given, a correction is needed.
insert dbo.tst (PersonID,adate) values ('A','20040101')
insert dbo.tst (PersonID,adate,anint) values ('A','20040115',1)
insert dbo.tst (PersonID,adate) values ('A','20040119')
insert dbo.tst (PersonID,adate,anint) values ('A','20040130',1) <= should be 0, not 1
insert dbo.tst (PersonID,adate) values ('A','20040205')
insert dbo.tst (PersonID,adate,anint) values ('B','20040103',0)
insert dbo.tst (PersonID,adate) values ('B','20040109')
insert dbo.tst (PersonID,adate) values ('B','20040119')
so, the adjusted data would look like;
insert dbo.tst (PersonID,adate) values ('A','20040101')
insert dbo.tst (PersonID,adate,anint) values ('A','20040115',1)
insert dbo.tst (PersonID,adate) values ('A','20040119')
insert dbo.tst (PersonID,adate,anint) values ('A','20040130',0)
insert dbo.tst (PersonID,adate) values ('A','20040205')
insert dbo.tst (PersonID,adate,anint) values ('B','20040103',0)
insert dbo.tst (PersonID,adate) values ('B','20040109')
insert dbo.tst (PersonID,adate) values ('B','20040119')
which gives this example data (as originally given by the poster)
-- adjusted input according to example
PersonID adate anint
-------- ----------- -----------
A 2004-01-01 NULL
A 2004-01-15 1
A 2004-01-19 NULL
A 2004-01-30 0
A 2004-02-05 NULL
B 2004-01-03 0
B 2004-01-09 NULL
B 2004-01-19 NULL
After running the update, you get this result;
-- output from adjusted input according to example
PersonID adate anint
-------- ----------- -----------
A 2004-01-01 NULL
A 2004-01-15 1
A 2004-01-19 1
A 2004-01-30 0
A 2004-02-05 1 <= should be 0
B 2004-01-03 0
B 2004-01-09 0
B 2004-01-19 0
Anyway... again, not quite sure what the poster really wants, how the example data relates, and it might even be a typo in there from the beginnig..
We'll have to wait for a more detailed explanation perhaps.
/Kenneth
-
You are right. We'll have to wait for a more detailed explanation. A cursor will be probably the easiest way to solve this, but I do not like cursors ... Let's wait ...
-
Once you fix the input the output worked fine for me, although I believe it is succeptable to access path variations and I don't think it will work all the time. The reason is that it needs to use not just any previous value, but the most recent previous value. The following update should work.
UPDATE dbo.tst
SET anint = ( SELECT t2.anint
FROM dbo.tst t2
WHERE t2.PersonId = t1.PersonId
AND t2.adate = ( SELECT MAX ( t3.adate )
FROM dbo.tst t3
WHERE t3.adate < t1.adate
AND t3.PersonId = t1.PersonId
AND t3.anint IS NOT NULL ) )
FROM dbo.tst t1
WHERE t1.anint IS NULL
-
Question Detail:
Kenneth Wilhelmsson's explaining is correct and more cleaar. The A column's value doesn't matter, if there's a value, leave it, if none, replace it with most recent one.
This question is used for patient's record. Every time a patient go to hospital, ususally he need fill some forms. Later someone will check it, if a field was found blank, then they will check the record history, get the most recent one to fill the field. If the field never filled before, leave it blank.
Thanks for replying. I'll check them all.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply