HELP WITH RANKING !( please)

Hello Everybody:

Somebody know how I can create a nested ranking from a analysis services
cube, using MDX?.. i mean, sort a sales ranking that show .how % of my
clients....b.buy only 100 articles......how many clients ...expressed in %
, buy just 200 articles parent level using a measure and then sort
a child level using the same measure... something like:

I got it nex table :
Location client_id Sales article vendor_id
city 1 122230 100 01
City1 122231 200 05
City1 122232 500 02
City1 122233 100 04
City 2 122234 100 02
City2 122235 100 02
City2 122236 200 03
City3 122237 300 01
City3 122238 400 01
City3 122239 200 03

I want show this : 40% clients . Buy 100 articles
30% clients . Buy 200 articles
10% clients .Buy 300 articles
10% clients . Buy 400 articles
10% clients . Buy 500 articles

or this : the 70 % of clients buy between 100 and 200 aticles !!

i.m use sql server 2000 , analysis services 8.0 ,olap , and mdx

PLEASE HELP ME MADE THIS!!!!

write me too at lealy_lenn@.hotmail.com

--
hi all !

Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200509/1Use this logic

declare @.t table(i int)
insert into @.t values(100)
insert into @.t values(200)
insert into @.t values(300)
insert into @.t values(100)
insert into @.t values(400)
insert into @.t values(100)
insert into @.t values(300)
insert into @.t values(600)
insert into @.t values(200)
insert into @.t values(600)
select i, s*100/Total as percentage from (
select i,count(i) as s ,(select count(i) from @.t) as total from @.t
group by i) T

Madhivanan|||Heathon via SQLMonster.com wrote:
> Hello Everybody:
> Somebody know how I can create a nested ranking from a analysis services
> cube, using MDX?.. i mean, sort a sales ranking that show .how % of my
> clients....b.buy only 100 articles......how many clients ...expressed in %
> , buy just 200 articles parent level using a measure and then sort
> a child level using the same measure... something like:
> I got it nex table :
> Location client_id Sales article vendor_id
> city 1 122230 100 01
> City1 122231 200 05
> City1 122232 500 02
> City1 122233 100 04
> City 2 122234 100 02
> City2 122235 100 02
> City2 122236 200 03
> City3 122237 300 01
> City3 122238 400 01
> City3 122239 200 03
>
> I want show this : 40% clients . Buy 100 articles
> 30% clients . Buy 200 articles
> 10% clients .Buy 300 articles
> 10% clients . Buy 400 articles
> 10% clients . Buy 500 articles
>
> or this : the 70 % of clients buy between 100 and 200 aticles !!
> i.m use sql server 2000 , analysis services 8.0 ,olap , and mdx
> PLEASE HELP ME MADE THIS!!!!
> write me too at lealy_lenn@.hotmail.com

You might get a better response in microsoft.public.sqlserver.olap

Simon

Help with nested Query ?

I have three Tables Student, Courses, Marks

Table : Student
Columns
StudentID <PK>
First Name
Last Name

Table : Grades
Columns
StudentID <FK>
Grade

Table : Courses
Columns
StudentID <FK>
CourseID
CourseDesc

Now to get all the course descriptions which this particular student is taking based on the StudentID we do something like this :

SELECT c.courseDesc
FROM Courses c, Student s
WHERE s.StudentID = '100'
AND s.StudentID = c.StudentID

The above will work

But If I need to do it in nested query how can I do it : Something like

SELECT * FROM

(

SELECT c.courseDesc
FROM Courses c, Student s
AND s.StudentID = c.StudentID

)

WHERE s.StudentID = '100'

Thanks for the help.

Harsimrat

If I do something like this, it should work and its not happy

SELECT * FROM

(

SELECT c.courseDesc, s.StudentID

FROM Courses c, Student s

WHERE s.StudentID = c.StudentID

)

WHERE s.StudentID = '100'

|||

You need to give the derived table an alias to get it to work

Code Snippet

SELECT * FROM

(

SELECT c.courseDesc, s.StudentID

FROM Courses c, Student s

WHERE s.StudentID = c.StudentID

) as items

WHERE s.StudentID = '100'

Though, why the subquery?

Help With Nested Query

I am having trouble with the following query.

Important Tables:
Product (table of products)
--ProductID
--ProductName

ProductCategories (Associates a Product with one or more categories)
--ProductID
--CategoryID

Category (table of categories that a product may fall under)
--CategoryID
--CategoryName

Information:

Basically I have a product that falls into two categories. Therefore there are two records in the ProcuctCategories Table. I am trying to create a query that will find all products that are in categories 1 & 2.

Attempted Solution:
SELECT * FROM Product
WHERE (ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =1))
AND
(ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =2))

This returned zero records though it should have returned the product that is in categories 1&2.

I would appreciate any help available.

Thank you,
-PatrickI am trying to create a query that will find all products that are in categories 1 & 2.do a regular many-to-many join, but use GROUP BY on the product, and HAVING to retain only those products which were in more than one category
select ProductName
from Category C
inner
join ProductCategories PC
on C.CategoryID = PC.CategoryID
inner
join Product P
on PC.ProductID = P.ProductID
where C.CategoryID in (1,2)
group
by ProductName
having count(*) > 1|||You are going to kick yourself, but the reason your query failed to return records is because you were trying to compare outer "ProductID"s to inner "CategoryID"s.

...WHERE (ProductID IN (SELECT CategoryID...???

You can rewrite your query more simply like this:

select Product.*
from Product
inner join ProductCategories Cat1 on Product.ProductID = Cat1.ProductID
inner join ProductCategories Cat2 on Product.ProductID = Cat2.ProductID
where Cat1.CategoryID = 1 and Cat2.CategoryID = 2

Use the DISTINCT keywork if the query returns multiple records.

help with nested Query

Hi
I have 2 tables. The first has employee information and the second has
payroll information. I need to find out people who are not in the
payroll but in the employee table.
Since the payroll has multiple instances i have to filter it and find
out for each payroll.
I don't think i have explained it very well so here is the data set.
hope someone can help me with this.
Thanks in advance
prit

Tbl Employee
PlanIDSSN
1001111111111
1001222222222
1001333333333

TblPayrolldetail
IDNumPlanID SSN
11001111111111
11001222222222
21001222222222
21001333333333

Required RESULT required(Missing employees from payroll)
IDNumSSN
1333333333
2111111111I think this could be what you're looking for:

SELECT I.idnum, E.ssn
FROM
(SELECT DISTINCT idnum
FROM PayrollDetail) AS I
CROSS JOIN Employees AS E
LEFT JOIN PayrollDetail AS D
ON I.idnum = D.idnum
AND E.ssn = D.ssn
WHERE D.idnum IS NULL

If you have another table for the entity represented by Idnum then use that
table in place of the derived table "I".

--
David Portas
SQL Server MVP
--

Help with nested inner joins

Hi,
I want to find some people in my SQLServer 2000 database. It's a quite large
database, with approx 200 tables.
Together with the person, I want some information attached to him. However,
this information is in another table that can be reached via some other
tables.
My question is:
How do I most efficiently extract this information? Is inner joins a good
option or is there a better way. If I need information from table1 and table
5, is this a good idea?
SELECT table1.ID, table5.info
FROM table1
INNER JOIN table2 ON table1.xxx = table2.xxx
INNER JOIN table3 ON table2.xxx = table3.xxx
INNER JOIN table4 ON table3.xxx = table4.xxx
INNER JOIN table5 ON table4.xxx = table5.xxx
Thanks,
Mats-LennartWithout seeing DDL, I can only go on assumptions...
I am assuming that the only logical way to connect tabel1 to table5 is via
tables 2, 3, and 4. Based on this, I believe the SQL below is the only way
to get the data you want.
If you post DDL (table creates, primary and foreign keys) for the tables
involved, folks may be able to explain another way to do it, or possibly
changes to your database structure.
"Mats-Lennart Hansson" <ap_skallen@.hotmail.com> wrote in message
news:e1rQtquNGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I want to find some people in my SQLServer 2000 database. It's a quite
large
> database, with approx 200 tables.
> Together with the person, I want some information attached to him.
However,
> this information is in another table that can be reached via some other
> tables.
> My question is:
> How do I most efficiently extract this information? Is inner joins a good
> option or is there a better way. If I need information from table1 and
table
> 5, is this a good idea?
> SELECT table1.ID, table5.info
> FROM table1
> INNER JOIN table2 ON table1.xxx = table2.xxx
> INNER JOIN table3 ON table2.xxx = table3.xxx
> INNER JOIN table4 ON table3.xxx = table4.xxx
> INNER JOIN table5 ON table4.xxx = table5.xxx
> Thanks,
> Mats-Lennart
>

Help with Nested Case Statements

Hi,

I am trying to write a query which is something like this :

CASE WHEN CASE WHEN crp.Title_Code = crp1.Title_Code
THEN ar.Description + '|' + ar1.Description
WHEN crp.Title_Code = crp2.Title_Code
THEN ar.Description + '|' + ar2.Description
WHEN crp.Title_Code = crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END

WHEN CASE WHEN crp1.Title_Code = crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END

WHEN CASE WHEN crp2.Title_Code = crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END

END AS Reason_Code_Description

But this is obviously not correct because the WHEN statements do not have a THEN statement - but I dont have anything to do in THEN statement becuase the nested CASE statements take care of everything. Can anybody please help me modify the query so that it works?

Thanks !!

It is not clear what you are attempting to accomplish. Perhaps if you were to present the entire concept, and the entire query, we might be better able to help you.|||

SELECT CASE crp.Title_Code WHEN crp1.Title_Code
THEN ar.Description + '|' + ar1.Description,
WHEN crp2.Title_Code
THEN ar.Description + '|' + ar2.Description,
WHEN crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END
UNION
SELECT CASE crp1.Title_Code WHEN crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description ,
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END
UNION
SELECT CASE crp2.Title_Code WHEN crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END

Of course you need to incorporate your JOINS.

Just my twist on it,

Adamus

|||

Agreed. Can you at least make it clear if you are trying to get one value back, or three? Each of the WHEN clauses needs a boolean expression to determin if it is used. So if you want one value, it should be something like

CASE WHEN <boolean condition>
THEN CASE WHEN crp.Title_Code = crp1.Title_Code
THEN ar.Description + '|' + ar1.Description
WHEN crp.Title_Code = crp2.Title_Code
THEN ar.Description + '|' + ar2.Description
WHEN crp.Title_Code = crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END

WHEN <boolean condition>
THEN CASE WHEN crp1.Title_Code = crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END

WHEN <boolean condition>
THEN CASE WHEN crp2.Title_Code = crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END

END AS Reason_Code_Description

|||

I think I need to give the complete description here..This is the whole query - I modified it to get it to working..But my query returns only one record whereas it should return 4 different records if Reason_Code_ID1,Reason_Code_ID2,Reason_Code_ID3,Reason_Code_ID4 have different values for look up field of Title_Code and should concatenate the description for ones that have same Title_Code value.

Select CASE WHEN ar1.Description is NOT NULL AND ar2.Description is not null AND ar3.Description is not null
THEN CASE WHEN crp.Title_Code = crp1.Title_Code
THEN ar.Description + '|' + ar1.Description
WHEN crp.Title_Code = crp2.Title_Code
THEN ar.Description + '|' + ar2.Description
WHEN crp.Title_Code = crp3.Title_Code
THEN ar.Description + '|' + ar3.Description
ELSE ar.Description END
WHEN ar2.Description is not null AND ar3.Description is not null
THEN CASE WHEN crp1.Title_Code = crp2.Title_Code
THEN ar1.Description + '|' + ar2.Description
WHEN crp1.Title_Code = crp3.Title_Code
THEN ar1.Description + '|' + ar3.Description
ELSE ar1.Description END
WHEN ar3.Description is not null THEN
CASE WHEN crp2.Title_Code = crp3.Title_Code
THEN ar2.Description + '|' + ar3.Description
ELSE ar2.Description END
END AS Reason_Code_Description,
crp.CRP_Score_Reason_Code_ID,
crp.Title_Code
From ADF_CRP_Score s (nolock)
LEFT OUTER JOIN CRP_Score_Reason_Code crp
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm (nolock)
JOIN CCR..ADF_Reference ar (nolock)
ON arm.Lookup_ID = ar.Lookup_ID AND ar.Language = 'F'
ON arm.Bureau_Code_ID = '1'
AND arm.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar.Code = crp.CRP_Score_Reason_Code_ID
AND arm.Segment = 'CRP'
ON (crp.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID1)
LEFT OUTER JOIN CRP_Score_Reason_Code crp1
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm1 (nolock)
JOIN CCR..ADF_Reference ar1 (nolock)
ON arm1.Lookup_ID = ar1.Lookup_ID AND ar1.Language = 'F'
ON arm1.Bureau_Code_ID = '1'
AND arm1.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar1.Code = crp1.CRP_Score_Reason_Code_ID
AND arm1.Segment = 'CRP'
ON (crp1.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID2)
LEFT OUTER JOIN CRP_Score_Reason_Code crp2
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm2 (nolock)
JOIN CCR..ADF_Reference ar2 (nolock)
ON arm2.Lookup_ID = ar2.Lookup_ID AND ar2.Language = 'F'
ON arm2.Bureau_Code_ID = '1'
AND arm2.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar2.Code = crp2.CRP_Score_Reason_Code_ID
AND arm2.Segment = 'CRP'
ON (crp2.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID3)
LEFT OUTER JOIN CRP_Score_Reason_Code crp3
LEFT OUTER JOIN CCR..ADF_Reference_Mapping arm3 (nolock)
JOIN CCR..ADF_Reference ar3 (nolock)
ON arm3.Lookup_ID = ar3.Lookup_ID AND ar3.Language = 'F'
ON arm3.Bureau_Code_ID = '1'
AND arm3.Segment_Field = 'CRP_Score_Reason_Code_ID'
AND ar3.Code = crp3.CRP_Score_Reason_Code_ID
AND arm3.Segment = 'CRP'
ON (crp3.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID4)

Please help.

Thanks!

|||

That is quite a SQL statement :) Can you give us some sample DDL and INSERT statements to make this more clear (and more simple) and sample results (expecting 2 rows, not 4 :)

Thanks

|||

There must be a more difficult way to code this. :)

You might be able to filter this query down in the WHERE clause instead of having a multitude of JOINS and SELECT CASE

What exactly is the goal of this query?

Adamus

|||

SELECT rc.Reason_Code_Description FROM
ADF_CRP_Score s (nolock)
LEFT OUTER JOIN CRP_Score_Reason_Code rc ON
(rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID1)
OR (rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID2)
OR (rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID3)
OR (rc.CRP_Score_Reason_Code_ID = s.CRP_Score_Reason_Code_ID4)

This was the initail query that returnd following results :

CBSSD|Number of purchases in the previous 12 months.||||D|
CBSSD|Total monthly payments.||||E|
CBSSD|Total high credit .||||K|
CBSSD|Number of sales.||||L|

I had to modify the query so that if Title_code (D,E,K,L) in above example are same , like below

CBSSD|Number of purchases in the previous 12 months.||||D|
CBSSD| Age of oldest retail account.||||D|
CBSSD|Total high credit .||||K|
CBSSD|Number of sales.||||L|

then the data segments should be concatenated into one to give this result:

CBSSD|Number of purchases in the previous 12 months.| Age of oldest retail account||||D|
CBSSD|Total high credit .||||K|
CBSSD|Number of sales.||||L|

What my modified query returns is :

CBSSD|Number of purchases in the previous 12 months.| Age of oldest retail account||||D|

And what I would like to return is :

CBSSD|Number of purchases in the previous 12 months.| Age of oldest retail account||||D|
CBSSD|Total high credit .|Total balance||||K|
CBSSD|Number of sales.||||L|

I know its a bit confusing and complex and that is why I am here ..I am totally confused...:)

Please help.

Thanks!

|||

Although the below example isn't a complete solution to your problem [as I don't have either the table definitions or the patience to unpick your CASE statements :) ], it should help to start you off thinking of solutions along similar lines.

The example requires SQL Server 2005.

Chris

DECLARE @.MyTable TABLE ([Desc] VARCHAR(100) NULL, [Code] CHAR(1))
INSERT INTO @.MyTable
SELECT 'Number of purchases in the previous 12 months.', 'D' UNION ALL
SELECT 'Age of oldest retail account', 'D' UNION ALL
SELECT NULL, 'D' UNION ALL
SELECT NULL, 'D' UNION ALL
SELECT 'Total high credit.', 'K' UNION ALL
SELECT NULL, 'K' UNION ALL
SELECT 'Total balance', 'K' UNION ALL
SELECT NULL,'K' UNION ALL
SELECT 'Number of sales.', 'L' UNION ALL
SELECT NULL, 'L' UNION ALL
SELECT NULL, 'L' UNION ALL
SELECT NULL, 'L'

DECLARE @.MyCodeTable TABLE ([Code] CHAR(1))
INSERT INTO @.MyCodeTable
SELECT 'D' UNION
SELECT 'K' UNION
SELECT 'L'

SELECT 'CBSSD|'
+ REPLACE(
REPLACE(
(SELECT REPLACE(ISNULL(mt.[Desc], '') + '|', ' ', '~') AS [data()]
FROM @.MyTable mt
WHERE mt.[Code] = mct.[Code]
ORDER BY 1 DESC
FOR XML PATH('')), ' ', '')
, '~', ' ')
+ mct.[Code] + '|' AS [String]
FROM @.MyCodeTable mct
/*Output
CBSSD|Number of purchases in the previous 12 months.|Age of oldest retail account|||D|
CBSSD|Total high credit.|Total balance|||K|
CBSSD|Number of sales.||||L|
*/