Create Confections with Live Insects Club dues

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Create Confections with Live Insects Club dues

Author

Topic

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-16 : 15:23:53

In my quest for assistance, I have created a test table of the "Create Confections with Live Insects Club" for copy and paste.....

DROP TABLE patients
CREATE TABLE patients
(
mem_num int NULL,
p_name nvarchar (25) NULL,
addr nvarchar (25) NULL,
city_state nvarchar (35) NULL
)
GO
INSERT patients VALUES(1010,'Hoggshead, Pigeout','02 Avenue of the Ailments','Aardvarksville, Missouri')
INSERT patients VALUES(1222,'Hoggshead, Agnes ','02 Avenue of the Ailments','Aardvarksville, Missouri')
INSERT patients VALUES(3000,'Saine, Yuri N. ','Box 0 ','Corndog City, Wyoming')
INSERT patients VALUES(4444,'Buttbreath, Miffy ','13 Cackle Rd, ','Egg Puke Lake, Indiana')
INSERT patients VALUES(4445,'Buttbreath, Elmer ','13 Cackle Rd, ','Egg Puke Lake, Indiana')
INSERT patients VALUES(7777,'Plork, Mort Plug ','45 Brown Circle ','Stainsville, Arizona')
INSERT patients VALUES(9000,'Zleeping, Hoosatt ','Free-Box-of-Ants Drive ','Roadkill, Alaska')
GO

I need to let these folks know that their "Create Confections with Live Insects Club" dues are soon due. However, some members cohabitate. Rather than waste postage, how to I make it so that I have to send only one postcard to Aardvarksville, MO and Egg Puke Lake, IN?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-16 : 15:31:04

SELECT MAX(mem_num), addr, city_state
FROM patients
GROUP BY addr, city_state

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-16 : 15:32:28

Thanks, Tara, I knew it was something simple.

However, I also want the first of the names to be kept, too. How would I do that (like include p_name in the select---but that gives me an error!)????????????????

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-16 : 16:07:01

Would I have to use a cursor?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-16 : 16:12:43

>>Would I have to use a cursor?
I was waiting for that :)

You should post your desired output so we don't guess what you want


--if you don't need the mem_num
select	min(p_name) p_name
	,addr
	,city_state 
from	patients 
group by addr
	,city_state

--if first p_name and any mem_name at the address will do 
select	min(p_name) p_name
	,max(mem_num) mem_num
	,addr
	,city_state 
from	patients 
group by addr
	,city_state

--if you need the correct mem_name associated with the p_name
select	b.mem_num
	,a.p_name
	,a.addr
	,a.city_state
from	(
	select	min(p_name) p_name
		,addr
		,city_state 
	from	patients a
	group by addr
		,city_state
	) a
join	patients b
		on a.p_name = b.p_name
		and a.addr = b.addr
		and a.city_state = b.city_state

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-16 : 16:26:55

TG....

It's a shame that I have to resort to threatening with cursor usage to get a response here! (Ha, ha---isn't that funny???!!!)

The result I crave is a listing without Agnes Hoggshead and Elmer Buttbreath. They have the same addresss as their spouses, so I don't want to waste postage (when I can spent the extra dough on another package of 400 grasshoppers and creme d'menthe! Yummy!).

Thanks....

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-16 : 16:32:38

Xerxes,

Please post the exact desired result set that you want using your sample data.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-16 : 16:36:50

[code]
SELECT DISTINCT 'Mr./Mrs. ' + LEFT(p_name,CHARINDEX(',',p_name)-1)+CHAR(13)+CHAR(10)
+addr+CHAR(13)+CHAR(10)
+city_state+CHAR(13)+CHAR(10)
+'---------------------------' AS MailTo
FROM patients

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-16 : 16:43:11

Thanks for the responses! I have the answer, thanks Brett.

PLEASE check the Obituary in the Yak Corral!
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53857
Thanks!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-16 : 16:45:50

That assumes the last names are the same. Wouldn't you want both names on it to be sure you don't offend anyone?

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-16 : 16:47:58

quote:
Originally posted by tduggan

That assumes the last names are the same. Wouldn't you want both names on it to be sure you don't offend anyone?

Tara

Yikes!!! You are RIGHT, Tara.

I didn't think of this in my haste to finish this insipid albatross which has been hanging on my neck for the last 2 weeks! OK.....so, now what should I do here? Should I consider using a cursor to convert columns to rows, then select distinct?

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-16 : 17:03:07

I used TG's last solution and built on it:



SET NOCOUNT ON
CREATE TABLE patients
(
mem_num int NULL,
p_name nvarchar (25) NULL,
addr nvarchar (25) NULL,
city_state nvarchar (35) NULL
)
GO
INSERT patients VALUES(1010,'Hoggshead, Pigeout','02 Avenue of the Ailments','Aardvarksville, Missouri')
INSERT patients VALUES(1222,'Hoggshead, Agnes','02 Avenue of the Ailments','Aardvarksville, Missouri')
INSERT patients VALUES(3000,'Saine, Yuri N.','Box 0 ','Corndog City, Wyoming')
INSERT patients VALUES(4444,'Buttbreath, Miffy','13 Cackle Rd, ','Egg Puke Lake, Indiana')
INSERT patients VALUES(4445,'Buttbreath, Elmer','13 Cackle Rd, ','Egg Puke Lake, Indiana')
INSERT patients VALUES(7777,'Plork, Mort Plug','45 Brown Circle ','Stainsville, Arizona')
INSERT patients VALUES(9000,'Zleeping, Hoosatt','Free-Box-of-Ants Drive ','Roadkill, Alaska')
GO

select	COALESCE(a.p_name + ' and '+  b.p_name, a.p_name)	AS MailTo,a.addr	,a.city_state
from	
(
	select	
		min(p_name) p_name
		,addr
		,city_state 
	from	patients a
	group by addr ,city_state
	) a
left outer join	patients b
on 	a.addr = b.addr and a.city_state = b.city_state and a.p_name <> b.p_name

DROP TABLE patients

The order of the names will be dependent on the alphabet though.

[EDIT] Just noticed I copied in an extra query. I've now edited it out.

Tara

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-16 : 17:08:36

Dear SQL Goddess....I can live with alphabetization, no problem.

Please know that I am most grateful you caught my faux pas

prior to my sending off my results! I shudder

to imagine the offenses this would have caused

. Now it looks like I'll still be gainfully employed a little while longer.

I hope you have a terrific day! I'm having one already!!!

Thanks again!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-16 : 17:14:51

[code]
CREATE TABLE patients
(
mem_num int NULL,
p_name nvarchar (25) NULL,
addr nvarchar (25) NULL,
city_state nvarchar (35) NULL
)
GO
INSERT patients VALUES(1010,'Hoggshead, Pigeout','02 Avenue of the Ailments','Aardvarksville, Missouri')
INSERT patients VALUES(1222,'Hoggshead, Agnes ','02 Avenue of the Ailments','Aardvarksville, Missouri')
INSERT patients VALUES(3000,'Saine, Yuri N. ','Box 0 ','Corndog City, Wyoming')
INSERT patients VALUES(4444,'Buttbreath, Miffy ','13 Cackle Rd, ','Egg Puke Lake, Indiana')
INSERT patients VALUES(4445,'Buttbreath, Elmer ','13 Cackle Rd, ','Egg Puke Lake, Indiana')
INSERT patients VALUES(7777,'Plork, Mort Plug ','45 Brown Circle ','Stainsville, Arizona')
INSERT patients VALUES(9000,'Zleeping, Hoosatt ','Free-Box-of-Ants Drive ','Roadkill, Alaska')
INSERT patients VALUES(1234,'Dugga, Tara ','1 Engagement Bliss Drive','San Diego,CA')
INSERT patients VALUES(1234,'Fiance, Taras','1 Engagement Bliss Drive','San Diego,CA')
GO

CREATE FUNCTION GetAllResidents(@addr nvarchar(25), @city_state nvarchar(35))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Result VARCHAR(8000)

SELECT @Result = COALESCE(@Result + ' Or ','')
+ REVERSE(LEFT(REVERSE(p_name),CHARINDEX(',',REVERSE(p_name))-2)) + ' ' + LEFT(p_name,CHARINDEX(',',p_name)-1)
FROM patients
WHERE addr=@addr AND city_state = @city_State

RETURN @Result
END
GO

SELECT dbo.GetAllResidents(addr, city_state) + CHAR(13) + CHAR(10)
+ addr + CHAR(13) + CHAR(10)
+ city_state + CHAR(13) + CHAR(10)
+ REPLICATE('-',LEN(city_state))
FROM (SELECT DISTINCT addr, city_state FROM patients) AS XXX
GO

DROP Function GetAllResidents
DROP TABLE patients
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-16 : 17:31:05

WOW! I DIDN'T KNOW TARA HAD A FIANCE?! WHAT A LUCKY DEVIL!(Sure wish I was 25 years younger and single again!)

Thanks for the phenomenal code, Brett!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-17 : 09:27:36

your welcome...but this isn't a real database is it?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-17 : 10:12:49

quote:
Originally posted by X002548

your welcome...but this isn't a real database is it?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

C'mon, Brett, do you honestly believe I'd have a database like this? No, I already have the scars

--proof of my having been raked over the coals--from being hammered over database normalization ever since I punched in here at SQLTeam.

If there's one thing I've learned: it's normalization. And I have several cursors that perform normalization daily!

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-17 : 11:11:16

I want....

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-17 : 11:11:34

...to set....

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-17 : 11:11:59

...this topic....

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-08-17 : 11:12:22

......ON FIRE!!!!

Thanks....

Semper fi,
XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources