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 query! Ranking of Sum() column

If anyone can help with this, I'd be most appreciative.
Basically, I'm trying to sum a column based on a unique ID and then
find out the RANK of that record in the table.
Here's the table:
Points
- PointID
- UserID
- Points
There can be multiple records with the same UserID.
Here's the query I'm using now:
SELECT Sum(Points), UserID FROM Points Group By UserID Order By
Sum(Points) desc
This basically returns ALL the records with the Points summed. I think
loop through in my code to find what row number a specific ID is. This
is NOT efficient and is slowing down my site considerably.
This is a query someone recommended I used:
SELECT COUNT(*) AS rank FROM Points WHERE (sum(points) >= (SELECT
sum(points) FROM Points WHERE UserId = 65))
SQL Server doesn't like that query, though, because of the aggregate
function. I was searching for the rank of UserID 65.
If anyone could help me with this I'd appreciate it. I ONLY need the
rank of one record, so hoepfully I don't need to use a temp table for
this.
Thanks,
Andyhttp://www.aspfaq.com/show.asp?id=2427
<andymilk@.gmail.com> wrote in message
news:1146492469.413328.220320@.j33g2000cwa.googlegroups.com...
> If anyone can help with this, I'd be most appreciative.
> Basically, I'm trying to sum a column based on a unique ID and then
> find out the RANK of that record in the table.
> Here's the table:
> Points
> - PointID
> - UserID
> - Points
> There can be multiple records with the same UserID.
> Here's the query I'm using now:
> SELECT Sum(Points), UserID FROM Points Group By UserID Order By
> Sum(Points) desc
> This basically returns ALL the records with the Points summed. I think
> loop through in my code to find what row number a specific ID is. This
> is NOT efficient and is slowing down my site considerably.
> This is a query someone recommended I used:
> SELECT COUNT(*) AS rank FROM Points WHERE (sum(points) >= (SELECT
> sum(points) FROM Points WHERE UserId = 65))
> SQL Server doesn't like that query, though, because of the aggregate
> function. I was searching for the rank of UserID 65.
> If anyone could help me with this I'd appreciate it. I ONLY need the
> rank of one record, so hoepfully I don't need to use a temp table for
> this.
> Thanks,
> Andy
>|||Try,
-- sql server 2000
create view v1
as
select UserID, sum(Points) as sum_Points
from t1
group by UserID
go
select * from v1
go
select
count(*) as rank,
a.UserID,
a.sum_Points
from
v1 as a inner join v1 as b
on (a.sum_Points < b.sum_Points)
or (a.sum_Points = b.sum_Points and a.UserID >= b.UserID)
group by
a.UserID,
a.sum_Points
order by
rank
go
AMB
"andymilk@.gmail.com" wrote:

> If anyone can help with this, I'd be most appreciative.
> Basically, I'm trying to sum a column based on a unique ID and then
> find out the RANK of that record in the table.
> Here's the table:
> Points
> - PointID
> - UserID
> - Points
> There can be multiple records with the same UserID.
> Here's the query I'm using now:
> SELECT Sum(Points), UserID FROM Points Group By UserID Order By
> Sum(Points) desc
> This basically returns ALL the records with the Points summed. I think
> loop through in my code to find what row number a specific ID is. This
> is NOT efficient and is slowing down my site considerably.
> This is a query someone recommended I used:
> SELECT COUNT(*) AS rank FROM Points WHERE (sum(points) >= (SELECT
> sum(points) FROM Points WHERE UserId = 65))
> SQL Server doesn't like that query, though, because of the aggregate
> function. I was searching for the rank of UserID 65.
> If anyone could help me with this I'd appreciate it. I ONLY need the
> rank of one record, so hoepfully I don't need to use a temp table for
> this.
> Thanks,
> Andy
>

Help with query! Ranking of Sum() column

Are you sure this works with an aggregate function?
Also, can I return ONE row and get the correct rank?Can you provide some useful DDL, sample data, and desired output? (
http://www.aspfaq.com/5006 )
I'm having a hard time visualizing " I think loop through in my code to find
what row number a specific ID is."
A
<andymilk@.gmail.com> wrote in message
news:1146493870.042951.123210@.i39g2000cwa.googlegroups.com...
> Are you sure this works with an aggregate function?
> Also, can I return ONE row and get the correct rank?
>

Help with query

Hi,
I have a stored procedure (posted below) that returns a club ranking list
with fatsest to slowest time for a swim club based on
Stroke,Distance,Course,Gender,Age. Number of rows in the ranking is based on
the @.Rowcount variable passed in.
I would like to expand this query to return a the (single) fastest time per
strokeID held in the BBMD_Strokes table. I understand that I could use a
CURSOR with a SELECT StrokeID from BBMD_strokes and loop through the same
query , but have read that CURSORS should be avoided due to poor
performance. Is there a prefered option to solve this ?
Niclas
CREATE procedure dbo.BBMD_GetEventRecord
@.StrokeID int,
@.DistanceID int,
@.CourseID int,
@.GenderID int,
@.AgeID int,
@.RowCount int
AS
Set ROWCOUNT @.Rowcount
SELECT D.DistanceName + ' ' + S.StrokeName as EventName,U.LastName + ', ' +
U.firstname as Swimmer,
R.Result,G.GalaName, G.StartDate,X.DOB
FROM BBMD_Results R
JOIN BBMD_Events E ON R.Eventid=E.EventID
JOIN BBMD_Galas G ON R.GalaID=G.GalaID
JOIN BBMD_Strokes S ON E.strokeID=S.strokeID
JOIN BBMD_Distances D ON E.DistanceID=D.DistanceID
JOIN Users U ON R.UserID=U.UserID
JOIN BBMD_ExtUser X ON R.USERID=X.UserID
JOIN (SELECT R.UserID,MIN(R.Result) as RES
FROM BBMD_Results R
JOIN BBMD_Events E ON R.EventID=E.EventID
JOIN BBMD_ExtUser X ON R.UserID=X.UserID
JOIN BBMD_Galas G ON R.GalaID=G.GalaID
WHERE
E.StrokeID=@.StrokeID AND
E.Distanceid=@.DistanceID AND
E.Genderid=@.GenderID AND
E.Courseid=@.CourseID AND
R.Resulttypeid=1 AND
DATEDIFF (YEAR, X.DOB, G.StartDate ) - CASE
WHEN 100 * MONTH(G.StartDate) + DAY(G.StartDate)
< 100 * MONTH(X.DOB) + DAY(X.DOB)
THEN 1 ELSE 0 END
BETWEEN (SELECT YearMin From BBMD_YearGroups
WHERE YearGroupID= @.Age)
AND
(SELECT YearMax From BBMD_YearGroups WHERE YearGroupID= @.AgeID)
Group By R.UserID) AS MinR ON minR.Res=R.result
AND minR.UserID=R.UserID
GROUP BY U.Lastname,U.firstname,G.GalaName, MinR.Res,R.Result,
S.StrokeName,D.DistanceName, G.StartDate,X.DOB
ORDER BY RESULT
GOPlease send the table DDL and sample data as INSERT statements, and what the
expected output looks like. Without that information, we are guessing and
the quality of help is sub-optimal..
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:%23roRJf5lGHA.1488@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I have a stored procedure (posted below) that returns a club ranking list
> with fatsest to slowest time for a swim club based on
> Stroke,Distance,Course,Gender,Age. Number of rows in the ranking is based
> on the @.Rowcount variable passed in.
> I would like to expand this query to return a the (single) fastest time
> per strokeID held in the BBMD_Strokes table. I understand that I could use
> a CURSOR with a SELECT StrokeID from BBMD_strokes and loop through the
> same query , but have read that CURSORS should be avoided due to poor
> performance. Is there a prefered option to solve this ?
> Niclas
> CREATE procedure dbo.BBMD_GetEventRecord
> @.StrokeID int,
> @.DistanceID int,
> @.CourseID int,
> @.GenderID int,
> @.AgeID int,
> @.RowCount int
> AS
> Set ROWCOUNT @.Rowcount
> SELECT D.DistanceName + ' ' + S.StrokeName as EventName,U.LastName + ', '
> + U.firstname as Swimmer,
> R.Result,G.GalaName, G.StartDate,X.DOB
> FROM BBMD_Results R
> JOIN BBMD_Events E ON R.Eventid=E.EventID
> JOIN BBMD_Galas G ON R.GalaID=G.GalaID
> JOIN BBMD_Strokes S ON E.strokeID=S.strokeID
> JOIN BBMD_Distances D ON E.DistanceID=D.DistanceID
> JOIN Users U ON R.UserID=U.UserID
> JOIN BBMD_ExtUser X ON R.USERID=X.UserID
> JOIN (SELECT R.UserID,MIN(R.Result) as RES
> FROM BBMD_Results R
> JOIN BBMD_Events E ON R.EventID=E.EventID
> JOIN BBMD_ExtUser X ON R.UserID=X.UserID
> JOIN BBMD_Galas G ON R.GalaID=G.GalaID
> WHERE
> E.StrokeID=@.StrokeID AND
> E.Distanceid=@.DistanceID AND
> E.Genderid=@.GenderID AND
> E.Courseid=@.CourseID AND
> R.Resulttypeid=1 AND
> DATEDIFF (YEAR, X.DOB, G.StartDate ) - CASE
> WHEN 100 * MONTH(G.StartDate) + DAY(G.StartDate)
> < 100 * MONTH(X.DOB) + DAY(X.DOB)
> THEN 1 ELSE 0 END
> BETWEEN (SELECT YearMin From BBMD_YearGroups
> WHERE YearGroupID= @.Age)
> AND
> (SELECT YearMax From BBMD_YearGroups WHERE YearGroupID= @.AgeID)
> Group By R.UserID) AS MinR ON minR.Res=R.result
> AND minR.UserID=R.UserID
> GROUP BY U.Lastname,U.firstname,G.GalaName, MinR.Res,R.Result,
> S.StrokeName,D.DistanceName, G.StartDate,X.DOB
> ORDER BY RESULT
> GO
>|||
> @.StrokeID int,
> @.DistanceID int,
> @.CourseID int,
> @.GenderID int,
> @.AgeID int,
> @.RowCount int
Why is everything in your world an identifier? Explain what an
"age_id" is? Likewise, what is a gender_id? Gee, everyone else uses
an ISO gender_code.
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.