July 16, 2012 at 12:57 pm
Hi
I was wondering if someone could help me. I have a huge table that stores product delivery routes and the buildings that belong to each route. The problem is that everytime a new building is included to one Route, its Seq field will be equal to zero (see the first 4 rows below).
#ID RouteID Seq BuildingID StreetName StreetNr StreetAddInfo
01 D903010 0000 8912200 John Smith 103 13A
02 D321989 0000 7893738 Mary Clarkson 24 11A
03 D327298 0000 9436432 Cleveland 1134 72A
04 D327298 0000 9436432 Cleveland 1134 73A
05 D674748 0001 6355454 Christina Tyler 67 21B
06 D674748 0002 6355472 Christina Tyler 67 22B
07 D674748 0003 6355590 Christina Tyler 67 23B
08 D674748 0004 6355632 Christina Tyler 67 24B
09 D327298 0000 9436432 Cleveland 1134 62A
10 D903010 0005 9712232 John Smith 103 14A
11 D903010 0006 9712420 John Smith 103 15A
12 D903010 0007 9712560 John Smith 103 16A
13 D903010 0008 9712646 John Smith 103 17A
14 D321989 0009 4345649 Mary Clarkson 25 12A
15 D321989 0009 4345750 Mary Clarkson 25 13A
16 D321989 0009 4345775 Mary Clarkson 25 14A
I need a select that will retrieve/sort the newest rows (Seq = 0) close to the existing rows with same StreetName and StreetNumber. The outcome should be like this (I'm adding a NEWSEQ column):
#ID RouteID Seq BuildingID StreetName StreetNr StreetAddInfo NEWSEQ
05 D674748 0001 6355454 Christina Tyler 67 21B 0001
06 D674748 0002 6355472 Christina Tyler 67 22B 0002
07 D674748 0003 6355590 Christina Tyler 67 23B 0003
08 D674748 0004 6355632 Christina Tyler 67 24B 0004
03 D327298 0000 9436432 Cleveland 1134 72A 0005
04 D327298 0000 9436432 Cleveland 1134 73A 0006
09 D327298 0000 9436432 Cleveland 1134 62A 0007
01 D903010 0000 8912200 John Smith 103 13A 0008
10 D903010 0005 9712232 John Smith 103 14A 0009
11 D903010 0006 9712420 John Smith 103 15A 0010
12 D903010 0007 9712560 John Smith 103 16A 0011
13 D903010 0008 9712646 John Smith 103 17A 0012
02 D321989 0000 7893738 Mary Clarkson 24 11A 0013
14 D321989 0009 4345649 Mary Clarkson 25 12A 0014
15 D321989 0009 4345750 Mary Clarkson 25 13A 0015
16 D321989 0009 4345775 Mary Clarkson 25 14A 0016
I am not allowed to change this data, but only display it in a report. As the table contains many rows, I wouldn't like to copy its data to another table. Is there a way to acomplish that using ROW_NUMBER() and RANK() functions? Thanks in advance!
July 16, 2012 at 1:06 pm
something like this?
With MyCTE (#ID,RouteID,Seq,BuildingID,StreetName,StreetNr,StreetAddInfo)
AS
(
SELECT '01','D903010','0000','8912200','John Smith','103','13A' UNION ALL
SELECT '02','D321989','0000','7893738','Mary Clarkson','24','11A' UNION ALL
SELECT '03','D327298','0000','9436432','Cleveland','1134','72A' UNION ALL
SELECT '04','D327298','0000','9436432','Cleveland','1134','73A' UNION ALL
SELECT '05','D674748','0001','6355454','Christina Tyler','67','21B' UNION ALL
SELECT '06','D674748','0002','6355472','Christina Tyler','67','22B' UNION ALL
SELECT '07','D674748','0003','6355590','Christina Tyler','67','23B' UNION ALL
SELECT '08','D674748','0004','6355632','Christina Tyler','67','24B' UNION ALL
SELECT '09','D327298','0000','9436432','Cleveland','1134','62A' UNION ALL
SELECT '10','D903010','0005','9712232','John Smith','103','14A' UNION ALL
SELECT '11','D903010','0006','9712420','John Smith','103','15A' UNION ALL
SELECT '12','D903010','0007','9712560','John Smith','103','16A' UNION ALL
SELECT '13','D903010','0008','9712646','John Smith','103','17A' UNION ALL
SELECT '14','D321989','0009','4345649','Mary Clarkson','25','12A' UNION ALL
SELECT '15','D321989','0009','4345750','Mary Clarkson','25','13A' UNION ALL
SELECT '16','D321989','0009','4345775','Mary Clarkson','25','14A'
)
SELECT Row_number() over (partition by RouteId order by RouteId,#ID) As RW,*
FROM MyCTE
order by RouteId,#ID
Lowell
July 16, 2012 at 1:31 pm
Hi Lowell,
Yours clever solution generates a very close output to what I am looking for. However, I made 2 mistakes for which I offer my apologies.
First, actually the Route ID can repeat many times (the table stores all possible routes). So probably it would be better to use the D903010 as the RouteID for all rows in my example (the real data cannot be used because it has so many rows for each route).
#ID RouteID Seq BuildingID StreetName StreetNr StreetAddInfo
01 D903010 0000 8912200 John Smith 103 13A
02 D903010 0000 7893738 Mary Clarkson 24 11A
03 D903010 0000 9436432 Cleveland 1134 72A
04 D903010 0000 9436432 Cleveland 1134 73A
05 D903010 0001 6355454 Christina Tyler 67 21B
06 D903010 0002 6355472 Christina Tyler 67 22B
07 D903010 0003 6355590 Christina Tyler 67 23B
08 D903010 0004 6355632 Christina Tyler 67 24B
09 D903010 0005 9436432 Cleveland 1134 62A
10 D903010 0006 9712232 John Smith 103 14A
11 D903010 0007 9712420 John Smith 103 15A
12 D903010 0008 9712560 John Smith 103 16A
13 D903010 0009 9712646 John Smith 103 17A
14 D903010 0010 4345649 Mary Clarkson 25 12A
15 D903010 0011 4345750 Mary Clarkson 25 13A
16 D903010 0012 4345775 Mary Clarkson 25 14A
Second, the Seq column values weren't in order (I corrected it in the above table).
And finally, I would like to "preserve" the Route original sequence, moving only the newest unordered rows close to the existing rows. So, in this way, 'Christina Tyler' comes first than 'Cleveland', which comes first than 'John Smith' and 'Mary Clarkson'. Their relative position are kept.
#ID RouteID Seq BuildingID StreetName StreetNr StreetAddInfo NEWSEQ
05 D903010 0001 6355454 Christina Tyler 67 21B 0001
06 D903010 0002 6355472 Christina Tyler 67 22B 0002
07 D903010 0003 6355590 Christina Tyler 67 23B 0003
08 D903010 0004 6355632 Christina Tyler 67 24B 0004
03 D903010 0000 9436432 Cleveland 1134 72A 0005
04 D903010 0000 9436432 Cleveland 1134 73A 0006
09 D327298 0005 D903010 Cleveland 1134 62A 0007
01 D903010 0000 8912200 John Smith 103 13A 0008
10 D903010 0006 9712232 John Smith 103 14A 0009
11 D903010 0007 9712420 John Smith 103 15A 0010
12 D903010 0008 9712560 John Smith 103 16A 0011
13 D903010 0009 9712646 John Smith 103 17A 0012
02 D903010 0000 7893738 Mary Clarkson 24 11A 0013
14 D903010 0010 4345649 Mary Clarkson 25 12A 0014
15 D903010 0011 4345750 Mary Clarkson 25 13A 0015
16 D903010 0012 4345775 Mary Clarkson 25 14A 0016
Do you happen to know how to do that? Thank you very much!
Best!
July 19, 2012 at 2:24 pm
Hi,
Does anybody have a solution for this problem?
Many Thanks!
July 19, 2012 at 3:25 pm
some readily consumable sample data would really help to get you an answer. im currently formating your sample data and will get back to you soon.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
July 19, 2012 at 3:49 pm
Here is a way to do it might not be the best but it should work
With MyCTE (#ID,RouteID,Seq,BuildingID,StreetName,StreetNr,StreetAddInfo)
--data changed a little so its not in alphabetical order for sequence to prove its actually working
AS
(
SELECT '01','D903010','0000','8912200','John Smith','103','13A' UNION ALL
SELECT '02','D903010','0000','7893738','Mary Clarkson','24','11A' UNION ALL
SELECT '03','D903010','0000','9436432','Cleveland','1134','72A' UNION ALL
SELECT '04','D903010','0000','9436432','Cleveland','1134','73A' UNION ALL
SELECT '05','D903010','0013','6355454','Christina Tyler','67','21B' UNION ALL
SELECT '06','D903010','0014','6355472','Christina Tyler','67','22B' UNION ALL
SELECT '07','D903010','0015','6355590','Christina Tyler','67','23B' UNION ALL
SELECT '08','D903010','0016','6355632','Christina Tyler','67','24B' UNION ALL
SELECT '09','D903010','0005','9436432','Cleveland','1134','62A' UNION ALL
SELECT '10','D903010','0006','9712232','John Smith','103','14A' UNION ALL
SELECT '11','D903010','0007','9712420','John Smith','103','15A' UNION ALL
SELECT '12','D903010','0008','9712560','John Smith','103','16A' UNION ALL
SELECT '13','D903010','0009','9712646','John Smith','103','17A' UNION ALL
SELECT '14','D903010','0010','4345649','Mary Clarkson','25','12A' UNION ALL
SELECT '15','D903010','0011','4345750','Mary Clarkson','25','13A' UNION ALL
SELECT '16','D903010','0012','4345775','Mary Clarkson','25','14A'
),
--Get the max Seq per street and routeID and then give it a row number as the Seq goes up so i have a rank
StreetRank AS (
SELECT ROW_NUMBER() OVER (PARTITION BY RouteID ORDER BY (MAX(Seq))) AS StreetRank, MAX(Seq) MaxSeq, StreetName, RouteID
FROM MyCTE GROUP BY StreetName, RouteID
)
--Join my street rank to the data table and sort by StreetRank then Seq
SELECT m.#ID, m.RouteID,m.Seq,m.BuildingID,m.StreetName,m.StreetNr, m.StreetAddInfo
FROM StreetRank sr
INNER JOIN MyCTE m
ON m.StreetName = sr.StreetName
AND m.RouteID = sr.RouteID
ORDER BY sr.StreetRank, m.Seq
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
July 20, 2012 at 12:57 am
darthurfle (7/19/2012)
Hi,Does anybody have a solution for this problem?
Many Thanks!
Without a sample data script to work with? Figure out the algorithm for generating the numbers in the seq column, filtering out rows where the value is zero. It's going to be something like
SELECT TOP 1000
seq = ROW_NUMBER() OVER (PARTITION BY RouteID ORDER BY StreetName), *
FROM MyTable
WHERE seq > 0
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
July 23, 2012 at 12:14 am
CELKO (7/20/2012)
Address data is formatted using the CASS specs from USPS and you can get software that will assist you. Have you ever heard of a SAN (Standard Address Number)?
I am curious if this CASS spec covers foreign (i.e., non-US) addresses.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply