Prev: 'Having' question
Next: Round Function
From: Mark B on 21 Jul 2008 01:55
Plamen,

Back in May you helped me by posting the code below to create records into a
groups table.

Now I want to create a new indexed table field called GroupNameUpOneLevel. I
would like it to store:

SalesPersonName GroupName
GroupNameUpOneLevel
--------------------- --------------
----------------------------
Frank Brown Fedex
Frank Brown Fedex.United States Fedex
Frank Brown Fedex.United States.Texas
Fedex.United States
Frank Brown Fedex.United States.Texas.Dallas Fedex.United
States.Texas

Are you able to show me what the modifications would be to the original code
below to incorporate this additional field insert?


The code you originally posted:
=======================

CREATE TABLE Groups (
group_name VARCHAR(80) PRIMARY KEY,
sales_person VARCHAR(35));

INSERT INTO Groups VALUES('Foo.Group', 'Frank Brown');

GO

CREATE PROCEDURE InsertGroup
@sales_person VARCHAR(35),
@raw_group_name VARCHAR(80)
AS
BEGIN
DELETE Groups
WHERE sales_person = @sales_person;

DECLARE @earth TABLE (
group_name VARCHAR(80),
group_idx INT);


WITH
N1 (n) AS (SELECT 1 UNION ALL SELECT 2),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER()
OVER(ORDER BY X.n) FROM N3 AS X, N3 AS Y)
INSERT INTO Groups (sales_person, group_name)
OUTPUT Inserted.group_name,
LEN(Inserted.group_name) -
LEN(REPLACE(Inserted.group_name, '.', ''))
INTO @earth
SELECT @sales_person,
SUBSTRING(@raw_group_name, 1,
CHARINDEX('.', @raw_group_name + '.', n) - 1)
FROM N4
WHERE SUBSTRING('.' + @raw_group_name, n, 1) = '.'
AND n < LEN(@raw_group_name) + 1;


INSERT INTO Groups (sales_person, group_name)
SELECT @sales_person,
STUFF(group_name, 1,
CHARINDEX('.', group_name + '.') - 1, 'Earth')
FROM @earth
WHERE group_idx <= 4;


END;


GO


EXEC InsertGroup
@sales_person = 'Frank Brown',
@raw_group_name = 'Fedex.United States.Texas.Dallas.Airport
Branch.Customer Services.Team C';


SELECT sales_person, group_name
FROM Groups;


HTH,

Plamen Ratchev
http://www.SQLStudio.com





From: Erland Sommarskog on 21 Jul 2008 05:00
Mark B (none123(a)none.com) writes:
> Back in May you helped me by posting the code below to create records
> into a groups table.
>
> Now I want to create a new indexed table field called
> GroupNameUpOneLevel. I would like it to store:
>
> SalesPersonName GroupName
> GroupNameUpOneLevel
> --------------------- --------------
> ----------------------------
> Frank Brown Fedex
> Frank Brown Fedex.United States Fedex
> Frank Brown Fedex.United States.Texas
> Fedex.United States
> Frank Brown Fedex.United States.Texas.Dallas Fedex.United
> States.Texas
>
> Are you able to show me what the modifications would be to the original
> code below to incorporate this additional field insert?


This may be what you are looking for?

CREATE TABLE Groups (
group_name VARCHAR(80) PRIMARY KEY,
upper_level varchar(80),
sales_person VARCHAR(35));

GO

CREATE PROCEDURE InsertGroup
@sales_person VARCHAR(35),
@raw_group_name VARCHAR(80)
AS
BEGIN
DELETE Groups
WHERE sales_person = @sales_person;

WITH
N1 (n) AS (SELECT 1 UNION ALL SELECT 2),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER()
OVER(ORDER BY X.n) FROM N3 AS X, N3 AS Y),
groups (group_name) AS (
SELECT SUBSTRING(@raw_group_name, 1,
CHARINDEX('.', @raw_group_name + '.', n) - 1)
FROM N4
WHERE SUBSTRING('.' + @raw_group_name, n, 1) = '.'
AND n < LEN(@raw_group_name) + 1
)
INSERT INTO Groups (sales_person, group_name, upper_level)
SELECT @sales_person, group_name,
CASE WHEN charindex('.', group_name) > 0
THEN substring(group_name, 1,
len(group_name) -
charindex('.', reverse(group_name)))
END
FROM groups

END;


GO


EXEC InsertGroup
@sales_person = 'Frank Brown',
@raw_group_name = 'Fedex.United States.Texas.Dallas.Airport
Branch.Customer Services.Team C';


SELECT sales_person, group_name, upper_level
FROM Groups;
go
drop table Groups
drop procedure InsertGroup



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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: steve dassin on 21 Jul 2008 05:39
Help minus Understanding = Hope
You can only hope you can get more of the same kind of help. You'd be better
off
doing this in a way you understand so you can modify it. Hope is a poor
foundation
for responsibility. Use a cursor if you have too. What good is performance
only?
Help comes at a price, there are no free lunches.

www.beyondsql.blogspot.com



From: Plamen Ratchev on 21 Jul 2008 10:19
Hi Mark,

It is always best to describe the initial problem in more detail so you can
get better help from everyone here. Your requirements were not so simple and
the code may be misleading.

Also, you may want to look at hierarchies and in particular materialized
path.

Here is the code changed to produce the desired result set (in a way very
similar to Erland's code, but complete in context of the full requirements).

CREATE TABLE Groups (
group_name VARCHAR(80) PRIMARY KEY,
sales_person VARCHAR(35),
GroupNameUpOneLevel VARCHAR(80));

GO

CREATE PROCEDURE InsertGroup
@sales_person VARCHAR(35),
@raw_group_name VARCHAR(80)
AS
BEGIN
DELETE Groups
WHERE sales_person = @sales_person;

DECLARE @earth TABLE (
group_name VARCHAR(80),
group_idx INT,
GroupNameUpOneLevel VARCHAR(80));


WITH
N1 (n) AS (SELECT 1 UNION ALL SELECT 2),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER()
OVER(ORDER BY X.n) FROM N3 AS X, N3 AS Y)
INSERT INTO Groups (sales_person, group_name, GroupNameUpOneLevel)
OUTPUT Inserted.group_name,
LEN(Inserted.group_name) -
LEN(REPLACE(Inserted.group_name, '.', '')),
Inserted.GroupNameUpOneLevel
INTO @earth
SELECT sales_person, group_name,
CASE WHEN CHARINDEX('.', group_name) > 0
THEN LEFT(group_name, LEN(group_name) -
CHARINDEX('.', REVERSE(group_name)))
END AS GroupNameUpOneLevel
FROM(
SELECT @sales_person,
SUBSTRING(@raw_group_name, 1,
CHARINDEX('.', @raw_group_name + '.', n) - 1)
FROM N4
WHERE SUBSTRING('.' + @raw_group_name, n, 1) = '.'
AND n < LEN(@raw_group_name) + 1) AS T(sales_person, group_name);

INSERT INTO Groups (sales_person, group_name, GroupNameUpOneLevel)
SELECT @sales_person,
STUFF(group_name, 1,
CHARINDEX('.', group_name + '.') - 1, 'Earth'),
STUFF(GroupNameUpOneLevel, 1,
CHARINDEX('.', GroupNameUpOneLevel + '.') - 1,
'Earth')
FROM @earth
WHERE group_idx <= 4;

END;

GO

EXEC InsertGroup
@sales_person = 'Frank Brown',
@raw_group_name = 'Fedex.United States.Texas.Dallas.Airport
Branch.Customer Services.Team C';

SELECT sales_person, group_name, GroupNameUpOneLevel
FROM Groups;


HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: Mark B on 22 Jul 2008 08:47
Thanks very much.

The REVERSE function was the answer to my thinking... I was trying to figure
out if there was a "From Right" switch for the CHARINDEX function.



"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:61B4A765-75D3-456F-ACD1-62A7895A1BB7(a)microsoft.com...
> Hi Mark,
>
> It is always best to describe the initial problem in more detail so you
> can get better help from everyone here. Your requirements were not so
> simple and the code may be misleading.
>
> Also, you may want to look at hierarchies and in particular materialized
> path.
>
> Here is the code changed to produce the desired result set (in a way very
> similar to Erland's code, but complete in context of the full
> requirements).
>
> CREATE TABLE Groups (
> group_name VARCHAR(80) PRIMARY KEY,
> sales_person VARCHAR(35),
> GroupNameUpOneLevel VARCHAR(80));
>
> GO
>
> CREATE PROCEDURE InsertGroup
> @sales_person VARCHAR(35),
> @raw_group_name VARCHAR(80)
> AS
> BEGIN
> DELETE Groups
> WHERE sales_person = @sales_person;
>
> DECLARE @earth TABLE (
> group_name VARCHAR(80),
> group_idx INT,
> GroupNameUpOneLevel VARCHAR(80));
>
>
> WITH
> N1 (n) AS (SELECT 1 UNION ALL SELECT 2),
> N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
> N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
> N4 (n) AS (SELECT ROW_NUMBER()
> OVER(ORDER BY X.n) FROM N3 AS X, N3 AS Y)
> INSERT INTO Groups (sales_person, group_name, GroupNameUpOneLevel)
> OUTPUT Inserted.group_name,
> LEN(Inserted.group_name) -
> LEN(REPLACE(Inserted.group_name, '.', '')),
> Inserted.GroupNameUpOneLevel
> INTO @earth
> SELECT sales_person, group_name,
> CASE WHEN CHARINDEX('.', group_name) > 0
> THEN LEFT(group_name, LEN(group_name) -
> CHARINDEX('.', REVERSE(group_name)))
> END AS GroupNameUpOneLevel
> FROM(
> SELECT @sales_person,
> SUBSTRING(@raw_group_name, 1,
> CHARINDEX('.', @raw_group_name + '.', n) - 1)
> FROM N4
> WHERE SUBSTRING('.' + @raw_group_name, n, 1) = '.'
> AND n < LEN(@raw_group_name) + 1) AS T(sales_person, group_name);
>
> INSERT INTO Groups (sales_person, group_name, GroupNameUpOneLevel)
> SELECT @sales_person,
> STUFF(group_name, 1,
> CHARINDEX('.', group_name + '.') - 1, 'Earth'),
> STUFF(GroupNameUpOneLevel, 1,
> CHARINDEX('.', GroupNameUpOneLevel + '.') - 1,
> 'Earth')
> FROM @earth
> WHERE group_idx <= 4;
>
> END;
>
> GO
>
> EXEC InsertGroup
> @sales_person = 'Frank Brown',
> @raw_group_name = 'Fedex.United States.Texas.Dallas.Airport
> Branch.Customer Services.Team C';
>
> SELECT sales_person, group_name, GroupNameUpOneLevel
> FROM Groups;
>
>
> HTH,
>
> Plamen Ratchev
> http://www.SQLStudio.com

 | 
Pages: 1
Prev: 'Having' question
Next: Round Function