Tricky SELECT - SQL Server Forums

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)

Tricky SELECT

Author

Topic

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-23 : 08:34:13

Hi all, sorry if the following code looks messy but I hope that you can work out the relationships from the WHERE clause.


SELECT	DISTINCT P.TITLE, P.SURNAME, P.FORENAME, CONVERT(CHAR(10), DATEOFBIRTH, 103) AS DATEOFBIRTH, 
	ISNULL(NATIONALIDNUMBER, 'Not Available') AS NATIONALIDNUMBER, E.EMPLOYEEREFERENCE, PERCENTAGE
FROM	PERSON P,
	EMPLOYEE E,
	SCHEMEMEMBER SM,
	MEMBERBENEFIT MB,
	INTEGERHISTORY IH,
	NOMINATION N
WHERE	P.PERSONUID = E.PERSONUID
AND	E.EMPLOYEEUID = SM.EMPLOYEEUID
AND	SM.MEMBERUID = MB.MEMBERUID
AND	MB.MEMBERBENEFITUID = IH.PARENTUID
AND	MB.MEMBERBENEFITUID *= N.MEMBERBENEFITUID
AND	IH.VALUE IN (4125,4126,4148,4157,4161,4163,5342,5343,5346,5348)
AND	IH.CATID IN (SELECT CATID FROM CATEGORYID WHERE CATIDCODE = 'MBENEVHIST')
AND	SURNAME NOT LIKE 'AATEST%'

The problem is as follows :

The above query returns the following rows (well, 2 records from 3000)

Mr Lowndes William 07/04/1951 Not Available 00004 NULL
Mr Lowndes William 07/04/1951 Not Available 00004 100.00

As you can see, the query is returning 2 rows for this member even though both records are identical except for the percentage. In this case I only want to see one record (i.e. not the one with the NULL). But this of course depends on whether the member has two records or not. If the member has only one record and it has a NULL in PERCENRAGE then I want to keep it.

My second question is, how do you adapt the above query so if a member has two lines with two different percentages it would pace them in a different column. So you have Nomination Perc1, Nomination Perc2 etc...

Hope this is clear and thanks for your help in advance.

Good Luck!

Nazim
A custom title

1408 Posts

Posted - 2003-06-23 : 09:57:18

For a start , this will optimize your query. Always go for Ansi joins .

to actually solve your problem . can v know from which table does percentage comes from. if u can also provide your table schema scripts with some sample data ,v will have our task cut out.

SELECT DISTINCT P.TITLE, P.SURNAME, P.FORENAME,
CONVERT(CHAR(10), DATEOFBIRTH, 103) AS DATEOFBIRTH,
ISNULL(NATIONALIDNUMBER, 'Not Available') AS NATIONALIDNUMBER,
E.EMPLOYEEREFERENCE, PERCENTAGE
FROM PERSON P
inner join EMPLOYEE E
on P.PERSONUID = E.PERSONUID
inner join SCHEMEMEMBER SM
on E.EMPLOYEEUID = SM.EMPLOYEEUID
inner join MEMBERBENEFIT MB
on SM.MEMBERUID = MB.MEMBERUID
inner join INTEGERHISTORY IH
on MB.MEMBERBENEFITUID = IH.PARENTUID
left outer join NOMINATION N
on MB.MEMBERBENEFITUID = N.MEMBERBENEFITUID
where IH.VALUE IN (4125,4126,4148,4157,4161,4163,5342,5343,5346,5348)
AND IH.CATID IN (SELECT CATID FROM CATEGORYID WHERE CATIDCODE = 'MBENEVHIST')
AND SURNAME NOT LIKE 'AATEST%'

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Edited by - nazim on 06/23/2003 09:58:05

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-23 : 10:59:32

I am afriad I can not publish the structure of our database on the internet!

What I would like however is to be able to transpose the data.
The example that I mentioned can still be used.

Mr Lowndes William 07/04/1951 Not Available 00004 50.00
Mr Lowndes William 07/04/1951 Not Available 00004 50.00

What I would like to be able to do is to restructure the result so that it displays one record for the above person with two columns as Percentage1 and Percentage2

If a mamber has 3 records with say 20%, 40% and 20% then we would have Percentage1, Percentage2 and Percentage3 and so on. I know that I did not provide sample data, but any advice would be appreciated.

Thanks in advance.

Good Luck!

mr_mist
Grunnio

1870 Posts

Posted - 2003-06-23 : 11:10:16

Well, you could always do...

SELECT TITLE, SURNAME, FORENAME, DATEOFBIRTH, NATIONALIDNUMBER,
EMPLOYEEREFERENCE, max (PERCENTAGE) as percentage

FROM

(
SELECT DISTINCT P.TITLE, P.SURNAME, P.FORENAME,
CONVERT(CHAR(10), DATEOFBIRTH, 103) AS DATEOFBIRTH,
ISNULL(NATIONALIDNUMBER, 'Not Available') AS NATIONALIDNUMBER,
E.EMPLOYEEREFERENCE, PERCENTAGE
FROM PERSON P
inner join EMPLOYEE E
on P.PERSONUID = E.PERSONUID
inner join SCHEMEMEMBER SM
on E.EMPLOYEEUID = SM.EMPLOYEEUID
inner join MEMBERBENEFIT MB
on SM.MEMBERUID = MB.MEMBERUID
inner join INTEGERHISTORY IH
on MB.MEMBERBENEFITUID = IH.PARENTUID
left outer join NOMINATION N
on MB.MEMBERBENEFITUID = N.MEMBERBENEFITUID
where IH.VALUE IN (4125,4126,4148,4157,4161,4163,5342,5343,5346,5348)
AND IH.CATID IN (SELECT CATID FROM CATEGORYID WHERE CATIDCODE = 'MBENEVHIST')
AND SURNAME NOT LIKE 'AATEST%'
) b

group by TITLE, SURNAME, FORENAME, DATEOFBIRTH, NATIONALIDNUMBER,
EMPLOYEEREFERENCE

-------
Moo.

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-06-23 : 11:22:29

Hmmmm, I am not only interested in the MAX(Percentage). I want all of them.

Any ideas on how to spread the result set so that the data is represented horizontally?

Thanks in advance.

Good Luck!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources