Backfill Data 2

[briangriffin]

Table looks like this:

ID Patient Procedure
----------------------------------------
1 John CBC
1 CHEM14
1 PSA
2 Sue CBC
2 CHEM14
3 Frank CBC


I need to return this data but fill in the name field for each record (and there are other blank fields that need to be filled in as well).

I could bludgeon this into something workable, but I would bet that there's a much easier way than what I would do. Typically I dump the data into a variable table, then use an alias of that table to do updates on the original.

Thanks for your ideas.

 

[gmmastros]

I would approach this using a single query, like this...

Declare @Temp Table(Id Int, Patient VarChar(20), [Procedure] VarChar(20))

Insert Into @Temp Values(1,'John' ,'CBC')
Insert Into @Temp Values(1,''     ,'CHEM14')
Insert Into @Temp Values(1,''     ,'PSA')
Insert Into @Temp Values(2,'Sue'  ,'CBC')
Insert Into @Temp Values(2,''     ,'CHEM14')
Insert Into @Temp Values(3,'Frank','CBC')

Update A
Set    A.Patient = B.Patient
From   @Temp A
       Inner Join (
          Select Id, Max(Patient) As Patient
          From   @Temp
          Group BY Id
          ) As B
          On A.Id = B.Id

Select * From @Temp

Note that I am creating a table variable here, loading it with your sample data, and then using it in the update statement. You can copy/paste this code to a query window so you can run it and see how it works.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[briangriffin]

Well, I sure screwed that up. Yes, that solution works for my description, but in reality each patient does not have a unique ID.

The data really looks like this:

ID Patient Procedure
----------------------------------------
1 John CBC
2 CHEM14
3 PSA
4 Sue CBC
5 CHEM14
6 Frank CBC

Which I think makes this a bit harder - sorry for the inaccuracy.

 

[gmmastros]

No fair. You changed the requirements. You sound like my user now!

Just to be clear, you want the patient names filled in. The row with id 2 and 3 should be John, and 5 should get Sue, correct? Essentially, if the next row (based on the value of the id column) has a blank PatientName, then it should take the PatientName from the row above it, right?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[briangriffin]

Never thought I'd be "that user"...

Yes, you are correct. Every blank name should have the name closest above it. Thanks.

 

[gmmastros]

Yeah... This makes things a little more complicated.

I did come up with a solution that *almost* works. In fact, it does work with your sample data, but it fails for the last patient when there are multiple rows.

Anyway... here's my partial solution. Maybe it will help you.

Declare @Temp Table(Id Int, Patient VarChar(20), [Procedure] VarChar(20))

Insert Into @Temp Values(1,'John' ,'CBC')
Insert Into @Temp Values(2,''     ,'CHEM14')
Insert Into @Temp Values(3,''     ,'PSA')
Insert Into @Temp Values(4,'Sue'  ,'CBC')
Insert Into @Temp Values(5,''     ,'CHEM14')
Insert Into @Temp Values(6,'Frank','CBC')
Insert Into @Temp Values(7,'','CBC')

;With cte As
(
  Select *, Row_Number() Over (Order BY ID) As RowId
  From   @Temp 
  Where  Patient > ''
)
Update T
Set    T.Patient = A.Patient
From   cte As A
       Inner Join cte As B
         On A.RowId = B.RowId - 1
       Inner Join @Temp T 
         On T.ID Between A.Id And B.Id - 1

Select * From @Temp

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Simple solution for SQL 2005 and up:

;with cte as (select T.ID, F.Id as [MatchID], F.Patient from @Temp T 
cross apply (select top (1) * from @Temp 
T1 where T1.Id < T.Id and T1.Patient >'' order by ID DESC) F
where T.Patient ='')

update T set Patient = c.Patient from @Temp T inner join cte c on T.Id = c.Id 
select * from @Temp

PluralSight Learning Library

 

[markros]

Sorry, the solution is simpler - you don't need double join in UPDATE:

;with cte as (select T.ID, T.Patient, F.Id as [MatchID], F.Patient as NewPatient from @Temp T 
cross apply (select top (1) * from @Temp 
T1 where T1.Id < T.Id and T1.Patient >'' order by ID DESC) F
where T.Patient ='')

update cte set Patient = NewPatient --from @Temp T --inner join cte c on T.Id = c.Id 
select * from @Temp

PluralSight Learning Library

 

[briangriffin]

That did the trick. Many thanks to you both.