SQL Query to update table with prior field value
I have a table "estimate". It contains the fields:
itemno
assemblyseq
prefix
parentnum
partnum
priorpeer
I need to update the "priorpeer" column. The query needs to use the "prefix" column to determine the update. The rules are:
if prefix = 0 then "priorpeer" = -1
if itemno ends in .0 then "priorpeer" = -1
if the first instance of the prefix (when the query is sorted by itemno) then "priorpeer" = -1
otherwise, while still sorted by itemno, the "priorpeer" = assemblyseq of the prior record with the matching prefix.
See the attached excel spreadsheet for the desired update to "priorpeer" field. column "H" shows what I want the end update result to be.
Thanks for your help.
ExamplePriorPeer.xlsx
itemno
assemblyseq
prefix
parentnum
partnum
priorpeer
I need to update the "priorpeer" column. The query needs to use the "prefix" column to determine the update. The rules are:
if prefix = 0 then "priorpeer" = -1
if itemno ends in .0 then "priorpeer" = -1
if the first instance of the prefix (when the query is sorted by itemno) then "priorpeer" = -1
otherwise, while still sorted by itemno, the "priorpeer" = assemblyseq of the prior record with the matching prefix.
See the attached excel spreadsheet for the desired update to "priorpeer" field. column "H" shows what I want the end update result to be.
Thanks for your help.
ExamplePriorPeer.xlsx
David Johnson, CD
A4 doesn't end in 0 it ends in 1 A4 would have to be 1.0 the same holds for all values that you want to end in .0
ASKER
A4 actually does end in .0. I see they got dropped when I pasted the data into Excel. See updated spreadsheet.
ExamplePriorPeer-updated.xlsx
ExamplePriorPeer-updated.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can create a table (temp or view) with those values using the following select:
select itemno,'0' as prevno, assembly_seq, prefix, 0 as prior_seq, '0' as prevfix, -1 as priorpeer
from estimate where prefix = '0'
union all
select x.itemno, y.itemno, x.assembly_seq, x.prefix, y.assembly_seq as prior_seq, y.prefix as prevfix,
case when length(y.prefix) = 1 then -1
when x.prefix > y.prefix then -1
when x.prefix = y.prefix then y.assembly_seq
else -1
end priorpeer--, x.rn xrn, y.rn yrn
from
(select itemno, assembly_seq, prefix, priorpeer,
row_number() over (order by prefix, assembly_seq) rn
from estimate) x,
(select itemno, assembly_seq, prefix, priorpeer,
row_number() over (order by prefix, assembly_seq) rn
from estimate) y
where x.rn = y.rn + 1
You can then use merge or a join for your update.
select itemno,'0' as prevno, assembly_seq, prefix, 0 as prior_seq, '0' as prevfix, -1 as priorpeer
from estimate where prefix = '0'
union all
select x.itemno, y.itemno, x.assembly_seq, x.prefix, y.assembly_seq as prior_seq, y.prefix as prevfix,
case when length(y.prefix) = 1 then -1
when x.prefix > y.prefix then -1
when x.prefix = y.prefix then y.assembly_seq
else -1
end priorpeer--, x.rn xrn, y.rn yrn
from
(select itemno, assembly_seq, prefix, priorpeer,
row_number() over (order by prefix, assembly_seq) rn
from estimate) x,
(select itemno, assembly_seq, prefix, priorpeer,
row_number() over (order by prefix, assembly_seq) rn
from estimate) y
where x.rn = y.rn + 1
You can then use merge or a join for your update.
ASKER
I used the LAG and LEAD functions, since this will be hosted on SQL 2012. Thanks