Prev: xp_logininfo for a low privillage user.
Next: Partitioning - adding data to 1 table grows the NDFs of all other
From: Joachim Hofmann on 20 Jul 2010 06:24
Hello,

I have a table filled with streets from which some were renamed one ore more times.
Each street has a scode. If it was renamed is has an entry scodenew and status 'S', otherwise no
scodenew and status 'G', this is the valid street name.
I want to ask for any old street name, and want tot get the valid name, E.G. if I am asking for
'astrasse' or 'bstrasse' as well, I want to get 'cstrasse'.

I guess this could be managed by a recursive CTE, but I dont know how. How would a CTE solution look
like?
Or is there another way for this kind of query?


CREATE TABLE [dbo].[Rekursionstest](
[sname] [varchar](50) NOT NULL,
[scode] [char](10) NULL,
[status] [char](1) NOT NULL,
[scodenew] [char](10) NULL
) ON [PRIMARY]
GO

sname scode status scodenew
astrasse 4711 S 4712
bstrasse 4712 S 4713
cstrasse 4713 G NULL
xstrasse 0815 S 0816
ystrasse 0816 G NULL
nstrasse 333 G NULL


Thank You
Joachim

[SQL Server 2008]
From: Dan Guzman on 20 Jul 2010 08:21
> I guess this could be managed by a recursive CTE, but I dont know how. How
> would a CTE solution look like?

WITH cte AS
(
SELECT sname, scode, status, scodenew
FROM dbo.Rekursionstest
WHERE sname = 'astrasse'
UNION ALL
SELECT new.sname, new.scode, new.status, new.scodenew
FROM dbo.Rekursionstest new
JOIN cte ON cte.scodenew = new.scode
)
SELECT sname, scode, status, scodenew
FROM cte
WHERE scodenew IS NULL;

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Joachim Hofmann" <speicher(a)freenet.de> wrote in message
news:uAtJzW$JLHA.4796(a)TK2MSFTNGP02.phx.gbl...
> Hello,
>
> I have a table filled with streets from which some were renamed one ore
> more times.
> Each street has a scode. If it was renamed is has an entry scodenew and
> status 'S', otherwise no scodenew and status 'G', this is the valid street
> name.
> I want to ask for any old street name, and want tot get the valid name,
> E.G. if I am asking for 'astrasse' or 'bstrasse' as well, I want to get
> 'cstrasse'.
>
> I guess this could be managed by a recursive CTE, but I dont know how. How
> would a CTE solution look like?
> Or is there another way for this kind of query?
>
>
> CREATE TABLE [dbo].[Rekursionstest](
> [sname] [varchar](50) NOT NULL,
> [scode] [char](10) NULL,
> [status] [char](1) NOT NULL,
> [scodenew] [char](10) NULL
> ) ON [PRIMARY]
> GO
>
> sname scode status scodenew
> astrasse 4711 S 4712
> bstrasse 4712 S 4713
> cstrasse 4713 G NULL
> xstrasse 0815 S 0816
> ystrasse 0816 G NULL
> nstrasse 333 G NULL
>
>
> Thank You
> Joachim
>
> [SQL Server 2008]

From: --CELKO-- on 20 Jul 2010 16:37
This is a history table, where each street name has a duration. Quit
trying to use a linked list as your data model:

CREATE TABLE StreetHistory
(street_code CHAR(10) NOT NULL PRIMARY KEY, -- you showed no key!!
street_name VARCHAR(50) NOT NULL,
start_date DATE NOT NULL,
end_date DATE, -- null is current name,
CHECK (start_date <= end_date),
etc);

The query is easy with the right DDL

SELECT street_name, start_date, COALESCE (end_date, CAST
(CURRENT_TIMESTAMP AS DATE))
FROM StreetHistory
WHERE street_code = @my_street_code;
From: Eric Isaacs on 20 Jul 2010 17:29
I agree with Celko that you need a new db design to make your problem
much easier, but I disagree with his SQL solution.

If you create a table, as Celko suggests, you would use the following
SQL to determine the current street name from either the current
street name or any previous street names.

SELECT SH2.street_name
FROM StreetHistory SH1 INNER JOIN StreetHistory SH2 ON
SH1.Street_Code = SH2.Street_Code AND SH2.End_Date IS NULL --The NULL
one is the current street name in history
WHERE SH1.street_code = @my_street_code;

-Eric Isaacs
From: Erland Sommarskog on 20 Jul 2010 17:59
--CELKO-- (jcelko212(a)earthlink.net) writes:
> This is a history table, where each street name has a duration. Quit
> trying to use a linked list as your data model:
>
> CREATE TABLE StreetHistory
> (street_code CHAR(10) NOT NULL PRIMARY KEY, -- you showed no key!!
> street_name VARCHAR(50) NOT NULL,
> start_date DATE NOT NULL,
> end_date DATE, -- null is current name,
> CHECK (start_date <= end_date),
> etc);

That solution can be correct in some situations. But I don't think that
it is very interesting in the context of addresses. For instance, it
may be of interest to know that Olof Palmes Gata once was Tunnelgatan
so that old references can be resolved, but it's probably not very
interesting to know when that happened.

(Then again, the example opens for another interesting problem. In fact,
the entire Tunnelgatan was not renamed, only the part west of Sveav�gen
was. The small street east of Sveav�gen that leads to the tunnel is
still Tunnelgatan.)

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2
Prev: xp_logininfo for a low privillage user.
Next: Partitioning - adding data to 1 table grows the NDFs of all other