Help in Query - 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 2005 Forums

Transact-SQL (2005)

Help in Query

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-08 : 05:05:08

Hi Team, I need help to build this query. i have two tables. One is Tbl_Rules and another one is Tbl_Lastnames. My requirement is for each rule in the rules table, i need to check this rule in lastname table and get the count of matches, percentage of matches with culture in rule table and the next highest match(remaining cultures).

For example, if rule %gupta is taken, it checks in lastname table. Total 4 records(count) is there to match rule %gupta. Now the culture in rule table need to compare the culture in last name table. the culture for %gupta in rule table is INDI. Among 4 records in last name table, only 3 records match the exact culture. so %Match is 75 and next highest is 25-GERM


Tbl_Rules

RType	Rules	Culture
RLL	%gupta	INDI
RLL	%hober	GERM
RLL	%skyn	ITAL

Tbl_LastName

Culture  	Name
FREN	Freskyn
INDI	pangupta
DUTC	Buckskyn
GERM	Heuschober
INDI	Dasgupta
GERM	Lugupta
INDI	Gergupta
GERM	Shober
SCOT	Ruskyn
SCOT	Arskyn


Output

RType	Rules	Culture	TotalCnt	%Match	NextHighest
RLL	%gupta	INDI	4	75	25-GERM
RLL	%hober	GERM	2	100	0
RLL	%skyn	SCOT,ITAL	4	50	25-FREN,25-DUTC

G. Satish

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 11:34:53

[code]SELECT t.RType,
t.Rules,
t.Culture,
t1.TotalCnt,
t1.[%Match],
LEFT(cl.CList,LEN(cl.CList)-1) AS NextHighest

FROM Tbl_Rules t
CROSS APPLY (SELECT COUNT(CASE WHEN Culture = t.Culture THEN 1 ELSE NULL END) AS TotalCnt,
COUNT(CASE WHEN Culture = t.Culture THEN 1 ELSE NULL END)*100.0/COUNT(1) AS [%Match]
FROM Tbl_LastName
WHERE Name LIKE t.Rules
)t1
CROSS APPLY (SELECT CAST( COUNT(Name)*100.0/COUNT(Name) OVER () AS varchar(10)) + '-' + Culture + ','
FROM Tbl_LastName
WHERE Name LIKE t.Rules
AND Culture <> t.Culture
GROUP BY Culture
ORDER BY COUNT(Name) DESC
FOR XML PATH(''))cl(CList)
[/code]

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-07-09 : 03:44:27

Hi Visakh, When i try to execute your query, i am getting this error
"Msg 8124, Level 16, State 1, Line 1
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression."

G. Satish

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources