Hi!
Does anyone know how to compute a 'mode' average instead of the 'mean'
average that the aggregate function 'AVG' returns? Is there another
function, or some kind of parameter for the AVG function?
Thanks,
CraigThere are no othe average functions, but can you explain in more detail what
you want... The mode of a list of values is the value which occurs most
frequently... and I don't understand "mode average".
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CraigyBoop" <CraigyBoop@.discussions.microsoft.com> wrote in message
news:9896A30B-F1B3-44BB-8BDA-7CAB98EE3487@.microsoft.com...
> Hi!
> Does anyone know how to compute a 'mode' average instead of the 'mean'
> average that the aggregate function 'AVG' returns? Is there another
> function, or some kind of parameter for the AVG function?
> Thanks,
> Craig|||Wayne,
Thanks for replying!
From what I can make out, the AVG function returns the 'mean' average (that
is, add up all the values in a set of numbers, then divide the total by the
'number' of numbers in the set). I want to return the 'modal' average.
Your definition of 'mode' is precisely what I'm looking for, so I was hoping
there was a function that would return the average in this manner.
Any suggestions?
"Wayne Snyder" wrote:
> There are no othe average functions, but can you explain in more detail what
> you want... The mode of a list of values is the value which occurs most
> frequently... and I don't understand "mode average".
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "CraigyBoop" <CraigyBoop@.discussions.microsoft.com> wrote in message
> news:9896A30B-F1B3-44BB-8BDA-7CAB98EE3487@.microsoft.com...
> > Hi!
> >
> > Does anyone know how to compute a 'mode' average instead of the 'mean'
> > average that the aggregate function 'AVG' returns? Is there another
> > function, or some kind of parameter for the AVG function?
> >
> > Thanks,
> >
> > Craig
>
>
Showing posts with label function. Show all posts
Showing posts with label function. Show all posts
Monday, March 26, 2012
Mod function in SQL 2000
I need to use the mod function on a numeric field with 2digits decimal.
I was told that SQL server does not support mod on a decimal number.
Is it correct? If yes, is there any way around this?
Thanks in advanceWhat does MOD do?|||Modulus, right?|||Originally posted by Brett Kaiser
What does MOD do?
I meant MODULO. (Provides the remainder of one number divided by another.)
Thanks|||modulo isn't designed to work with decimals regardless if it's SQL or any other language.
You can must CAST or CONVERT the decimal to INT.
regards,
hmscott|||You can multiply both numbers by 100 and cast them as INT, like this:
select cast(4.32 * 100 as int) & cast(5.87 * 100 as int)|||Or, you can have this function:
if object_id('dbo.fn_Modulo42Decimals') is not null
drop function dbo.fn_Modulo42Decimals
go
create function dbo.fn_Modulo42Decimals (
@.First decimal(18, 2),
@.Second decimal(18, 2) ) returns int
as begin
return (
cast(@.First * 100 as int) & cast(@.Second * 100 as int)
)
end
go
select dbo.fn_Modulo42Decimals(5995.32, 154.67)
Not sure if it actually works right, always hated math :D|||How about
DECLARE @.x decimal(15,2), @.y decimal(15,2)
SELECT @.x = 12345.67, @.y = 2.15
SELECT FLOOR(@.x/@.y)-@.x/@.y|||But this is my favorite (courtesy of Kaiser's Bar & Grill - franchise inquiries are welcome):
DECLARE @.Weekend datetime
SELECT @.Weekend =
CONVERT(datetime,
CONVERT(varchar(10),GetDate(),120) + ' 17:00:00')
SELECT
DATEDIFF(mi, GetDate(), @.Weekend)/60.00 As Hours_till_Margarittaville|||and how about when I have a value of 48 digits long? I declared a float(50),
but when I perform a division it keeps displaying an error message about the maximum precision of a numeric being 38. How can I do this or work around?|||38 digits is the limit for numeric calculations in SQL Server. I don't know of any way around that limit within SQL.
You've piqued my curiouisity though... Why on earth would you care ?!?! What the heck would you store with fifty digits of precision ?
-PatP|||well, it's a barcode consisting of an employee's company number, department, dates, ... It's in total 50 digits long, the first 48 containing data and the last 2 are check digits (modulo 97). I need to check if the data is correctly recognized by our scanning software and if not, present that record to the operator that has to manually correct it.|||Hmmm... The only thing that comes to my mind would be to treat the barcode as two NUMERIC(30,0) columns.
-PatP|||And how would you do that? I don't think I can just cust the value in 2 parts and perform some calculations on it?|||Company number?
Departement number?
These are not really numbers, they are codes. If you don't add it, subtract it, or multiply it, then it is a string, not a number. Store your barcode as a 50 character string.|||they are already stored as varchar, but I have to perform a check on the recognized number, so: (first 48 characters) modulo 97 = (last 2 characters)|||Applause, please...
create Function BigStringModulo(@.BigNumString varchar(500), @.Divisor int)
returns int
as
--Function BigStringModulo
--blindman, 1/18/2005
--Returns the Modulo value of a large number expressed as a string value.
--Does not verify that the string is a valid number!
begin
declare @.WorkingString as varchar(500)
declare @.CalcString as varchar(8)
set @.WorkingString = @.BigNumString
set @.CalcString = left(@.WorkingString, 8)
while @.CalcString > @.Divisor
begin
set @.WorkingString = right(@.WorkingString, len(@.WorkingString) - len(@.CalcString))
set @.WorkingString = cast((@.CalcString % @.Divisor) as varchar) + @.WorkingString
set @.CalcString = left(@.WorkingString, 8)
end
return cast(@.CalcString as int)
end|||This may take some experimentation, but if I remember right
n * m mod p = ((n mod p) * (m mod p)) mod p
and
n + m mod p = ((n mod p) + (m mod p)) mod p
In that case, you can break your number into two pieces
(substing (number, 1, 25) * 10^25 + substring(number 26, 22)) mod 97. Break down the individual sections, and you have an ugly but workable solution.|||I'll applaud when I figure out how it was done, but it looks like it works. Don't you ever think inside the box?
Oh, and my 10^25 should probably be 10^22, or whatever exponent is correct for breaking the number into two halves.|||Found a bug. Have to handle the case where there are multiple consecutive zeros:
drop Function BigStringModulo
go
create Function BigStringModulo(@.BigNumString varchar(500), @.Divisor int)
returns int
as
--Function BigStringModulo
--blindman, 1/18/2005
--Returns the Modulo value of a large number expressed as a string value.
--Does not verify that the string is a valid number!
--TestVariables
-- declare @.BigNumString varchar(500)
-- declare @.Divisor int
-- set @.BigNumString = '97000000000001'
-- set @.Divisor = 2
begin
declare @.WorkingString as varchar(500)
declare @.CalcString as varchar(7)
set @.WorkingString = @.BigNumString
set @.CalcString = left(@.WorkingString, 7)
while (@.CalcString > @.Divisor) or len(@.WorkingString) > len(@.Divisor)
begin
set @.WorkingString = right(@.WorkingString, len(@.WorkingString) - len(@.CalcString))
set @.WorkingString = cast((@.CalcString % @.Divisor) as varchar) + @.WorkingString
set @.CalcString = left(@.WorkingString, 7)
end
--select cast(@.CalcString as int)
return cast(@.CalcString as int)
end|||Thanks for your replies. I will take a look at it see if it works tommorow, as I am far past my working hours, so I'll be off home now :D|||I had to change
"while @.CalcString >= @.divisor"
in case the divisor and working string were the same, but it looks like you found the same bug, and solved it a different way.
I think I understand what you have done, now. Your solution is similar to mine, but you are tagging on the upper string at the beginning of the lower string, and effectively getting the power of ten included that way. Nice job, Blindman.|||I liked the algorithm, but as you found out the code was not heavily tested. It is tough to verify the results when there are no other functions that will duplicate it! Please let me know if you see any more bugs in it.|||Well, it works ... Thanks blindman and MCrowley|||That was a fun challenge! Thanks for posting it.sql
I was told that SQL server does not support mod on a decimal number.
Is it correct? If yes, is there any way around this?
Thanks in advanceWhat does MOD do?|||Modulus, right?|||Originally posted by Brett Kaiser
What does MOD do?
I meant MODULO. (Provides the remainder of one number divided by another.)
Thanks|||modulo isn't designed to work with decimals regardless if it's SQL or any other language.
You can must CAST or CONVERT the decimal to INT.
regards,
hmscott|||You can multiply both numbers by 100 and cast them as INT, like this:
select cast(4.32 * 100 as int) & cast(5.87 * 100 as int)|||Or, you can have this function:
if object_id('dbo.fn_Modulo42Decimals') is not null
drop function dbo.fn_Modulo42Decimals
go
create function dbo.fn_Modulo42Decimals (
@.First decimal(18, 2),
@.Second decimal(18, 2) ) returns int
as begin
return (
cast(@.First * 100 as int) & cast(@.Second * 100 as int)
)
end
go
select dbo.fn_Modulo42Decimals(5995.32, 154.67)
Not sure if it actually works right, always hated math :D|||How about
DECLARE @.x decimal(15,2), @.y decimal(15,2)
SELECT @.x = 12345.67, @.y = 2.15
SELECT FLOOR(@.x/@.y)-@.x/@.y|||But this is my favorite (courtesy of Kaiser's Bar & Grill - franchise inquiries are welcome):
DECLARE @.Weekend datetime
SELECT @.Weekend =
CONVERT(datetime,
CONVERT(varchar(10),GetDate(),120) + ' 17:00:00')
SELECT
DATEDIFF(mi, GetDate(), @.Weekend)/60.00 As Hours_till_Margarittaville|||and how about when I have a value of 48 digits long? I declared a float(50),
but when I perform a division it keeps displaying an error message about the maximum precision of a numeric being 38. How can I do this or work around?|||38 digits is the limit for numeric calculations in SQL Server. I don't know of any way around that limit within SQL.
You've piqued my curiouisity though... Why on earth would you care ?!?! What the heck would you store with fifty digits of precision ?
-PatP|||well, it's a barcode consisting of an employee's company number, department, dates, ... It's in total 50 digits long, the first 48 containing data and the last 2 are check digits (modulo 97). I need to check if the data is correctly recognized by our scanning software and if not, present that record to the operator that has to manually correct it.|||Hmmm... The only thing that comes to my mind would be to treat the barcode as two NUMERIC(30,0) columns.
-PatP|||And how would you do that? I don't think I can just cust the value in 2 parts and perform some calculations on it?|||Company number?
Departement number?
These are not really numbers, they are codes. If you don't add it, subtract it, or multiply it, then it is a string, not a number. Store your barcode as a 50 character string.|||they are already stored as varchar, but I have to perform a check on the recognized number, so: (first 48 characters) modulo 97 = (last 2 characters)|||Applause, please...
create Function BigStringModulo(@.BigNumString varchar(500), @.Divisor int)
returns int
as
--Function BigStringModulo
--blindman, 1/18/2005
--Returns the Modulo value of a large number expressed as a string value.
--Does not verify that the string is a valid number!
begin
declare @.WorkingString as varchar(500)
declare @.CalcString as varchar(8)
set @.WorkingString = @.BigNumString
set @.CalcString = left(@.WorkingString, 8)
while @.CalcString > @.Divisor
begin
set @.WorkingString = right(@.WorkingString, len(@.WorkingString) - len(@.CalcString))
set @.WorkingString = cast((@.CalcString % @.Divisor) as varchar) + @.WorkingString
set @.CalcString = left(@.WorkingString, 8)
end
return cast(@.CalcString as int)
end|||This may take some experimentation, but if I remember right
n * m mod p = ((n mod p) * (m mod p)) mod p
and
n + m mod p = ((n mod p) + (m mod p)) mod p
In that case, you can break your number into two pieces
(substing (number, 1, 25) * 10^25 + substring(number 26, 22)) mod 97. Break down the individual sections, and you have an ugly but workable solution.|||I'll applaud when I figure out how it was done, but it looks like it works. Don't you ever think inside the box?
Oh, and my 10^25 should probably be 10^22, or whatever exponent is correct for breaking the number into two halves.|||Found a bug. Have to handle the case where there are multiple consecutive zeros:
drop Function BigStringModulo
go
create Function BigStringModulo(@.BigNumString varchar(500), @.Divisor int)
returns int
as
--Function BigStringModulo
--blindman, 1/18/2005
--Returns the Modulo value of a large number expressed as a string value.
--Does not verify that the string is a valid number!
--TestVariables
-- declare @.BigNumString varchar(500)
-- declare @.Divisor int
-- set @.BigNumString = '97000000000001'
-- set @.Divisor = 2
begin
declare @.WorkingString as varchar(500)
declare @.CalcString as varchar(7)
set @.WorkingString = @.BigNumString
set @.CalcString = left(@.WorkingString, 7)
while (@.CalcString > @.Divisor) or len(@.WorkingString) > len(@.Divisor)
begin
set @.WorkingString = right(@.WorkingString, len(@.WorkingString) - len(@.CalcString))
set @.WorkingString = cast((@.CalcString % @.Divisor) as varchar) + @.WorkingString
set @.CalcString = left(@.WorkingString, 7)
end
--select cast(@.CalcString as int)
return cast(@.CalcString as int)
end|||Thanks for your replies. I will take a look at it see if it works tommorow, as I am far past my working hours, so I'll be off home now :D|||I had to change
"while @.CalcString >= @.divisor"
in case the divisor and working string were the same, but it looks like you found the same bug, and solved it a different way.
I think I understand what you have done, now. Your solution is similar to mine, but you are tagging on the upper string at the beginning of the lower string, and effectively getting the power of ten included that way. Nice job, Blindman.|||I liked the algorithm, but as you found out the code was not heavily tested. It is tough to verify the results when there are no other functions that will duplicate it! Please let me know if you see any more bugs in it.|||Well, it works ... Thanks blindman and MCrowley|||That was a fun challenge! Thanks for posting it.sql
MOD function
Is there any function like MOD in T-SQL?
5 MOD 2 = 1
4 MOD 2 = 0
8 MOD 3 = 2SELECT 5 % 2
SELECT 4 % 2
SELECT 8 % 3
David Portas
SQL Server MVP
--
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:e5iSEFLhFHA.3568@.TK2MSFTNGP10.phx.gbl...
> Is there any function like MOD in T-SQL?
> 5 MOD 2 = 1
> 4 MOD 2 = 0
> 8 MOD 3 = 2
>
5 MOD 2 = 1
4 MOD 2 = 0
8 MOD 3 = 2SELECT 5 % 2
SELECT 4 % 2
SELECT 8 % 3
David Portas
SQL Server MVP
--
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:e5iSEFLhFHA.3568@.TK2MSFTNGP10.phx.gbl...
> Is there any function like MOD in T-SQL?
> 5 MOD 2 = 1
> 4 MOD 2 = 0
> 8 MOD 3 = 2
>
MOD function
Is there any function like MOD in T-SQL?
5 MOD 2 = 1
4 MOD 2 = 0
8 MOD 3 = 2SELECT 5 % 2
SELECT 4 % 2
SELECT 8 % 3
David Portas
SQL Server MVP
--
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:e5iSEFLhFHA.3568@.TK2MSFTNGP10.phx.gbl...
> Is there any function like MOD in T-SQL?
> 5 MOD 2 = 1
> 4 MOD 2 = 0
> 8 MOD 3 = 2
>
5 MOD 2 = 1
4 MOD 2 = 0
8 MOD 3 = 2SELECT 5 % 2
SELECT 4 % 2
SELECT 8 % 3
David Portas
SQL Server MVP
--
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:e5iSEFLhFHA.3568@.TK2MSFTNGP10.phx.gbl...
> Is there any function like MOD in T-SQL?
> 5 MOD 2 = 1
> 4 MOD 2 = 0
> 8 MOD 3 = 2
>
MOD function
Is there any function like MOD in T-SQL?
5 MOD 2 = 1
4 MOD 2 = 0
8 MOD 3 = 2
SELECT 5 % 2
SELECT 4 % 2
SELECT 8 % 3
David Portas
SQL Server MVP
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:e5iSEFLhFHA.3568@.TK2MSFTNGP10.phx.gbl...
> Is there any function like MOD in T-SQL?
> 5 MOD 2 = 1
> 4 MOD 2 = 0
> 8 MOD 3 = 2
>
5 MOD 2 = 1
4 MOD 2 = 0
8 MOD 3 = 2
SELECT 5 % 2
SELECT 4 % 2
SELECT 8 % 3
David Portas
SQL Server MVP
"David Dvali" <david_dvali@.hotmail.com> wrote in message
news:e5iSEFLhFHA.3568@.TK2MSFTNGP10.phx.gbl...
> Is there any function like MOD in T-SQL?
> 5 MOD 2 = 1
> 4 MOD 2 = 0
> 8 MOD 3 = 2
>
MOD Equivalent in SQl Server?
I'm trying to build a query that I'd like only run on records with an odd
number in a specific field.
Using the "MOD" function, I'd simply throw a criteria in that says where
"Field Mod 2 <> 0"
Is there a mod function in SQL Server 2K? I can't find it... If not,
what's my alternative?
Thanks in advance...
gThe "%" character is the modulo function, so try
Field % 2 <> 0
"Greg Toronto" wrote:
> I'm trying to build a query that I'd like only run on records with an odd
> number in a specific field.
> Using the "MOD" function, I'd simply throw a criteria in that says where
> "Field Mod 2 <> 0"
> Is there a mod function in SQL Server 2K? I can't find it... If not,
> what's my alternative?
> Thanks in advance...
> g|||>> Using the "MOD" function, I'd simply throw a criteria in that says where
"Field Mod 2 <> 0" <<
They stole the infixed % from C; the ANSI/ISO syntax is MOD(). But be
careful and try your MOD to see how you expect negative numbers to
work. This was a big problem in Standardizing Pascal years ago.
number in a specific field.
Using the "MOD" function, I'd simply throw a criteria in that says where
"Field Mod 2 <> 0"
Is there a mod function in SQL Server 2K? I can't find it... If not,
what's my alternative?
Thanks in advance...
gThe "%" character is the modulo function, so try
Field % 2 <> 0
"Greg Toronto" wrote:
> I'm trying to build a query that I'd like only run on records with an odd
> number in a specific field.
> Using the "MOD" function, I'd simply throw a criteria in that says where
> "Field Mod 2 <> 0"
> Is there a mod function in SQL Server 2K? I can't find it... If not,
> what's my alternative?
> Thanks in advance...
> g|||>> Using the "MOD" function, I'd simply throw a criteria in that says where
"Field Mod 2 <> 0" <<
They stole the infixed % from C; the ANSI/ISO syntax is MOD(). But be
careful and try your MOD to see how you expect negative numbers to
work. This was a big problem in Standardizing Pascal years ago.
Monday, February 20, 2012
Missing Format() - Function
Hi folks,
i have to translate SQL-Statements from Access 2000 to SQL
Server 2000. There was a lot of Access-Functions used
(mid, str, iif ...) and i convert them to SQL-Server
Syntax, but i dont found a way to simulate the results of
the Format-Function with was used for numeric data
as "##0.##" or "##0 %" and so on ...
What is the best way to substitute the FORMAT() function
at SQL Server?
Thanks for any help.
Niels
On Fri, 15 Apr 2005 01:33:00 -0700, nieurig wrote:
>Hi folks,
>i have to translate SQL-Statements from Access 2000 to SQL
>Server 2000. There was a lot of Access-Functions used
>(mid, str, iif ...) and i convert them to SQL-Server
>Syntax, but i dont found a way to simulate the results of
>the Format-Function with was used for numeric data
>as "##0.##" or "##0 %" and so on ...
>What is the best way to substitute the FORMAT() function
>at SQL Server?
>Thanks for any help.
>Niels
Hi Niels,
The best way is to return unformatted data to the client and let the
client handle the formatting. Formatting is not a task that the server
is ideally suited for.
If you must do it at the server, then start by looking into functions
such as CONVERT, CAST and STR. If they don't do what you need, then post
some examples of unformatted data and how you want it to look, so that I
(and others) can look into it and try to come up with something.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||good morning hugo,
thanks for your comment.
>The best way is to return unformatted data to the client
and let the
>client handle the formatting. Formatting is not a task
that the server
>is ideally suited for.
Well i have to format the results, because the code of the
given application was lost and i translate the sql-
statements coming from a textfile.
>If you must do it at the server, then start by looking
into functions
>such as CONVERT, CAST and STR. If they don't do what you
need,
I use them for formating datetime values. It works well
but at numeric data the results a not good. therefore i
start to write a serverside function. I will post the code
if it was ready.
Niels
|||On Sat, 16 Apr 2005 00:30:57 -0700, nieurig wrote:
>good morning hugo,
>thanks for your comment.
>and let the
>that the server
>Well i have to format the results, because the code of the
>given application was lost and i translate the sql-
>statements coming from a textfile.
Hi Niels,
I might have misunderstood you. I thought you wanted to find a way to
control the format used for outputting numeric values from SQL Server,
but now I'm starting to think that you need to do the reverse: read in a
file with formatted results and make sure that they are converted to the
correct numeric values by SQL Server.
But maybe I am still misunderstanding you?
>into functions
>need,
>I use them for formating datetime values. It works well
>but at numeric data the results a not good. therefore i
>start to write a serverside function. I will post the code
>if it was ready.
Yes, you can do that. Or you can post some examples of typical data in
input / output pairs, to help me understand what exactly you need to get
done. I can only try to help if I know what you're trying to achieve,
and frankly, I don't know right now. Since I don't know what Access'
FORMAT() function does, you'll need to teach me (by example) - then I
can try to find a way to mimic this in SQL Server.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
i have to translate SQL-Statements from Access 2000 to SQL
Server 2000. There was a lot of Access-Functions used
(mid, str, iif ...) and i convert them to SQL-Server
Syntax, but i dont found a way to simulate the results of
the Format-Function with was used for numeric data
as "##0.##" or "##0 %" and so on ...
What is the best way to substitute the FORMAT() function
at SQL Server?
Thanks for any help.
Niels
On Fri, 15 Apr 2005 01:33:00 -0700, nieurig wrote:
>Hi folks,
>i have to translate SQL-Statements from Access 2000 to SQL
>Server 2000. There was a lot of Access-Functions used
>(mid, str, iif ...) and i convert them to SQL-Server
>Syntax, but i dont found a way to simulate the results of
>the Format-Function with was used for numeric data
>as "##0.##" or "##0 %" and so on ...
>What is the best way to substitute the FORMAT() function
>at SQL Server?
>Thanks for any help.
>Niels
Hi Niels,
The best way is to return unformatted data to the client and let the
client handle the formatting. Formatting is not a task that the server
is ideally suited for.
If you must do it at the server, then start by looking into functions
such as CONVERT, CAST and STR. If they don't do what you need, then post
some examples of unformatted data and how you want it to look, so that I
(and others) can look into it and try to come up with something.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||good morning hugo,
thanks for your comment.
>The best way is to return unformatted data to the client
and let the
>client handle the formatting. Formatting is not a task
that the server
>is ideally suited for.
Well i have to format the results, because the code of the
given application was lost and i translate the sql-
statements coming from a textfile.
>If you must do it at the server, then start by looking
into functions
>such as CONVERT, CAST and STR. If they don't do what you
need,
I use them for formating datetime values. It works well
but at numeric data the results a not good. therefore i
start to write a serverside function. I will post the code
if it was ready.
Niels
|||On Sat, 16 Apr 2005 00:30:57 -0700, nieurig wrote:
>good morning hugo,
>thanks for your comment.
>and let the
>that the server
>Well i have to format the results, because the code of the
>given application was lost and i translate the sql-
>statements coming from a textfile.
Hi Niels,
I might have misunderstood you. I thought you wanted to find a way to
control the format used for outputting numeric values from SQL Server,
but now I'm starting to think that you need to do the reverse: read in a
file with formatted results and make sure that they are converted to the
correct numeric values by SQL Server.
But maybe I am still misunderstanding you?
>into functions
>need,
>I use them for formating datetime values. It works well
>but at numeric data the results a not good. therefore i
>start to write a serverside function. I will post the code
>if it was ready.
Yes, you can do that. Or you can post some examples of typical data in
input / output pairs, to help me understand what exactly you need to get
done. I can only try to help if I know what you're trying to achieve,
and frankly, I don't know right now. Since I don't know what Access'
FORMAT() function does, you'll need to teach me (by example) - then I
can try to find a way to mimic this in SQL Server.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
Missing Distinct Count option in Measure's Aggregate Function property
I am trying to get a "distinct count" aggregate function specificed for a measure that is non-numeric. Is this possible? When I use the cube editor, the Distinct Count appears in the drop down list only when the measure is numeric or datetime. If the measure is varchar, the Distinct Count option is not there.
Any thoughts?
Any thoughts?
Hi,
Did you get any reply and/or solution to this problem.
I have similar issue. Would appreciate if you can share.
Thanks !
|||This should work if you select the measure in the cube designer, and then go to the property sheet (F4). If you set the DataType of the binding Source to WChar for example, you should then set the DataType of the Measure to UnsignedInt. Once you do that, DistinctCount is a valid AggregateFunction.|||I am relatively new to Cube Editor, and trying to follow your suggestion....
1) I selected the Measure (CustomerID), then can see the Properties sheet at bottom (Basic and Advanced) -> then I chenged the data type VarWChar/Unassigned Integer, but when I go back to Aggregate Funcation I do not see the distinct count option.
2) Where do I change the "DataType of the binding Source" for this measure ?
BTW, I am using SQL server/Analysis Services version 2000.
Thanks for help !
|||Sorry, I was referring to SQL BI Studio in SQL 2005 in my reply above. For AS2000, there are some caveats described in the Analysis Services 2000 Books Online Help topic "Using Aggregate Functions" which may provide the answer (excerpts below):
Regular cubes are limited in their use of distinct count measures. Only one distinct count measure is allowed in a regular cube, and it is allowed only if the regular cube does not include a dimension with custom rollup operators or custom rollup formulas. Virtual cubes, however, do not share these limitations. A virtual cube can employ multiple distinct count measures and can also use custom rollup operators and custom rollup formulas in conjunction with distinct count measures.
Because distinct count measures are nonadditive, the presence of a distinct count measure significantly restricts the ability of Microsoft? SQL Server? 2000 Analysis Services to preaggregate the cube. For this reason, it is recommended that each distinct count be placed in its own cube with no other measures. These cubes with distinct count measures can then be joined together with other cubes in a virtual cube that efficiently manages all of the measures.
Note If a cube uses a distinct count measure, the entire cube is treated as nonadditive. Nonadditive cubes do not support dynamically created members, therefore MDX functions, such as VisualTotals, which dynamically create members, will return an error if used on a nonadditive cube. This also affects other features, such as enabling visual totals in dimension security, which involve dynamically created members.
|||Hi Scott Oveson,
I think you are the man to answer my question:
There are 2 tables in my database: OrderHeader,OrderDetail. And the OrderDetail table has different lines for different product. (standard sales order scenario)
If the OrderDetail table is the fact table, I want to design a measure to know many orders we have? I cannot simply use count(orderno) as the measure because the field,orderno, is duplicate in the OrderDetail table, and I cannot use "count distinct" for this measure because I have lots of other measures(do I have to use virtual cube?).
Any suggestions if I want to redesign this cube's structure?(in AS2000)
Thank you so much!
|||Scott Oveson wrote:
Sorry, I was referring to SQL BI Studio in SQL 2005 in my reply above. For AS2000, there are some caveats described in the Analysis Services 2000 Books Online Help topic "Using Aggregate Functions" which may provide the answer (excerpts below):
Regular cubes are limited in their use of distinct count measures. Only one distinct count measure is allowed in a regular cube, and it is allowed only if the regular cube does not include a dimension with custom rollup operators or custom rollup formulas. Virtual cubes, however, do not share these limitations. A virtual cube can employ multiple distinct count measures and can also use custom rollup operators and custom rollup formulas in conjunction with distinct count measures.
Because distinct count measures are nonadditive, the presence of a distinct count measure significantly restricts the ability of Microsoft? SQL Server? 2000 Analysis Services to preaggregate the cube. For this reason, it is recommended that each distinct count be placed in its own cube with no other measures. These cubes with distinct count measures can then be joined together with other cubes in a virtual cube that efficiently manages all of the measures.
Note If a cube uses a distinct count measure, the entire cube is treated as nonadditive. Nonadditive cubes do not support dynamically created members, therefore MDX functions, such as VisualTotals, which dynamically create members, will return an error if used on a nonadditive cube. This also affects other features, such as enabling visual totals in dimension security, which involve dynamically created members.
Are you trying to count the orders for each customer or the details of the orders for each customer? In AS2000 you'd need to use the virtual cube (if you want to get counts from another fact table). In AS2005 you could use multiple measure groups to do this.
|||Scott, i′m sure you can help me...i need to create a cube with 3 distinct-count measures, so i created 3 local cubes with 1 measure each one and 1 virtual cube using this measures.
When i′m creating this virtual cube the wizard asks me to specify the available dimensions. All my 3 local cubes uses exactly the same dimensions ( 36 at all for each cube ) but the wizard allows me to add only the dimensions of one single cube because it can′t contain duplicated dimensions ( and it′s not my intention to display in my pivottable the same dimension repeated 3 times).
When i try to see the data available in my virtual cube, only the measure contained at the local cube that i specified during the wizard is available, the other two measures appears empty. Do you know what i need to do to solve this problem ?
Thanks in advance.
|||It sounds like the problem is due to having the private dimensions (local to the cube) instead of using a shared dimension. Recreate the dimension as a shared dimension and use that in the cubes instead. Then when you create the virtual cube you should be able to do what you're trying to do. You'll need to reprocess the dimension and the cubes.
Hope that helps.
|||Scott,
Thanks for helping me, i did what you told me and worked fine.
Missing Distinct Count option in Measure's Aggregate Function property
I am trying to get a "distinct count" aggregate function specificed for a measure that is non-numeric. Is this possible? When I use the cube editor, the Distinct Count appears in the drop down list only when the measure is numeric or datetime. If the measure is varchar, the Distinct Count option is not there.
Any thoughts?
Any thoughts?
Hi,
Did you get any reply and/or solution to this problem.
I have similar issue. Would appreciate if you can share.
Thanks !
|||This should work if you select the measure in the cube designer, and then go to the property sheet (F4). If you set the DataType of the binding Source to WChar for example, you should then set the DataType of the Measure to UnsignedInt. Once you do that, DistinctCount is a valid AggregateFunction.|||I am relatively new to Cube Editor, and trying to follow your suggestion....
1) I selected the Measure (CustomerID), then can see the Properties sheet at bottom (Basic and Advanced) -> then I chenged the data type VarWChar/Unassigned Integer, but when I go back to Aggregate Funcation I do not see the distinct count option.
2) Where do I change the "DataType of the binding Source" for this measure ?
BTW, I am using SQL server/Analysis Services version 2000.
Thanks for help !
|||Sorry, I was referring to SQL BI Studio in SQL 2005 in my reply above. For AS2000, there are some caveats described in the Analysis Services 2000 Books Online Help topic "Using Aggregate Functions" which may provide the answer (excerpts below):
Regular cubes are limited in their use of distinct count measures. Only one distinct count measure is allowed in a regular cube, and it is allowed only if the regular cube does not include a dimension with custom rollup operators or custom rollup formulas. Virtual cubes, however, do not share these limitations. A virtual cube can employ multiple distinct count measures and can also use custom rollup operators and custom rollup formulas in conjunction with distinct count measures.
Because distinct count measures are nonadditive, the presence of a distinct count measure significantly restricts the ability of Microsoft? SQL Server? 2000 Analysis Services to preaggregate the cube. For this reason, it is recommended that each distinct count be placed in its own cube with no other measures. These cubes with distinct count measures can then be joined together with other cubes in a virtual cube that efficiently manages all of the measures.
Note If a cube uses a distinct count measure, the entire cube is treated as nonadditive. Nonadditive cubes do not support dynamically created members, therefore MDX functions, such as VisualTotals, which dynamically create members, will return an error if used on a nonadditive cube. This also affects other features, such as enabling visual totals in dimension security, which involve dynamically created members.
|||Hi Scott Oveson,
I think you are the man to answer my question:
There are 2 tables in my database: OrderHeader,OrderDetail. And the OrderDetail table has different lines for different product. (standard sales order scenario)
If the OrderDetail table is the fact table, I want to design a measure to know many orders we have? I cannot simply use count(orderno) as the measure because the field,orderno, is duplicate in the OrderDetail table, and I cannot use "count distinct" for this measure because I have lots of other measures(do I have to use virtual cube?).
Any suggestions if I want to redesign this cube's structure?(in AS2000)
Thank you so much!
|||Sorry, I was referring to SQL BI Studio in SQL 2005 in my reply above. For AS2000, there are some caveats described in the Analysis Services 2000 Books Online Help topic "Using Aggregate Functions" which may provide the answer (excerpts below):
Regular cubes are limited in their use of distinct count measures. Only one distinct count measure is allowed in a regular cube, and it is allowed only if the regular cube does not include a dimension with custom rollup operators or custom rollup formulas. Virtual cubes, however, do not share these limitations. A virtual cube can employ multiple distinct count measures and can also use custom rollup operators and custom rollup formulas in conjunction with distinct count measures.
Because distinct count measures are nonadditive, the presence of a distinct count measure significantly restricts the ability of Microsoft? SQL Server? 2000 Analysis Services to preaggregate the cube. For this reason, it is recommended that each distinct count be placed in its own cube with no other measures. These cubes with distinct count measures can then be joined together with other cubes in a virtual cube that efficiently manages all of the measures.
Note If a cube uses a distinct count measure, the entire cube is treated as nonadditive. Nonadditive cubes do not support dynamically created members, therefore MDX functions, such as VisualTotals, which dynamically create members, will return an error if used on a nonadditive cube. This also affects other features, such as enabling visual totals in dimension security, which involve dynamically created members.
Are you trying to count the orders for each customer or the details of the orders for each customer? In AS2000 you'd need to use the virtual cube (if you want to get counts from another fact table). In AS2005 you could use multiple measure groups to do this.
|||Scott, i′m sure you can help me...i need to create a cube with 3 distinct-count measures, so i created 3 local cubes with 1 measure each one and 1 virtual cube using this measures.
When i′m creating this virtual cube the wizard asks me to specify the available dimensions. All my 3 local cubes uses exactly the same dimensions ( 36 at all for each cube ) but the wizard allows me to add only the dimensions of one single cube because it can′t contain duplicated dimensions ( and it′s not my intention to display in my pivottable the same dimension repeated 3 times).
When i try to see the data available in my virtual cube, only the measure contained at the local cube that i specified during the wizard is available, the other two measures appears empty. Do you know what i need to do to solve this problem ?
Thanks in advance.
|||It sounds like the problem is due to having the private dimensions (local to the cube) instead of using a shared dimension. Recreate the dimension as a shared dimension and use that in the cubes instead. Then when you create the virtual cube you should be able to do what you're trying to do. You'll need to reprocess the dimension and the cubes.
Hope that helps.
|||Scott,
Thanks for helping me, i did what you told me and worked fine.
Missing Distinct Count option in Measure's Aggregate Function property
I am trying to get a "distinct count" aggregate function specificed for a measure that is non-numeric. Is this possible? When I use the cube editor, the Distinct Count appears in the drop down list only when the measure is numeric or datetime. If the measure is varchar, the Distinct Count option is not there.
Any thoughts?
Any thoughts?
Hi,
Did you get any reply and/or solution to this problem.
I have similar issue. Would appreciate if you can share.
Thanks !
|||This should work if you select the measure in the cube designer, and then go to the property sheet (F4). If you set the DataType of the binding Source to WChar for example, you should then set the DataType of the Measure to UnsignedInt. Once you do that, DistinctCount is a valid AggregateFunction.|||I am relatively new to Cube Editor, and trying to follow your suggestion....
1) I selected the Measure (CustomerID), then can see the Properties sheet at bottom (Basic and Advanced) -> then I chenged the data type VarWChar/Unassigned Integer, but when I go back to Aggregate Funcation I do not see the distinct count option.
2) Where do I change the "DataType of the binding Source" for this measure ?
BTW, I am using SQL server/Analysis Services version 2000.
Thanks for help !
|||Sorry, I was referring to SQL BI Studio in SQL 2005 in my reply above. For AS2000, there are some caveats described in the Analysis Services 2000 Books Online Help topic "Using Aggregate Functions" which may provide the answer (excerpts below):
Regular cubes are limited in their use of distinct count measures. Only one distinct count measure is allowed in a regular cube, and it is allowed only if the regular cube does not include a dimension with custom rollup operators or custom rollup formulas. Virtual cubes, however, do not share these limitations. A virtual cube can employ multiple distinct count measures and can also use custom rollup operators and custom rollup formulas in conjunction with distinct count measures.
Because distinct count measures are nonadditive, the presence of a distinct count measure significantly restricts the ability of Microsoft? SQL Server? 2000 Analysis Services to preaggregate the cube. For this reason, it is recommended that each distinct count be placed in its own cube with no other measures. These cubes with distinct count measures can then be joined together with other cubes in a virtual cube that efficiently manages all of the measures.
Note If a cube uses a distinct count measure, the entire cube is treated as nonadditive. Nonadditive cubes do not support dynamically created members, therefore MDX functions, such as VisualTotals, which dynamically create members, will return an error if used on a nonadditive cube. This also affects other features, such as enabling visual totals in dimension security, which involve dynamically created members.
|||Hi Scott Oveson,
I think you are the man to answer my question:
There are 2 tables in my database: OrderHeader,OrderDetail. And the OrderDetail table has different lines for different product. (standard sales order scenario)
If the OrderDetail table is the fact table, I want to design a measure to know many orders we have? I cannot simply use count(orderno) as the measure because the field,orderno, is duplicate in the OrderDetail table, and I cannot use "count distinct" for this measure because I have lots of other measures(do I have to use virtual cube?).
Any suggestions if I want to redesign this cube's structure?(in AS2000)
Thank you so much!
|||Sorry, I was referring to SQL BI Studio in SQL 2005 in my reply above. For AS2000, there are some caveats described in the Analysis Services 2000 Books Online Help topic "Using Aggregate Functions" which may provide the answer (excerpts below):
Regular cubes are limited in their use of distinct count measures. Only one distinct count measure is allowed in a regular cube, and it is allowed only if the regular cube does not include a dimension with custom rollup operators or custom rollup formulas. Virtual cubes, however, do not share these limitations. A virtual cube can employ multiple distinct count measures and can also use custom rollup operators and custom rollup formulas in conjunction with distinct count measures.
Because distinct count measures are nonadditive, the presence of a distinct count measure significantly restricts the ability of Microsoft? SQL Server? 2000 Analysis Services to preaggregate the cube. For this reason, it is recommended that each distinct count be placed in its own cube with no other measures. These cubes with distinct count measures can then be joined together with other cubes in a virtual cube that efficiently manages all of the measures.
Note If a cube uses a distinct count measure, the entire cube is treated as nonadditive. Nonadditive cubes do not support dynamically created members, therefore MDX functions, such as VisualTotals, which dynamically create members, will return an error if used on a nonadditive cube. This also affects other features, such as enabling visual totals in dimension security, which involve dynamically created members.
Are you trying to count the orders for each customer or the details of the orders for each customer? In AS2000 you'd need to use the virtual cube (if you want to get counts from another fact table). In AS2005 you could use multiple measure groups to do this.
|||Scott, i′m sure you can help me...i need to create a cube with 3 distinct-count measures, so i created 3 local cubes with 1 measure each one and 1 virtual cube using this measures.
When i′m creating this virtual cube the wizard asks me to specify the available dimensions. All my 3 local cubes uses exactly the same dimensions ( 36 at all for each cube ) but the wizard allows me to add only the dimensions of one single cube because it can′t contain duplicated dimensions ( and it′s not my intention to display in my pivottable the same dimension repeated 3 times).
When i try to see the data available in my virtual cube, only the measure contained at the local cube that i specified during the wizard is available, the other two measures appears empty. Do you know what i need to do to solve this problem ?
Thanks in advance.
|||It sounds like the problem is due to having the private dimensions (local to the cube) instead of using a shared dimension. Recreate the dimension as a shared dimension and use that in the cubes instead. Then when you create the virtual cube you should be able to do what you're trying to do. You'll need to reprocess the dimension and the cubes.
Hope that helps.
|||Scott,
Thanks for helping me, i did what you told me and worked fine.
Subscribe to:
Posts (Atom)