Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Doing something like an Excel FillDown function in SQL looking for suggestions??? Expand / Collapse
Author
Message
Posted Monday, November 12, 2012 7:34 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504
I have a recordset that returns the following data:
ItemNumber	DisplaySeqNum
10 1
2
3
4
20 5
6
7
8
30 9
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.

ItemNumber	DisplaySeqNum
10 1
10 2
10 3
10 4
20 5
20 6
20 7
20 8
30 9
30 10
30 11


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



---------------------------------------------------------------
Mike Hahn - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1383702
Posted Monday, November 12, 2012 7:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
What you have there is a spreadsheet, not a database table. Rows in a database table don't have a sequence to them, so there's no way for the database to know that "the second row" should have the same value as "the first row". As far as the database is concerned, there is no "first row" or "second row". Those are things that humans looking at the data add in, based on our own mental prejudices.

I know that sounds weird, and it's one of the hardest things for new DBAs to wrap their heads around (along with how join math works, that's the one I usually finds kicks people's feet out from under them them worst). But it's true.

It's one of the key differences in how you approach databases vs how you approach spreadsheets.

What you're trying to do isn't something that SQL Server (or Oracle, DB2, MySQL, etc.) is designed to do. You won't be able to count on the results working correctly because of that. It may look right 10 times in a row, but you can't count on the 11th time being right. So don't rely on it at all.

Get the devs to fix how they are entering the data. That's the only valid solution.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1383708
Posted Monday, November 12, 2012 10:58 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504
GSquared (11/12/2012)
What you have there is a spreadsheet, not a database table. Rows in a database table don't have a sequence to them, so there's no way for the database to know that "the second row" should have the same value as "the first row". As far as the database is concerned, there is no "first row" or "second row". Those are things that humans looking at the data add in, based on our own mental prejudices.

I know that sounds weird, and it's one of the hardest things for new DBAs to wrap their heads around (along with how join math works, that's the one I usually finds kicks people's feet out from under them them worst). But it's true.

It's one of the key differences in how you approach databases vs how you approach spreadsheets.

What you're trying to do isn't something that SQL Server (or Oracle, DB2, MySQL, etc.) is designed to do. You won't be able to count on the results working correctly because of that. It may look right 10 times in a row, but you can't count on the 11th time being right. So don't rely on it at all.

Get the devs to fix how they are entering the data. That's the only valid solution.

Yeah I understand it's not a DB function and I completely agree with you that the developers should fix it but I'm on contract for a year to do some SSRS/SSAS development work and a program change isn't an option. I'm sure DBA's would lose their stomaches if they saw this database. A database developed by old school VB6 programmers... with very little restrictions... wow... (not bashing programmers, I too started out as a C++/VB programmer about 20 years ago)


---------------------------------------------------------------
Mike Hahn - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1383786
Posted Monday, November 12, 2012 5:41 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 35,366, Visits: 31,902
Want a cool sig (11/12/2012)
I have a recordset that returns the following data:
ItemNumber	DisplaySeqNum
10 1
2
3
4
20 5
6
7
8
30 9
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.

ItemNumber	DisplaySeqNum
10 1
10 2
10 3
10 4
20 5
20 6
20 7
20 8
30 9
30 10
30 11


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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1383916
Posted Monday, November 12, 2012 8:58 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504
Thanks Jeff I'll give it a try tomorrow, I'm not very familiar with cross apply very much.

---------------------------------------------------------------
Mike Hahn - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1383934
Posted Monday, November 12, 2012 10:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 35,366, Visits: 31,902
Want a cool sig (11/12/2012)
Thanks Jeff I'll give it a try tomorrow, I'm not very familiar with cross apply very much.


Cross Apply is fairly easy. It's just a correlated subquery that's very similar to the sub-query you might do in a SELECT list except that it can return more than one value. There's also an OUTER Apply that works like a correlated subquery with a left outer join.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1383945
Posted Tuesday, November 13, 2012 7:43 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504
Wow Jeff that is wicked fast... thanks a lot... the time for returning 2600 rows really shows how much faster your method is...

Jeff's wicked fast script
SQL Server Execution Times:
CPU time = 563 ms, elapsed time = 682 ms.

My wicked slow script
SQL Server Execution Times:
CPU time = 25500 ms, elapsed time = 25696 ms.


---------------------------------------------------------------
Mike Hahn - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1384103
Posted Wednesday, November 14, 2012 2:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:45 AM
Points: 35,366, Visits: 31,902
Want a cool sig (11/13/2012)
Wow Jeff that is wicked fast... thanks a lot... the time for returning 2600 rows really shows how much faster your method is...

Jeff's wicked fast script
SQL Server Execution Times:
CPU time = 563 ms, elapsed time = 682 ms.

My wicked slow script
SQL Server Execution Times:
CPU time = 25500 ms, elapsed time = 25696 ms.


Ugh! I appreciate the compliment but more than 1/2 a second to process just 2600 rows is pretty bad for me. The right kind of index might help it, though.

I say it's slow because a "Quirky Update" would do such a thing to a million rows in about 2 or 3 seconds. If you have a large nnumber of rows, let me know and we'll see what we can put together for you.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1384476
Posted Wednesday, November 14, 2012 8:45 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504
Jeff Moden (11/14/2012)
Want a cool sig (11/13/2012)
Wow Jeff that is wicked fast... thanks a lot... the time for returning 2600 rows really shows how much faster your method is...

Jeff's wicked fast script
SQL Server Execution Times:
CPU time = 563 ms, elapsed time = 682 ms.

My wicked slow script
SQL Server Execution Times:
CPU time = 25500 ms, elapsed time = 25696 ms.


Ugh! I appreciate the compliment but more than 1/2 a second to process just 2600 rows is pretty bad for me. The right kind of index might help it, though.

I say it's slow because a "Quirky Update" would do such a thing to a million rows in about 2 or 3 seconds. If you have a large nnumber of rows, let me know and we'll see what we can put together for you.


it's part of a larger query that hits a table with 530k records and joins 5 other tables... the 2600 records is the largest recordset that I can find in the table... and unfortunately the columns I'm using isn't indexed... I'm happy with 1/2 a second... Thanks


---------------------------------------------------------------
Mike Hahn - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1384688
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse