Want a cool sig (11/12/2012)
I have a recordset that returns the following data:
ItemNumberDisplaySeqNum
101
2
3
4
205
6
7
8
309
10
11
It's like an invoice sheet where there's multiple parts to each item number. There's no other relationship between the parts and the item number other then the fact that when ordered by the display sequence the item number that's first applies to all parts that do not have an item number until the next item number is filled. (programmers). Anyways I've worked out a solution using CTE but wanted to know if there's a better way to write it. I'm avoiding cursors or other loops. The result is shown below. Potentially there could be hundreds maybe thousands of parts and performance may be an issue. Thanks in advance to anyone who replies. 🙂
ItemNumberDisplaySeqNum
101
102
103
104
205
206
207
208
309
3010
3011
if object_id('tempdb.dbo.#TempTable') is not null
drop table #TempTable
create table #TempTable(ItemNumber varchar(10) not null, DisplaySeqNum int not null)
insert into #TempTable
values('10',1),('',2),('',3),('',4),('20',5),('',6),('',7),('',8),('30',9),('',10),('',11)
select *
from #TempTable
;with s1 as (
select scqli.ItemNumber
,scqli.DisplaySeqNum
from #TempTable scqli
),
s2 as (
select ItemNumber
,DisplaySeqNum
,ROW_NUMBER()over(order by ItemNumber) RowNum
from s1
where ItemNumber <> ''
),
s3 as (
select sd.ItemNumber
,sc.DisplaySeqNum
from s1 sc
inner join (
select sa.ItemNumber
,sa.DisplaySeqNum StartLine
,isnull(sb.DisplaySeqNum-1,100000) EndLine
from s2 sa
left join (select ItemNumber, DisplaySeqNum, RowNum - 1 RowNum from s2) sb
on sa.RowNum = sb.RowNum
) sd
on sc.DisplaySeqNum between sd.StartLine and sd.EndLine
)
select *
from s3
Thank you for taking the time to setup some test data. It makes it a whole lot easier for folks to try to help.
I don't know what other folks call these typess of things but I call them "data smears" because you have to "smear" the data down from one row to the next.
The following code will do what you want with the data you've so nicely provided and, if you have the right indexing, will be lightning quick.
UPDATE tt
SET ItemNumber = ca.ItemNumber
FROM #TempTable tt
CROSS APPLY (SELECT TOP 1 ItemNumber
FROM #TempTable ttca
WHERE ttca.DisplaySeqNum <= tt.DisplaySeqNum
AND ttca.ItemNumber > ''
ORDER BY ttca.DisplaySeqNum DESC) ca
;
--Jeff Moden
Change is inevitable... Change for the better is not.