SQL Server - Avoid cursor while serial update

Question

Post reply

SQL Server - Avoid cursor while serial update

sbob

Old Hand

Points: 333

January 24, 2020 at 2:10 pm

Go to Answer

#3720151

How can I avoid use cursor to implement the following? I read that it can be done with CTE but I didn't get it working with the same result.

In the example I am using two tables, the first one is the holders table containing a list of people and the transfers table where each transfer indicates a change at a specific record of the first table.

Below you can see the code, which brings the right results:

create table #holders(Person VARCHAR(50), Kind VARCHAR(50), Pctg FLOAT)
create table #transfers(Person_FROM VARCHAR(50), Person_To VARCHAR(50), Kind VARCHAR(50), Pctg_New FLOAT, Eff_Date DATE)

insert into #holders
select 'Person One', 'Kind 1', 50 union all
select 'Person Two', 'Kind 1', 50 union all
select 'Person Three', 'Kind 1', NULL union all
select 'Person Four', 'Kind 1', NULL union all
select 'Person One', 'Kind 2', 100

insert into #transfers
select 'Person One', 'Person A', 'Kind 1', 70, '2019-12-31' union all
select 'Person Two', 'Person B', 'Kind 1', 30, '2020-01-01' union all
select 'Person A', 'Person A1', 'Kind 1', 70, '2020-01-02' union all
select 'Person A', 'Person A2', 'Kind 1', 70, '2020-01-03' union all --Should Avoided
select 'Person A2', 'Person A3', 'Kind 1', 70, '2020-01-04' union all --Should Avoided
select 'Person A1', 'Person A4', 'Kind 1', 70, '2020-01-05'

declare
@Person_FROM varchar(50),
@Person_To varchar(50),
@Kind varchar(50),
@Pctg_New float

declare cur cursor for select Person_FROM, Person_To, Kind, Pctg_New from #transfers order by Eff_Date
open cur
fetch next from cur into @Person_FROM, @Person_To, @Kind, @Pctg_New
while @@FETCH_STATUS = 0 begin
update #holders set Person = @Person_To, Pctg = @Pctg_New where Person = @Person_FROM AND Kind = @Kind
fetch next from cur into @Person_FROM, @Person_To, @Kind, @Pctg_New
end
close cur
deallocate cur

SELECT * FROM #holders

drop table #holders
drop table #transfers

The results should be exactly the following:

correct_results

I think that the key is it is needed serialized update (order by Eff_Date) and some kind of recursive (The first line should update 3 times using this flow: "Person One" --> "Person A" --> "Person A1" --> "Person A4").

Any help is welcome! Thanks in advance

Mark Cowne

One Orange Chip

Points: 26952

January 24, 2020 at 4:17 pm

Answer

#3720216

Here's a recursive CTE solution

WITH Recur AS (
 SELECT Person_FROM AS Person_FROM_Start, Person_FROM, Person_To, Kind, Pctg_New, Eff_Date
 FROM #transfers
 UNION ALL
 SELECT ts.Person_FROM_Start, te.Person_FROM, te.Person_To, te.Kind, te.Pctg_New, te.Eff_Date
 FROM Recur ts
 INNER JOIN #transfers te ON ts.Person_To = te.Person_FROM AND ts.Kind = te.Kind AND te.Eff_Date > ts.Eff_Date
),
Results AS (
 SELECT Person_FROM_Start,Person_To,Kind,Pctg_New,Eff_Date,
 ROW_NUMBER() OVER(PARTITION BY Person_FROM_Start,Kind ORDER BY Eff_Date DESC) AS rn
 FROM Recur
)
SELECT ISNULL(r.Person_To,h.Person) AS Person, h.Kind, ISNULL(r.Pctg_New,h.Pctg) AS Pctg
FROM #holders h
LEFT OUTER JOIN Results r ON r.Person_FROM_Start = h.Person AND r.Kind = h.Kind AND r.rn = 1;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 10 posts - 1 through 9 (of 9 total)

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

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

Yuck! I started on this before spotting the need for some sort of recursion, to make the Person A4 bit work.

If I had more time, I think that I would first build a query to create an 'enhanced' #transfers table. This version would contain straight mappings (Person One to Person A4 etc), making the rest of exercise trivial.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 2

What are you really trying to do here? Why overwrite, when you could just capture the history by adding a date of transfer or something and have the whole history?

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

sbob Old Hand Points: 333 More actions · Answer 3

sbob

Old Hand

Points: 333

January 24, 2020 at 4:31 pm

#3720220

Super! Thanks a lot

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 4

No need for a cte:

update h
   set h.Person = t.Person_To, 
       h.Pctg = t.Pctg_New 
  from #holders h
 inner join #transfers t
         on t.Kind = h.Kind
        and t.Person_FROM = h.Person

Phil Parkin SSC Guru Points: 246963 More actions · Answer 5

Jonathan AC Roberts wrote:

No need for a cte:

update h
   set h.Person = t.Person_To, 
       h.Pctg = t.Pctg_New 
  from #holders h
 inner join #transfers t
         on t.Kind = h.Kind
        and t.Person_FROM = h.Person

This does not correctly update Person One.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sbob Old Hand Points: 333 More actions · Answer 6

Hi @jonathan-2 AC Roberts

Thanks for your answer

Applied this update the results will be:

Person Kind Pctg

Person A Kind 1 70

Person B Kind 1 30

Person Three Kind 1 NULL

Person Four Kind 1 NULL

Person One Kind 2 100

which are incorrect

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 7

Phil Parkin wrote:

Jonathan AC Roberts wrote:

No need for a cte:

update h
   set h.Person = t.Person_To, 
       h.Pctg = t.Pctg_New 
  from #holders h
 inner join #transfers t
         on t.Kind = h.Kind
        and t.Person_FROM = h.Person

This does not correctly update Person One.

Ah, yes! I see why the recursive CTE was suggested now.

Steve Collins SSCrazy Points: 2158 More actions · Answer 8

Nicely done Mark Cowne. My attempt avoids the inequality on date (which makes me nervous) but is otherwise the same.


with
t_rn_cte(person_from, person_to, kind, pctg_new, eff_date, row_num) as (
    select
      t.*, row_number() over(partition by person_from, kind order by eff_date asc) row_num
    from
      #transfers t),
t_cte(orig_person_from, person_from, person_to, kind, pctg_new, eff_date, h_level) as (
    select
        t.person_from, t.person_from, t.person_to, t.kind, t.pctg_new, t.eff_date, 0
    from
      t_rn_cte t
    where
      t.row_num=1
    union all
    select
      hc.orig_person_from, t.person_from, t.person_to, t.kind, t.pctg_new, t.eff_date, hc.h_level+1
    from
      t_rn_cte t
     join
      t_cte hc on t.person_from=hc.person_to
                  and t.kind=hc.kind
                  and t.row_num=1),
t_level_rn_cte(orig_person_from, person_from, person_to, kind, pctg_new, eff_date, h_level, row_num) as (
    select
      tc.*, row_number() over(partition by orig_person_from, kind order by h_level desc) row_num
    from
      t_cte tc)
select 
  coalesce(tc.person_to, h.person) person,
  h.kind,
  coalesce(tc.pctg_new, h.Pctg) pctg
from
  #holders h
 left join
  t_level_rn_cte tc on h.person=tc.orig_person_from
                       and h.kind=tc.kind
                       and tc.row_num=1  
order by
  h.kind, 
  coalesce(tc.person_to, h.person) asc;
go

This reply was modified 4 years, 10 months ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können