Mega / GIga

Hi Friends,
How can i transform the following query to return size in
Gigabytes ?
str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
thx
Johnny
Divide by 1024?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Johnny" <johnny_silvestre@.yahoo.de> wrote in message
news:942601c4339b$9a47f110$a401280a@.phx.gbl...
> Hi Friends,
> How can i transform the following query to return size in
> Gigabytes ?
> str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
> thx
> Johnny
|||divide by 1024

Mega / GIga

Hi Friends,
How can i transform the following query to return size in
Gigabytes ?
str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
thx
JohnnyDivide by 1024?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Johnny" <johnny_silvestre@.yahoo.de> wrote in message
news:942601c4339b$9a47f110$a401280a@.phx.gbl...
> Hi Friends,
> How can i transform the following query to return size in
> Gigabytes ?
> str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
> thx
> Johnny|||divide by 1024

Mega / GIga

Hi Friends,
How can i transform the following query to return size in
Gigabytes ?
str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
thx
JohnnyDivide by 1024?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Johnny" <johnny_silvestre@.yahoo.de> wrote in message
news:942601c4339b$9a47f110$a401280a@.phx.gbl...
> Hi Friends,
> How can i transform the following query to return size in
> Gigabytes ?
> str(sum(convert(dec(15),v.size))* 8192 / 1048576,10,2)+'MB'
> thx
> Johnny|||divide by 1024

median query without using function

I have a table (cars) with 3 fields:
VIN, Class, sell_price
101, sports, 10000
102, sports, 11000
103, luxury, 9000
104, sports, 11000
105, sports, 11000
106, luxury, 5000
107, sports, 11000
108, sports, 11000
109, luxury, 9000
i need to write a query that WITHOUT USING A FUNCTION will return the
median selling price for each class of car. result should look like:
Class, Med_Price
luxury, 9000
sports, 11000
thanks to all u SQLers
>From your sample data it looks like you want the most commonly
occurring price for each vehicle (I thought this was
called the "mode", not the "median").
CREATE TABLE Cars(VIN INT,Class VARCHAR(10),sell_price INT)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(101, 'sports', 10000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(102, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(103, 'luxury', 9000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(104, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(105, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(106, 'luxury', 5000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(107, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(108, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(109, 'luxury', 9000)
GO
CREATE VIEW CarNums
AS
SELECT Class,sell_price,COUNT(*) as Num
FROM Cars
GROUP BY Class,sell_price
GO
SELECT c1.Class,c1.sell_price AS Med_Price
FROM CarNums c1
INNER JOIN (
SELECT Class,MAX(Num)
FROM CarNums
GROUP BY Class) C2(Class,Num) ON C2.Class=C1.Class
AND C2.Num=C1.Num
|||My mistake, you can get the medians using this
CREATE VIEW CarRank
AS
SELECT c1.VIN,
c1.Class,
c1.sell_price,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class
AND ((c2.sell_price<c1.sell_price)
OR (c2.sell_price=c1.sell_price AND c2.VIN<=c1.VIN))) as
Rank,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class) as MaxRank
FROM Cars c1
GO
SELECT Class,AVG(sell_price) AS Med_Price
FROM CarRank
WHERE Rank IN ((MaxRank+1)/2,(MaxRank/2)+1)
GROUP BY Class
|||I have a whoel chapter on verious ways to do this in SQL FOR SMARTIES.
Here is one answer.
Median with Characteristic Function
Anatoly Abramovich, Yelena Alexandrova, and Eugene Birger presented a
series of articles in SQL Forum magazine on computing the median (SQL
Forum 1993, 1994). They define a characteristic function, which they
call delta, using the Sybase sign() function. The delta or
characteristic function accepts a Boolean expression as an argument and
returns a 1 if it is TRUE and a zero if it is FALSE or UNKNOWN.
In SQL-92 we have a CASE expression, which can be used to construct the
delta function. This is new to SQL-92, but you can find vendor
functions of the form IF...THEN...ELSE that behave like the condition
expression in Algol or like the question markPcolon operator in C.
The authors also distinguish between the statistical median, whose
value must be a member of the set, and the financial median, whose
value is the average of the middle two members of the set. A
statistical median exists when there is an odd number of items in the
set. If there is an even number of items, you must decide if you want
to use the highest value in the lower half (they call this the left
median) or the lowest value in the upper half (they call this the right
median).
The left statistical median of a unique column can be found with this
query:
SELECT P1.bin
FROM Parts AS P1, Parts AS P2
GROUP BY P1.bin
HAVING SUM(CASE WHEN (P2.bin <= P1.bin) THEN 1 ELSE 0 END)
= (COUNT(*) + 1) / 2;
Changing the direction of the theta test in the HAVING clause will
allow you to pick the right statistical median if a central element
does not exist in the set. You will also notice something else about
the median of a set of unique values: It is usually meaningless. What
does the median bin number mean, anyway? A good rule of thumb is that
if it does not make sense as an average, it does not make sense as a
median.
The statistical median of a column with duplicate values can be found
with a query based on the same ideas, but you have to adjust the HAVING
clause to allow for overlap; thus, the left statistical median is found
by
SELECT P1.weight
FROM Parts AS P1, Parts AS P2
GROUP BY P1.weight
HAVING SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1);
Notice that here the left and right medians can be the same, so there
is no need to pick one over the other in many of the situations where
you have an even number of items. Switching the comparison operators in
the two CASE expressions will give you the right statistical median.
The author's query for the financial median depends on some Sybase
features that cannot be found in other products, so I would recommend
using a combination of the right and left statistical medians to return
a set of values about the center of the data, and then averaging them,
thus:
SELECT AVG(P1.weight)
FROM Parts AS P1, Parts AS P2
HAVING (SUM(CASE WHEN P2.weight <= P1.weight -- left median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1))
OR (SUM(CASE WHEN P2.weight >= P1.weight -- right median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1));
An optimizer may be able to reduce this expression internally, since
the expressions involved with COUNT(*) are constants. This entire query
could be put into a FROM clause and the average taken of the one or two
rows in the result to find the financial median. In SQL-89, you would
have to define this as a VIEW and then take the average.
If you have SQL-2005, you can try something like (untested):
SELECT AVG(x),
ROW_NUMBER () OVER (ORDER BY x ASC) AS hi,
ROW_NUMBER () OVER (ORDER BY x DESC) AS lo,
FROM Foobar
WHERE hi IN (lo, lo-1, lo+1);
|||i don't do homework for college students.

median query without using function

I have a table (cars) with 3 fields:
VIN, Class, sell_price
101, sports, 10000
102, sports, 11000
103, luxury, 9000
104, sports, 11000
105, sports, 11000
106, luxury, 5000
107, sports, 11000
108, sports, 11000
109, luxury, 9000
i need to write a query that WITHOUT USING A FUNCTION will return the
median selling price for each class of car. result should look like:
Class, Med_Price
luxury, 9000
sports, 11000
thanks to all u SQLers>From your sample data it looks like you want the most commonly
occurring price for each vehicle (I thought this was
called the "mode", not the "median").
CREATE TABLE Cars(VIN INT,Class VARCHAR(10),sell_price INT)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(101, 'sports', 10000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(102, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(103, 'luxury', 9000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(104, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(105, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(106, 'luxury', 5000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(107, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(108, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(109, 'luxury', 9000)
GO
CREATE VIEW CarNums
AS
SELECT Class,sell_price,COUNT(*) as Num
FROM Cars
GROUP BY Class,sell_price
GO
SELECT c1.Class,c1.sell_price AS Med_Price
FROM CarNums c1
INNER JOIN (
SELECT Class,MAX(Num)
FROM CarNums
GROUP BY Class) C2(Class,Num) ON C2.Class=C1.Class
AND C2.Num=C1.Num|||My mistake, you can get the medians using this
CREATE VIEW CarRank
AS
SELECT c1.VIN,
c1.Class,
c1.sell_price,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class
AND ((c2.sell_price<c1.sell_price)
OR (c2.sell_price=c1.sell_price AND c2.VIN<=c1.VIN))) as
Rank,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class) as MaxRank
FROM Cars c1
GO
SELECT Class,AVG(sell_price) AS Med_Price
FROM CarRank
WHERE Rank IN ((MaxRank+1)/2,(MaxRank/2)+1)
GROUP BY Class|||I have a whoel chapter on verious ways to do this in SQL FOR SMARTIES.
Here is one answer.
Median with Characteristic Function
Anatoly Abramovich, Yelena Alexandrova, and Eugene Birger presented a
series of articles in SQL Forum magazine on computing the median (SQL
Forum 1993, 1994). They define a characteristic function, which they
call delta, using the Sybase sign() function. The delta or
characteristic function accepts a Boolean expression as an argument and
returns a 1 if it is TRUE and a zero if it is FALSE or UNKNOWN.
In SQL-92 we have a CASE expression, which can be used to construct the
delta function. This is new to SQL-92, but you can find vendor
functions of the form IF...THEN...ELSE that behave like the condition
expression in Algol or like the question markPcolon operator in C.
The authors also distinguish between the statistical median, whose
value must be a member of the set, and the financial median, whose
value is the average of the middle two members of the set. A
statistical median exists when there is an odd number of items in the
set. If there is an even number of items, you must decide if you want
to use the highest value in the lower half (they call this the left
median) or the lowest value in the upper half (they call this the right
median).
The left statistical median of a unique column can be found with this
query:
SELECT P1.bin
FROM Parts AS P1, Parts AS P2
GROUP BY P1.bin
HAVING SUM(CASE WHEN (P2.bin <= P1.bin) THEN 1 ELSE 0 END)
= (COUNT(*) + 1) / 2;
Changing the direction of the theta test in the HAVING clause will
allow you to pick the right statistical median if a central element
does not exist in the set. You will also notice something else about
the median of a set of unique values: It is usually meaningless. What
does the median bin number mean, anyway? A good rule of thumb is that
if it does not make sense as an average, it does not make sense as a
median.
The statistical median of a column with duplicate values can be found
with a query based on the same ideas, but you have to adjust the HAVING
clause to allow for overlap; thus, the left statistical median is found
by
SELECT P1.weight
FROM Parts AS P1, Parts AS P2
GROUP BY P1.weight
HAVING SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1);
Notice that here the left and right medians can be the same, so there
is no need to pick one over the other in many of the situations where
you have an even number of items. Switching the comparison operators in
the two CASE expressions will give you the right statistical median.
The author's query for the financial median depends on some Sybase
features that cannot be found in other products, so I would recommend
using a combination of the right and left statistical medians to return
a set of values about the center of the data, and then averaging them,
thus:
SELECT AVG(P1.weight)
FROM Parts AS P1, Parts AS P2
HAVING (SUM(CASE WHEN P2.weight <= P1.weight -- left median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1))
OR (SUM(CASE WHEN P2.weight >= P1.weight -- right median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1));
An optimizer may be able to reduce this expression internally, since
the expressions involved with COUNT(*) are constants. This entire query
could be put into a FROM clause and the average taken of the one or two
rows in the result to find the financial median. In SQL-89, you would
have to define this as a VIEW and then take the average.
If you have SQL-2005, you can try something like (untested):
SELECT AVG(x),
ROW_NUMBER () OVER (ORDER BY x ASC) AS hi,
ROW_NUMBER () OVER (ORDER BY x DESC) AS lo,
FROM Foobar
WHERE hi IN (lo, lo-1, lo+1);|||i don't do homework for college students.sql

median query without using function

I have a table (cars) with 3 fields:
VIN, Class, sell_price
101, sports, 10000
102, sports, 11000
103, luxury, 9000
104, sports, 11000
105, sports, 11000
106, luxury, 5000
107, sports, 11000
108, sports, 11000
109, luxury, 9000
i need to write a query that WITHOUT USING A FUNCTION will return the
median selling price for each class of car. result should look like:
Class, Med_Price
luxury, 9000
sports, 11000
thanks to all u SQLers>From your sample data it looks like you want the most commonly
occurring price for each vehicle (I thought this was
called the "mode", not the "median").
CREATE TABLE Cars(VIN INT,Class VARCHAR(10),sell_price INT)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(101, 'sports', 10000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(102, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(103, 'luxury', 9000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(104, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(105, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(106, 'luxury', 5000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(107, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(108, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(109, 'luxury', 9000)
GO
CREATE VIEW CarNums
AS
SELECT Class,sell_price,COUNT(*) as Num
FROM Cars
GROUP BY Class,sell_price
GO
SELECT c1.Class,c1.sell_price AS Med_Price
FROM CarNums c1
INNER JOIN (
SELECT Class,MAX(Num)
FROM CarNums
GROUP BY Class) C2(Class,Num) ON C2.Class=C1.Class
AND C2.Num=C1.Num|||My mistake, you can get the medians using this
CREATE VIEW CarRank
AS
SELECT c1.VIN,
c1.Class,
c1.sell_price,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class
AND ((c2.sell_price<c1.sell_price)
OR (c2.sell_price=c1.sell_price AND c2.VIN<=c1.VIN))) as
Rank,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class) as MaxRank
FROM Cars c1
GO
SELECT Class,AVG(sell_price) AS Med_Price
FROM CarRank
WHERE Rank IN ((MaxRank+1)/2,(MaxRank/2)+1)
GROUP BY Class|||I have a whoel chapter on verious ways to do this in SQL FOR SMARTIES.
Here is one answer.
Median with Characteristic Function
Anatoly Abramovich, Yelena Alexandrova, and Eugene Birger presented a
series of articles in SQL Forum magazine on computing the median (SQL
Forum 1993, 1994). They define a characteristic function, which they
call delta, using the Sybase sign() function. The delta or
characteristic function accepts a Boolean expression as an argument and
returns a 1 if it is TRUE and a zero if it is FALSE or UNKNOWN.
In SQL-92 we have a CASE expression, which can be used to construct the
delta function. This is new to SQL-92, but you can find vendor
functions of the form IF...THEN...ELSE that behave like the condition
expression in Algol or like the question markPcolon operator in C.
The authors also distinguish between the statistical median, whose
value must be a member of the set, and the financial median, whose
value is the average of the middle two members of the set. A
statistical median exists when there is an odd number of items in the
set. If there is an even number of items, you must decide if you want
to use the highest value in the lower half (they call this the left
median) or the lowest value in the upper half (they call this the right
median).
The left statistical median of a unique column can be found with this
query:
SELECT P1.bin
FROM Parts AS P1, Parts AS P2
GROUP BY P1.bin
HAVING SUM(CASE WHEN (P2.bin <= P1.bin) THEN 1 ELSE 0 END)
= (COUNT(*) + 1) / 2;
Changing the direction of the theta test in the HAVING clause will
allow you to pick the right statistical median if a central element
does not exist in the set. You will also notice something else about
the median of a set of unique values: It is usually meaningless. What
does the median bin number mean, anyway? A good rule of thumb is that
if it does not make sense as an average, it does not make sense as a
median.
The statistical median of a column with duplicate values can be found
with a query based on the same ideas, but you have to adjust the HAVING
clause to allow for overlap; thus, the left statistical median is found
by
SELECT P1.weight
FROM Parts AS P1, Parts AS P2
GROUP BY P1.weight
HAVING SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1);
Notice that here the left and right medians can be the same, so there
is no need to pick one over the other in many of the situations where
you have an even number of items. Switching the comparison operators in
the two CASE expressions will give you the right statistical median.
The author's query for the financial median depends on some Sybase
features that cannot be found in other products, so I would recommend
using a combination of the right and left statistical medians to return
a set of values about the center of the data, and then averaging them,
thus:
SELECT AVG(P1.weight)
FROM Parts AS P1, Parts AS P2
HAVING (SUM(CASE WHEN P2.weight <= P1.weight -- left median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1))
OR (SUM(CASE WHEN P2.weight >= P1.weight -- right median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1));
An optimizer may be able to reduce this expression internally, since
the expressions involved with COUNT(*) are constants. This entire query
could be put into a FROM clause and the average taken of the one or two
rows in the result to find the financial median. In SQL-89, you would
have to define this as a VIEW and then take the average.
If you have SQL-2005, you can try something like (untested):
SELECT AVG(x),
ROW_NUMBER () OVER (ORDER BY x ASC) AS hi,
ROW_NUMBER () OVER (ORDER BY x DESC) AS lo,
FROM Foobar
WHERE hi IN (lo, lo-1, lo+1);|||i don't do homework for college students.

median query without using function

I have a table (cars) with 3 fields:

VIN, Class, sell_price
101, sports, 10000
102, sports, 11000
103, luxury, 9000
104, sports, 11000
105, sports, 11000
106, luxury, 5000
107, sports, 11000
108, sports, 11000
109, luxury, 9000

i need to write a query that WITHOUT USING A FUNCTION will return the
median selling price for each class of car. result should look like:

Class, Med_Price
luxury, 9000
sports, 11000

thanks to all u SQLers>From your sample data it looks like you want the most commonly
occurring price for each vehicle (I thought this was
called the "mode", not the "median").

CREATE TABLE Cars(VIN INT,Class VARCHAR(10),sell_price INT)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(101, 'sports', 10000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(102, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(103, 'luxury', 9000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(104, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(105, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(106, 'luxury', 5000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(107, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(108, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(109, 'luxury', 9000)
GO
CREATE VIEW CarNums
AS
SELECT Class,sell_price,COUNT(*) as Num
FROM Cars
GROUP BY Class,sell_price

GO

SELECT c1.Class,c1.sell_price AS Med_Price
FROM CarNums c1
INNER JOIN (
SELECT Class,MAX(Num)
FROM CarNums
GROUP BY Class) C2(Class,Num) ON C2.Class=C1.Class
AND C2.Num=C1.Num|||My mistake, you can get the medians using this

CREATE VIEW CarRank
AS
SELECT c1.VIN,
c1.Class,
c1.sell_price,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class
AND ((c2.sell_price<c1.sell_price)
OR (c2.sell_price=c1.sell_price AND c2.VIN<=c1.VIN))) as
Rank,
(SELECT COUNT(*) FROM Cars c2
WHERE c2.Class=c1.Class) as MaxRank
FROM Cars c1

GO

SELECT Class,AVG(sell_price) AS Med_Price
FROM CarRank
WHERE Rank IN ((MaxRank+1)/2,(MaxRank/2)+1)
GROUP BY Class|||I have a whoel chapter on verious ways to do this in SQL FOR SMARTIES.
Here is one answer.

Median with Characteristic Function

Anatoly Abramovich, Yelena Alexandrova, and Eugene Birger presented a
series of articles in SQL Forum magazine on computing the median (SQL
Forum 1993, 1994). They define a characteristic function, which they
call delta, using the Sybase sign() function. The delta or
characteristic function accepts a Boolean expression as an argument and
returns a 1 if it is TRUE and a zero if it is FALSE or UNKNOWN.

In SQL-92 we have a CASE expression, which can be used to construct the
delta function. This is new to SQL-92, but you can find vendor
functions of the form IF...THEN...ELSE that behave like the condition
expression in Algol or like the question markPcolon operator in C.

The authors also distinguish between the statistical median, whose
value must be a member of the set, and the financial median, whose
value is the average of the middle two members of the set. A
statistical median exists when there is an odd number of items in the
set. If there is an even number of items, you must decide if you want
to use the highest value in the lower half (they call this the left
median) or the lowest value in the upper half (they call this the right
median).

The left statistical median of a unique column can be found with this
query:

SELECT P1.bin
FROM Parts AS P1, Parts AS P2
GROUP BY P1.bin
HAVING SUM(CASE WHEN (P2.bin <= P1.bin) THEN 1 ELSE 0 END)
= (COUNT(*) + 1) / 2;

Changing the direction of the theta test in the HAVING clause will
allow you to pick the right statistical median if a central element
does not exist in the set. You will also notice something else about
the median of a set of unique values: It is usually meaningless. What
does the median bin number mean, anyway? A good rule of thumb is that
if it does not make sense as an average, it does not make sense as a
median.

The statistical median of a column with duplicate values can be found
with a query based on the same ideas, but you have to adjust the HAVING
clause to allow for overlap; thus, the left statistical median is found
by

SELECT P1.weight
FROM Parts AS P1, Parts AS P2
GROUP BY P1.weight
HAVING SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1);

Notice that here the left and right medians can be the same, so there
is no need to pick one over the other in many of the situations where
you have an even number of items. Switching the comparison operators in
the two CASE expressions will give you the right statistical median.

The author's query for the financial median depends on some Sybase
features that cannot be found in other products, so I would recommend
using a combination of the right and left statistical medians to return
a set of values about the center of the data, and then averaging them,
thus:

SELECT AVG(P1.weight)
FROM Parts AS P1, Parts AS P2
HAVING (SUM(CASE WHEN P2.weight <= P1.weight -- left median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight >= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1))
OR (SUM(CASE WHEN P2.weight >= P1.weight -- right median
THEN 1 ELSE 0 END)
>= ((COUNT(*) + 1) / 2)
AND SUM(CASE WHEN P2.weight <= P1.weight
THEN 1 ELSE 0 END)
>= (COUNT(*)/2 + 1));

An optimizer may be able to reduce this expression internally, since
the expressions involved with COUNT(*) are constants. This entire query
could be put into a FROM clause and the average taken of the one or two
rows in the result to find the financial median. In SQL-89, you would
have to define this as a VIEW and then take the average.

If you have SQL-2005, you can try something like (untested):

SELECT AVG(x),
ROW_NUMBER () OVER (ORDER BY x ASC) AS hi,
ROW_NUMBER () OVER (ORDER BY x DESC) AS lo,
FROM Foobar
WHERE hi IN (lo, lo-1, lo+1);|||i don't do homework for college students.

Median query with Group by clause

Hi,

I have a view in which I have 3 cols...(pno,ptno,diff)..diff is the
difference in time in minutes.I want to calculate Median(diff) group
by pno,ptno...using a sql query for SQL server...

Any help is greatly appreciated..

Thanks

AJ"AJ" <aj70000@.hotmail.com> wrote in message
news:6097f505.0404211014.79b1e842@.posting.google.c om...
> Hi,
> I have a view in which I have 3 cols...(pno,ptno,diff)..diff is the
> difference in time in minutes.I want to calculate Median(diff) group
> by pno,ptno...using a sql query for SQL server...
> Any help is greatly appreciated..
> Thanks
> AJ

http://groups.google.com/groups?hl=...80%40ietsmet.nl

Simon

measurement conversion

I am looking for a query that will convert inches into feet:
Here is what I have:
SELECT Orderid, Bundlename, PanelID, Linenumber, Height, Length,
Panelstatus, CompletedDT FROM panel
WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
and panelstatus='C'
order by completeddt
The height and Lenght are returned as:
Height Length
93.0 25.375
I want those values returned as feet. I'm seeing that I need to apply *12 to
a delcare statment, but not sure how to format the query.
I do not need to display the inches, just round the total footage up.
any help is appreciated.
Hi,UseROUND ( numeric_expression , length [ , function ] )
Where the numeric_expression is the number you want to round, length is the
number of characters to leave while rounding to the right or left of the
decimal point. If the length is positive, then numbers are rounded to the
right of the decimal point, where as when the length is negative then
numbers are rounded to the left of the decimal point. The function parameter
is optional. This parameter determines whether the ROUND function will round
or truncate the numeric_expression. If the function parameter is a zero (0),
the default, then rounding will occur; any other value for this parameter
will cause the ROUND function to truncate the numeric_expression.
SELECT Orderid, Bundlename, PanelID, Linenumber, Round(Height,0),
Round(Length,0) .....
Igor B
"Steve T." <stornari@.nvrinc.com> wrote in message
news:OK0ArxuiEHA.3172@.tk2msftngp13.phx.gbl...
>I am looking for a query that will convert inches into feet:
> Here is what I have:
> SELECT Orderid, Bundlename, PanelID, Linenumber, Height, Length,
> Panelstatus, CompletedDT FROM panel
> WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
> and panelstatus='C'
> order by completeddt
> The height and Lenght are returned as:
> Height Length
> 93.0 25.375
> I want those values returned as feet. I'm seeing that I need to apply *12
> to
> a delcare statment, but not sure how to format the query.
> I do not need to display the inches, just round the total footage up.
> any help is appreciated.
>

Measurement conversion

I am looking for a query that will convert inches into feet:
Here is what I have:
SELECT Orderid, Bundlename, PanelID, Linenumber, Height, Length,
Panelstatus, CompletedDT FROM panel
WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
and panelstatus='C'
order by completeddt
The height and Lenght are returned as:
Height Length
93.0 25.375
I want those values returned as feet. I'm seeing that I need to apply *12 to
a delcare statment, but not sure how to format the query.
I do not need to display the inches, just round the total footage up.
any help is appreciated."Steve T." <stornari@.nvrinc.com> wrote in message
news:OhQVqwuiEHA.3896@.TK2MSFTNGP15.phx.gbl...
> I am looking for a query that will convert inches into feet:
> Here is what I have:
> SELECT Orderid, Bundlename, PanelID, Linenumber, Height, Length,
> Panelstatus, CompletedDT FROM panel
> WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
> and panelstatus='C'
> order by completeddt
> The height and Lenght are returned as:
> Height Length
> 93.0 25.375
> I want those values returned as feet. I'm seeing that I need to apply *12
to
> a delcare statment, but not sure how to format the query.
> I do not need to display the inches, just round the total footage up.
> any help is appreciated.
>|||Oops, hit send too quickly last time...
This will round up the calculation if the precision of the number of feet is
greater than 0.0009; else, it will round down... If you want to make it
round up more often, just change the 0.499:
SELECT Orderid, Bundlename, PanelID, Linenumber,
ROUND((Height/12.0) + 0.499, 0) AS Height,
ROUND((Length/12.0) + 0.499, 0) AS Length,
Panelstatus, CompletedDT FROM panel
WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
and panelstatus='C'
order by completeddt
"Steve T." <stornari@.nvrinc.com> wrote in message
news:OhQVqwuiEHA.3896@.TK2MSFTNGP15.phx.gbl...
> I am looking for a query that will convert inches into feet:
> Here is what I have:
> SELECT Orderid, Bundlename, PanelID, Linenumber, Height, Length,
> Panelstatus, CompletedDT FROM panel
> WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
> and panelstatus='C'
> order by completeddt
> The height and Lenght are returned as:
> Height Length
> 93.0 25.375
> I want those values returned as feet. I'm seeing that I need to apply *12
to
> a delcare statment, but not sure how to format the query.
> I do not need to display the inches, just round the total footage up.
> any help is appreciated.
>|||Adam,
Thank you very much.
This will be very helpful.
Thanks again,
Steve
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OoFiE2uiEHA.3476@.tk2msftngp13.phx.gbl...
> Oops, hit send too quickly last time...
> This will round up the calculation if the precision of the number of feet
is
> greater than 0.0009; else, it will round down... If you want to make it
> round up more often, just change the 0.499:
>
> SELECT Orderid, Bundlename, PanelID, Linenumber,
> ROUND((Height/12.0) + 0.499, 0) AS Height,
> ROUND((Length/12.0) + 0.499, 0) AS Length,
> Panelstatus, CompletedDT FROM panel
> WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
> and panelstatus='C'
> order by completeddt
> "Steve T." <stornari@.nvrinc.com> wrote in message
> news:OhQVqwuiEHA.3896@.TK2MSFTNGP15.phx.gbl...
*12[vbcol=seagreen]
> to
>

Measurement conversion

I am looking for a query that will convert inches into feet:
Here is what I have:
SELECT Orderid, Bundlename, PanelID, Linenumber, Height, Length,
Panelstatus, CompletedDT FROM panel
WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
and panelstatus='C'
order by completeddt
The height and Lenght are returned as:
Height Length
93.0 25.375
I want those values returned as feet. I'm seeing that I need to apply *12 to
a delcare statment, but not sure how to format the query.
I do not need to display the inches, just round the total footage up.
any help is appreciated.
"Steve T." <stornari@.nvrinc.com> wrote in message
news:OhQVqwuiEHA.3896@.TK2MSFTNGP15.phx.gbl...
> I am looking for a query that will convert inches into feet:
> Here is what I have:
> SELECT Orderid, Bundlename, PanelID, Linenumber, Height, Length,
> Panelstatus, CompletedDT FROM panel
> WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
> and panelstatus='C'
> order by completeddt
> The height and Lenght are returned as:
> Height Length
> 93.0 25.375
> I want those values returned as feet. I'm seeing that I need to apply *12
to
> a delcare statment, but not sure how to format the query.
> I do not need to display the inches, just round the total footage up.
> any help is appreciated.
>
|||Oops, hit send too quickly last time...
This will round up the calculation if the precision of the number of feet is
greater than 0.0009; else, it will round down... If you want to make it
round up more often, just change the 0.499:
SELECT Orderid, Bundlename, PanelID, Linenumber,
ROUND((Height/12.0) + 0.499, 0) AS Height,
ROUND((Length/12.0) + 0.499, 0) AS Length,
Panelstatus, CompletedDT FROM panel
WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
and panelstatus='C'
order by completeddt
"Steve T." <stornari@.nvrinc.com> wrote in message
news:OhQVqwuiEHA.3896@.TK2MSFTNGP15.phx.gbl...
> I am looking for a query that will convert inches into feet:
> Here is what I have:
> SELECT Orderid, Bundlename, PanelID, Linenumber, Height, Length,
> Panelstatus, CompletedDT FROM panel
> WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
> and panelstatus='C'
> order by completeddt
> The height and Lenght are returned as:
> Height Length
> 93.0 25.375
> I want those values returned as feet. I'm seeing that I need to apply *12
to
> a delcare statment, but not sure how to format the query.
> I do not need to display the inches, just round the total footage up.
> any help is appreciated.
>
|||Adam,
Thank you very much.
This will be very helpful.
Thanks again,
Steve
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OoFiE2uiEHA.3476@.tk2msftngp13.phx.gbl...
> Oops, hit send too quickly last time...
> This will round up the calculation if the precision of the number of feet
is[vbcol=seagreen]
> greater than 0.0009; else, it will round down... If you want to make it
> round up more often, just change the 0.499:
>
> SELECT Orderid, Bundlename, PanelID, Linenumber,
> ROUND((Height/12.0) + 0.499, 0) AS Height,
> ROUND((Length/12.0) + 0.499, 0) AS Length,
> Panelstatus, CompletedDT FROM panel
> WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
> and panelstatus='C'
> order by completeddt
> "Steve T." <stornari@.nvrinc.com> wrote in message
> news:OhQVqwuiEHA.3896@.TK2MSFTNGP15.phx.gbl...
*12
> to
>

Measurement conversion

I am looking for a query that will convert inches into feet:
Here is what I have:
SELECT Orderid, Bundlename, PanelID, Linenumber, Height, Length,
Panelstatus, CompletedDT FROM panel
WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
and panelstatus='C'
order by completeddt
The height and Lenght are returned as:
Height Length
93.0 25.375
I want those values returned as feet. I'm seeing that I need to apply *12 to
a delcare statment, but not sure how to format the query.
I do not need to display the inches, just round the total footage up.
any help is appreciated."Steve T." <stornari@.nvrinc.com> wrote in message
news:OhQVqwuiEHA.3896@.TK2MSFTNGP15.phx.gbl...
> I am looking for a query that will convert inches into feet:
> Here is what I have:
> SELECT Orderid, Bundlename, PanelID, Linenumber, Height, Length,
> Panelstatus, CompletedDT FROM panel
> WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
> and panelstatus='C'
> order by completeddt
> The height and Lenght are returned as:
> Height Length
> 93.0 25.375
> I want those values returned as feet. I'm seeing that I need to apply *12
to
> a delcare statment, but not sure how to format the query.
> I do not need to display the inches, just round the total footage up.
> any help is appreciated.
>|||Oops, hit send too quickly last time...
This will round up the calculation if the precision of the number of feet is
greater than 0.0009; else, it will round down... If you want to make it
round up more often, just change the 0.499:
SELECT Orderid, Bundlename, PanelID, Linenumber,
ROUND((Height/12.0) + 0.499, 0) AS Height,
ROUND((Length/12.0) + 0.499, 0) AS Length,
Panelstatus, CompletedDT FROM panel
WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
and panelstatus='C'
order by completeddt
"Steve T." <stornari@.nvrinc.com> wrote in message
news:OhQVqwuiEHA.3896@.TK2MSFTNGP15.phx.gbl...
> I am looking for a query that will convert inches into feet:
> Here is what I have:
> SELECT Orderid, Bundlename, PanelID, Linenumber, Height, Length,
> Panelstatus, CompletedDT FROM panel
> WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
> and panelstatus='C'
> order by completeddt
> The height and Lenght are returned as:
> Height Length
> 93.0 25.375
> I want those values returned as feet. I'm seeing that I need to apply *12
to
> a delcare statment, but not sure how to format the query.
> I do not need to display the inches, just round the total footage up.
> any help is appreciated.
>|||Adam,
Thank you very much.
This will be very helpful.
Thanks again,
Steve
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OoFiE2uiEHA.3476@.tk2msftngp13.phx.gbl...
> Oops, hit send too quickly last time...
> This will round up the calculation if the precision of the number of feet
is
> greater than 0.0009; else, it will round down... If you want to make it
> round up more often, just change the 0.499:
>
> SELECT Orderid, Bundlename, PanelID, Linenumber,
> ROUND((Height/12.0) + 0.499, 0) AS Height,
> ROUND((Length/12.0) + 0.499, 0) AS Length,
> Panelstatus, CompletedDT FROM panel
> WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
> and panelstatus='C'
> order by completeddt
> "Steve T." <stornari@.nvrinc.com> wrote in message
> news:OhQVqwuiEHA.3896@.TK2MSFTNGP15.phx.gbl...
> > I am looking for a query that will convert inches into feet:
> > Here is what I have:
> >
> > SELECT Orderid, Bundlename, PanelID, Linenumber, Height, Length,
> > Panelstatus, CompletedDT FROM panel
> > WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00'
> > and panelstatus='C'
> > order by completeddt
> >
> > The height and Lenght are returned as:
> > Height Length
> > 93.0 25.375
> >
> > I want those values returned as feet. I'm seeing that I need to apply
*12
> to
> > a delcare statment, but not sure how to format the query.
> > I do not need to display the inches, just round the total footage up.
> >
> > any help is appreciated.
> >
> >
>

Measure Expression in Query ?

Hi - I want to do a query like this, in management studio, but it is giving me an error message.

select sum({TopCount([Asset].[Asset].[Asset].members,5,([Portfolio].[Portfolio].&[DD2342342],[Measures].[Value Base]))}, [Measures].[Portfolio Weighting]) on columns
from [MIQB Daily]

I tried different strto.. functions but didn't find any proper solution.

The Axis0 function expects a tuple set expression for the argument. A string or numeric expression was used.

Thanks

Noordin

Hi Noordin,

Unfortunately you can't just insert an MDX expression that returns a value on an axis - you have to declare a calculated member first, use the expression as its definition, and then place the calculated member on the axis. Here's an example from Adventure Works:

with member measures.demo as

sum(topcount([Date].[Date].[Date].members, 5, [Measures].[Internet Sales Amount]), [Measures].[Internet Sales Amount])

select {measures.demo, [Measures].[Internet Sales Amount]} on 0,

[Product].[Category].members on 1

from

[Adventure Works]

HTH,

Chris

|||

Thanks Chris,

Actually I was playing with Excel 2007 cube... formulas ... may be I need to do some work around.

do you have an idea of #IND value, what does it mean, is it infinity?

Regards

Noordin

|||I'm afraid I don't have an install of Office 2007 handy at the moment, so I'm not sure. Anyone else? #IND isn't what AS will return for infinity though - it returns 1.#INF.

means of tracing query plans

Hi,
aside from maybe using profiler, is there another means of displaying the qu
ery plans of procs being executed on the server over a period of time. ie. i
f a proc is being recompiled during execution is there a way for me to see t
he query plan of that proc
for each execution?
thanks.>> another means of displaying the query plans of procs being executed on
the server over a period of time
You might have third party software solutions. But I am not sure of anything
except profiler and the query analyzer window to display the execution plan
details. Moreover the profiler is the best way to go about such auditing
purposes. And why do you want to use other methods ?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"simax" <anonymous@.discussions.microsoft.com> wrote in message
news:661F00F1-EFA3-42F1-B271-9D4A757417CB@.microsoft.com...
> Hi,
> aside from maybe using profiler, is there another means of displaying the
query plans of procs being executed on the server over a period of time. ie.
if a proc is being recompiled during execution is there a way for me to see
the query plan of that proc for each execution?
> thanks.|||Profiler is the only way.... And if you are looking for recompiles, of
course you do not have to capture the execution plans, just the
recompiles... SP3 introduced a new counter in Profiler which is included
with recompiles to explain why the recompile occurred... ( it is doc'd in
the service pack documentation.)
"simax" <anonymous@.discussions.microsoft.com> wrote in message
news:661F00F1-EFA3-42F1-B271-9D4A757417CB@.microsoft.com...
> Hi,
> aside from maybe using profiler, is there another means of displaying the
query plans of procs being executed on the server over a period of time. ie.
if a proc is being recompiled during execution is there a way for me to see
the query plan of that proc for each execution?
> thanks.

means of tracing query plans

Hi
aside from maybe using profiler, is there another means of displaying the query plans of procs being executed on the server over a period of time. ie. if a proc is being recompiled during execution is there a way for me to see the query plan of that proc for each execution
thanks.>> another means of displaying the query plans of procs being executed on
the server over a period of time
You might have third party software solutions. But I am not sure of anything
except profiler and the query analyzer window to display the execution plan
details. Moreover the profiler is the best way to go about such auditing
purposes. And why do you want to use other methods ?
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"simax" <anonymous@.discussions.microsoft.com> wrote in message
news:661F00F1-EFA3-42F1-B271-9D4A757417CB@.microsoft.com...
> Hi,
> aside from maybe using profiler, is there another means of displaying the
query plans of procs being executed on the server over a period of time. ie.
if a proc is being recompiled during execution is there a way for me to see
the query plan of that proc for each execution?
> thanks.|||Profiler is the only way.... And if you are looking for recompiles, of
course you do not have to capture the execution plans, just the
recompiles... SP3 introduced a new counter in Profiler which is included
with recompiles to explain why the recompile occurred... ( it is doc'd in
the service pack documentation.)
"simax" <anonymous@.discussions.microsoft.com> wrote in message
news:661F00F1-EFA3-42F1-B271-9D4A757417CB@.microsoft.com...
> Hi,
> aside from maybe using profiler, is there another means of displaying the
query plans of procs being executed on the server over a period of time. ie.
if a proc is being recompiled during execution is there a way for me to see
the query plan of that proc for each execution?
> thanks.

means of tracing query plans

Hi,
aside from maybe using profiler, is there another means of displaying the query plans of procs being executed on the server over a period of time. ie. if a proc is being recompiled during execution is there a way for me to see the query plan of that proc
for each execution?
thanks.
>> another means of displaying the query plans of procs being executed on
the server over a period of time
You might have third party software solutions. But I am not sure of anything
except profiler and the query analyzer window to display the execution plan
details. Moreover the profiler is the best way to go about such auditing
purposes. And why do you want to use other methods ?
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"simax" <anonymous@.discussions.microsoft.com> wrote in message
news:661F00F1-EFA3-42F1-B271-9D4A757417CB@.microsoft.com...
> Hi,
> aside from maybe using profiler, is there another means of displaying the
query plans of procs being executed on the server over a period of time. ie.
if a proc is being recompiled during execution is there a way for me to see
the query plan of that proc for each execution?
> thanks.
|||Profiler is the only way.... And if you are looking for recompiles, of
course you do not have to capture the execution plans, just the
recompiles... SP3 introduced a new counter in Profiler which is included
with recompiles to explain why the recompile occurred... ( it is doc'd in
the service pack documentation.)
"simax" <anonymous@.discussions.microsoft.com> wrote in message
news:661F00F1-EFA3-42F1-B271-9D4A757417CB@.microsoft.com...
> Hi,
> aside from maybe using profiler, is there another means of displaying the
query plans of procs being executed on the server over a period of time. ie.
if a proc is being recompiled during execution is there a way for me to see
the query plan of that proc for each execution?
> thanks.

MDX's ORDER Function

Hi

I'm trying to order rows returned by a MDX query using ORDER function. It seems that I don't use it properly. Here is the query from the sample AS Database, AdventureWork.

WITH MEMBER [Measures].DIFF AS
'([Customer].[Country].CURRENTMEMBER, [Measures].[Internet Sales Amount])
-
([Customer].[Country].&[Australia], [Measures].[Internet Sales Amount])'
SELECT [Measures].DIFF ON COLUMNS,
ORDER([Customer].[Country].MEMBERS, [Measures].DIFF, BDESC) ON ROWS
FROM [Adventure Works]

what it returns is:

All Customers $20,297,676.64
United States $328,788.93
Australia $0.00
United Kingdom ($5,669,288.37)
Germany ($6,166,688.25)
France ($6,416,982.87)
Canada ($7,083,155.72)

I expected to see "Australia" at the end of the list and "All Customers" on top of it.
Any Idea?

Thanks in advance,
AmirThe results look correct to me. Why do you expect Australia at the end ? It's DIFF is 0 which is bigger then negative diffs of other countries.|||Sorry about that.
I didn't realize there is a bracket arround negative numbers.

Thanks

MDX:time between fatcs / time spent in a state

Hi all

I am an MDX newbie and it happens i have to do immediately with a query that seems (to me) pretty unorthodox.

The fact table i have is made up of the following entities: StateMachineId, StateId, TransitionDateTime.
In fact, i have several State Machines that can find themselves in different states and i log in a DB the time of their transitions and the new state.
I need to generate reports mostly based on the time spent in each state and i have no clue on what is the best way to do that in mdx.
In sql, what i did was to join this FactTable (FT1) with itself (FT2) ON StateMachineId adding the condition that FT2.TransitionDateTime BETWEEN FT1.TransitionDateTime AND FT1.TransitionDateTime + SomeTimeInterval , in this way i narroewed the size of the join. At this point, i took the row from FT2 with the lesser TransitionDateTime .
All this works perfectly, but it is akward and pretty slow. Another downside is that some longer transitions (those longer than SomeTimeInterval) are excluded.
I think working with MDX could be better but, as i stated before, i am new in this field.
Do you have some ideas to create a suitable cube for these queries?

Thank You So Much

Best regards

Wentu
It's me again

I found this SQL solution:

Code Snippet

with modnums as (
select *, row_number() over (order by StateMachineId, TransitionDateTime) as rn
from FactTable
)
select m_this.StateMachineId, m_this.TransitionDateTime, m_next.StateId, m_next.TransitionDateTime, m_this.StateId, datediff(ms,m_this.TransitionDateTime, m_next.TransitionDateTime) elapsedTime
from
modnums m_this
inner join
modnums m_next
on m_next.rn = m_this.rn + 1
and m_this.StateMachineId = m_next.StateMachineId

It's working very well (Thankx to Rob Farley for this) .
Now, i would like to use it in Analysis Services but the "OVER" is not allowed in Named Queries. Any Idea on how to write this query in MDX ?

Thankx again

Wentu

MDX: Null value to replace column in Query

I have an MDX query that is not doing what I want it to do.

Currently, I have an application that expects to recieve a certain
number of columns in order to make a chart.In SQL, if I was not
requesting data for all of the columns I could replace the column name
with a NULL and still recieve the other information with the in the
appropriate format (Except for that column would have all NULL values).
I cannot get this to work in MDX... an example in SQL which does work
follows:

i.e.
Requesting real information
SELECT id as col1, dog as col2 FROM SQL

Replace column with null but recieve same table format
SELECT NULL as col1, dog as col2 FROM SQL

I cannot figure out how to do this when requesting data with MDX in
Analysis Services. I have looked all through "MDX Solutions" and cannot
find the solution :) Although there was tons of great stuff in there.
TONS!

My MDX query that requests real data for all columns and works fine is
as follows:
SELECT {
[Measures].[YN] ,
[Measures].[YD] ,
[Measures].[YV] ,
} ON 0,

NONEMPTY(
{[Product].[p Hier Ty3 Bg 1 1].&[R104],[Product].[p Hier Ty3 Bg 1
1].&[R706]}
*{[Customer].[c Hier Ty3 Bg 1 3].&[Australia]}
*EXCEPT([Date 1].[c_month].[2004_M01]:[Date 1].[c_month].[2004_M03],
[Date 1].[c_month].[ALL])
*EXCEPT([Customer].[c Hier Ty3 Bg 2 2].Members,[Customer].[c Hier Ty3
Bg 2 2].[All])

) ON 1
FROM Mimir04

But if I want an empty column to replace {[Customer].[c Hier Ty3 Bg 1
3].&[Australia]}, my guess at a solution (coming from SQL and being a
newbie at MDX) was to put a null set in place of this dimension call...
{NULL}. Unfortunately, this means that nothing gets returned

What exactly is the solution to returning an empty column?

Could you please clarify whether you'd like empty rows or empty columns?

Or, best, show an example of how you expect the query result to look like (axes contents and cell data)?

Thank you

MDX with parameters using OleDb?

I'm trying to run an MDX query with a parameter. It works fine with ADOMD.NET, but I can't get it to work using OleDb. (I have to use OleDb because of limitations of the calling application.) Is this possible?

The following code works:

//using Microsoft.AnalysisServices.AdomdClient;

string MDX = "with member [Measures].[Test] as Str(@.Param1) "

+ "SELECT [Measures].[Test] on 0, "

+ "[Product].[Category].[Category].Members on 1 "

+ "from [Adventure Works]";

AdomdConnection conn = new AdomdConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");

conn.Open();

AdomdCommand cmd = new AdomdCommand(MDX, conn);

cmd.Parameters.Add("Param1", "abcde");

System.Data.DataSet ds = new System.Data.DataSet();

AdomdDataAdapter adp = new AdomdDataAdapter(cmd);

adp.Fill(ds);

Console.WriteLine(ds.Tables[0].Rows[0][1]);

conn.Close();

The following code fails:

//using System.Data.OleDb;

string MDX = "with member [Measures].[Test] as Str(@.Param1) "

+ "SELECT [Measures].[Test] on 0, "

+ "[Product].[Category].[Category].Members on 1 "

+ "from [Adventure Works]";

OleDbConnection conn = new OleDbConnection("Provider=MSOLAP.3;Data Source=localhost;Initial Catalog=Adventure Works DW;Integrated Security=SSPI;Persist Security Info=false;");

conn.Open();

OleDbCommand cmd = new OleDbCommand(MDX, conn);

cmd.Parameters.AddWithValue("Param1", "abcde");

System.Data.DataSet ds = new System.Data.DataSet();

OleDbDataAdapter adp = new OleDbDataAdapter(cmd);

adp.Fill(ds); //produces error: "The following system error occurred:The parameter is incorrect. . Error Code = 0x80070057, External Code = 0x00000000:."

Console.WriteLine(ds.Tables[0].Rows[0][1]);

conn.Close();

Try to add @. in front of the parameter name. It seems that ADOMD.NET and OledbClient are not constent in this regard.

cmd.Parameters.AddWithValue("@.Param1", "abcde");

|||

Irina-

I still get the same error even after adding the @. sign. If that code works for you, what version are you running on? Is this maybe an SP2 fix?

|||

Actually I'm getting the same error. Debugging OLEDB provider shows that OledbClient passes it

DBPARAMBINDINFO with NULL parameter name. So it seems like a bug in OledbClient. Currently I don't have access to OledbClient sourses. I'll try to find sorces tomorrow, but can't promise, so may be you should ask the same question on OledbClient forum.

|||

I cross-posted in the forum (I think) you were talking about:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1088407&SiteID=1&mode=1

|||

I haven't heard anything from the other forum, so I'm afraid I'm never going to get an answer on this. Irina, have you had a chance to look at the source? Or should I post this to connect.microsoft.com?

I also ran across the following Books Online page which mentions that you're required to use the ICommandWithParameters interface when doing parameterized MDX with OleDb. I'm not optimistic that will produce any different results, and I haven't really found a good code sample that shows how to implement that interface and use it.
http://msdn2.microsoft.com/en-us/library/ms145518.aspx

|||

reported to connect:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=251601