odd query request seems impossible

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

SQL Server Development (2000)

odd query request seems impossible

Author

Topic

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-09-16 : 16:11:10

I tried to ask this question before, but I'm afraid I wasn't very clear. I'm trying to streamline an existing reporting mechanism that is way too reliant on Visual Basic to generate results. If possible, I'd like to use SQL Server to do most of the work.

I have a primary key that is numbered like this: 1.1 represents the oldest member of family, 1.2 represents the next in line, 1.3 the next etc. Next family starts with 2.1 and so on. I didn't come up with this key so please don't mock me for this abomination.

A typical db record looks like this:

family_id lastname firstname

1.1 smith joe
1.2 peters jane
1.3 smith ed
2.1 jones bob
2.2 jones betty
2.3 hughes april
2.4 jones hunter

The folks who use this data love to have it grouped in ways that seem odd to me, but work for them. One report they currently have allows them to build their own queries across several tables in the SQL database, after which an excel spredsheet is kicked out in the following format:

oldest_id next_id oldest_last next_last

1.1 1.2 smith peters
1.1 1.3 smith smith
2.1 2.2 jones jones
2.1 2.3 jones hughes
2.1 2.4 jones jones

You'll note the repeating of the oldest person's information whenever there is a younger relation that follows in the same family.

I've massaged the data for security purposes, so please don't read a lot into the actual usefulleness of this information. I'm hoping that I've at least presented the basics of the problem -- i.e. I'd like to reproduce the above report by leveraging the power of T-SQL if possible. There are just pages and pages of VB code used on the client side to make this work and I really don't want to have to dissect someone else's amateurish mess before I begin my redesign. Things are bad enough with the database design that I want to avoid smashing my head against the wall on the client side as well.

Any help is greatly appreciated.

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-16 : 16:22:08

Does this help?

http://www.sqlteam.com/item.asp?ItemID=8866

Not to demoralize you, but the longer you leave the existing table structure in place the more aggravation you'll experience. If you're redesigning a lot of other stuff anyway you might as well do this one too.

tool
Starting Member

26 Posts

Posted - 2002-09-16 : 17:11:22

It's not the prettiest solution but it seems to work (I think!):

DECLARE @table table (family_id varchar(20),
lastname varchar(50),
firstname varchar(50))

INSERT INTO @table VALUES('1.1', 'smith', 'joe')
INSERT INTO @table VALUES('1.2', 'peters', 'jane')
INSERT INTO @table VALUES('1.3', 'smith', 'joe')
INSERT INTO @table VALUES('2.1', 'jones', 'bob')
INSERT INTO @table VALUES('2.2', 'jones', 'betty')
INSERT INTO @table VALUES('2.3', 'hughes', 'april')
INSERT INTO @table VALUES('2.4', 'jones', 'hunter')

SELECT oldest.family_id AS oldest_id, oldest.lastname AS next_id,
not_oldest.family_id AS oldest_last, not_oldest.lastname AS next_last
FROM
(SELECT family_id, lastname
FROM @table
WHERE RIGHT(family_id,LEN(family_id)-CHARINDEX('.',family_id))=1) oldest
INNER JOIN
(SELECT family_id, lastname
FROM @table
WHERE RIGHT(family_id,LEN(family_id)-CHARINDEX('.',family_id))>1) not_oldest
ON LEFT(oldest.family_id,CHARINDEX('.',oldest.family_id))=LEFT(not_oldest.family_id,CHARINDEX('.',oldest.family_id))
ORDER BY oldest.family_id, not_oldest.family_id

You're going to run into sorting problems when you get to family_id's over 9. You could try converting them to decimal type but then 10.10 is less than 10.2, not sure how to deal with that.

I agree with Rob, if it's an option redesign the table!

steelkilt
Constraint Violating Yak Guru

255 Posts

Posted - 2002-09-17 : 10:04:12

Thanks for the tips. What do you think of this idea:

Since the main thrust of these types of reports is to reveal relationships between a core family member and his/her relations, I have added a field the (test) demographics table that declares this relationship. A simple extract from the test demographics table listing the new field FAMILYASSOC:

ID lastname FAMILYASSOC
1.1 jones 1.1
1.2 jones 1.1
1.3 smith 1.1
1.4 jones 1.1
2.1 martin 2.1
2.2 martin 2.1
2.3 hughes 2.1

This way I can set up two VIEWS to run against this table, one that extracts the core family members (i.e. those with *.1 IDs) and one that extracts the rest of the family members (those with IDs that do not end in *.1).

I have successfully reproduced the Excel report format mentioned above running a join against the two VIEWS I've just outlined above. My thinking is that I will utilize the new FAMILYASSOC field in all the VIEWS I create across the database and then run the aforementioned JOIN to come up with the preferred report format. I see the FAMILYASSOC field as a valid and much needed field because it formalizes the relationship between family members.

What do you all think of this approach?

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-17 : 10:16:43

That's a good idea. You didn't have to rewrite the entire table either.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources