SQL Server - Avoid cursor while serial update
-
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 #transfersThe results should be exactly the following:
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
-
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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 -
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
-
Super! Thanks a lot
-
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 - 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.PersonThis does not correctly update Person One.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
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
- 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.PersonThis does not correctly update Person One.
Ah, yes! I see why the recursive CTE was suggested now.
-
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;
goAus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply