Performance comparison between VARCHAR and CLOB?

Any idea on performance comparison between VARCHAR and CLOB? For example, if I want to insert or update 4000 characters in a field of a row and I am confused whether that column's type would better be decalred as VARCHAR or CLOB, what do you suggest?I suggest you not declare anything as a CLOB in sql server.

Percentile Calculation

Hi All,

I have around 1000 employee records containing employee number and their salary. How do i calculate percentile on these records?

For example
How to calculate 25th Percentile on Salary of these 1000 records?

Hope I am clear with my question.

Thanks in advance.

Regards,
qALook up the "TOP n [PERCENT]" clause in Books Online.

select top 25 percent * from [YourTable]|||Hi,
Thanks for your immediate response. But you got it wrong. Its not Percentage, its PERCENTILE. If you have used MS-Excel there is a function called
PERCENTILE(Array, k)

For example if I have 10,20,30,40,50 as values
then 30th PERCENTILE of this comes as 22

Thanks
qA|||No, I don't get how the calculation is done. And Excel's Books Online (which is normally very good at describing statistical functions) gives no explanation of the algorithm at all.|||Wikipedia, while citing Excel's function as an example, appears to me to be describing behavior similiar to TOP %:

http://en.wikipedia.org/wiki/Percentile|||Hi,
The top clause in a query will return one ore more than one records. But Percentile will always return one and only one value.|||Yes, but how to calculate that value? I found this lovely quote:

"There is no universally accepted definition of a percentile."

on this website: http://cnx.rice.edu/content/m10805/latest/

Very curious. I guess what intrigues me is that I have been using Excel and doing applied business statistics and data mining for more than a decade and have not run into this, or ever used that Excel function before.

Try describing what you want to do, without referring to the Excel function.|||OK, the more I research PERCENTILE calculation, the more I understand why I've never used it. It is about the stupidest statistical measure I have ever seen. Why? Because makes a strict linear interpolation between just two data points out of the set, without any regard to the distribution of the data.

As an example, the 30th percentile of your values (10, 20, 30, 40, 50) is 22, as Excel calculates it. But the 30th percentile of the following values (0, 10, 20, 1000, 1000000) is...guess what...also 22.

I see no practical purpose for such a measure unless your goal is to mislead your audience.

Percentage?

Hi,
example:
if:
Price = 50
Percentage = 50
SQL column Liquid will return 25
Select Price - ((Price*Percentage)/100) as Liquid From Product
is the best way of doing that?You need to describe your issue better.
However, what you did describe sounds valid as long as you're willing to
deal with the inevetable rounding error.
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23lA50biJIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Hi,
> example:
> if:
> Price = 50
> Percentage = 50
> SQL column Liquid will return 25
> Select Price - ((Price*Percentage)/100) as Liquid From Product
> is the best way of doing that?
>|||Example, if exists a product (U$50) and a 50% discount, how much the buyer
will pay?
I tried to convert to english language my "problem"... I hope you can
understand!
Thanks a lot!
"Jay" <nospam@.nospam.org> escreveu na mensagem
news:%23%232wVblJIHA.5624@.TK2MSFTNGP04.phx.gbl...
> You need to describe your issue better.
> However, what you did describe sounds valid as long as you're willing to
> deal with the inevetable rounding error.
> "Paulo" <prbspfc@.uol.com.br> wrote in message
> news:%23lA50biJIHA.4272@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> example:
>> if:
>> Price = 50
>> Percentage = 50
>> SQL column Liquid will return 25
>> Select Price - ((Price*Percentage)/100) as Liquid From Product
>> is the best way of doing that?
>|||Is this what you mean?
USE tempdb
CREATE TABLE items (
Price DECIMAL(9,2),
Percentage TINYINT,
Liquid AS ((Price*Percentage)/100)
)
insert into items values (50, 50)
SELECT Price, Percentage, Liquid
FROM items
Returns: 50.00 50 25.000000
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23lA50biJIHA.4272@.TK2MSFTNGP06.phx.gbl...
> Hi,
> example:
> if:
> Price = 50
> Percentage = 50
> SQL column Liquid will return 25
> Select Price - ((Price*Percentage)/100) as Liquid From Product
> is the best way of doing that?
>

Percentage of total column in Report Builder

Hello,
We are the using the end-user tool Report Builder. I want to create a
'Percentage of total' column to a certain table. For example:
Value Percentage of total
Region A 200 20%
Region B 500 50%
Region C 300 30%
--
Total 1000 100%
How can I achieve that?
Regards,
Jaap MosselmanHello Jaap,
I sugget you add a new field in the Report Build which calculate the
Percentage of the total.
And you could format the column to show as the percentage.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Ok, new field is not problem, but how can I retrieve the actual (sub)total
value (in this example 1000) to use as denominator?
Value Percentage of total
Region A 200 20%
Region B 500 50%
Region C 300 30%
--
Total 1000 100%
Regards, Jaap
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> schreef in bericht
news:J3V2QNplHHA.1140@.TK2MSFTNGHUB02.phx.gbl...
> Hello Jaap,
> I sugget you add a new field in the Report Build which calculate the
> Percentage of the total.
> And you could format the column to show as the percentage.
> Hope this helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||You should be able to get the sum by doing something like this:
=IIF(sum(Fields!Yourvalue.value), "yourdataset")>0, fields!YourValue.Value /
sum(Fields!YourValue.Value, "yourdataset"))
Or you could do the summing in your dataset, and just use it as a normal
field.
Kaisa M. Lindahl Lervik
"Jaap" <Jaap@.newsgroups.nospam> wrote in message
news:u3lwBX6lHHA.4960@.TK2MSFTNGP02.phx.gbl...
> Ok, new field is not problem, but how can I retrieve the actual (sub)total
> value (in this example 1000) to use as denominator?
> Value Percentage of total
> Region A 200 20%
> Region B 500 50%
> Region C 300 30%
> --
> Total 1000 100%
> Regards, Jaap
> "Wei Lu [MSFT]" <weilu@.online.microsoft.com> schreef in bericht
> news:J3V2QNplHHA.1140@.TK2MSFTNGHUB02.phx.gbl...
>> Hello Jaap,
>> I sugget you add a new field in the Report Build which calculate the
>> Percentage of the total.
>> And you could format the column to show as the percentage.
>> Hope this helps.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Is that possible in de Report Builder end user tool?
Or only in the VS Report Editor?
Jaap
"Kaisa M. Lindahl Lervik" <kaisaml@.hotmail.com> schreef in bericht
news:%23I4ML46lHHA.3484@.TK2MSFTNGP02.phx.gbl...
> You should be able to get the sum by doing something like this:
> =IIF(sum(Fields!Yourvalue.value), "yourdataset")>0, fields!YourValue.Value
> / sum(Fields!YourValue.Value, "yourdataset"))
> Or you could do the summing in your dataset, and just use it as a normal
> field.
> Kaisa M. Lindahl Lervik
> "Jaap" <Jaap@.newsgroups.nospam> wrote in message
> news:u3lwBX6lHHA.4960@.TK2MSFTNGP02.phx.gbl...
>> Ok, new field is not problem, but how can I retrieve the actual
>> (sub)total value (in this example 1000) to use as denominator?
>> Value Percentage of total
>> Region A 200 20%
>> Region B 500 50%
>> Region C 300 30%
>> --
>> Total 1000 100%
>> Regards, Jaap
>> "Wei Lu [MSFT]" <weilu@.online.microsoft.com> schreef in bericht
>> news:J3V2QNplHHA.1140@.TK2MSFTNGHUB02.phx.gbl...
>> Hello Jaap,
>> I sugget you add a new field in the Report Build which calculate the
>> Percentage of the total.
>> And you could format the column to show as the percentage.
>> Hope this helps.
>> Sincerely,
>> Wei Lu
>> Microsoft Online Community Support
>> ==================================================>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> ==================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>|||Hello Jaap,
Yes, you could use, but with some modification.
In the Report Builder, you may Edit the Formula of a cell. And you could
use the IF function and SUM function.
Hope it helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Jaap,
I reproduce this issue.
The SUM(Total Area) only sum the field in the record.
You may need to aggregate the filed in the raw data and regenerate the
report model.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||But in general, you don't know what the 100% value is, the denominator,
because you don't know which selections and filters the end user is
creating.
Please, place it on the wishlist to have an option to do a 'percent of
total' formatting.
OK, for some specific situations, you can do it the hardcodes way you
suggested.
Regards,
Jaap
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> schreef in bericht
news:qyvcwjqnHHA.1144@.TK2MSFTNGHUB02.phx.gbl...
> Hello Jaap,
> I reproduce this issue.
> The SUM(Total Area) only sum the field in the record.
> You may need to aggregate the filed in the raw data and regenerate the
> report model.
> Hope this helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Jaap,
The Report Builder is a User end report generate tool. It did not include
all the function which Report Designer has.
If you have any funcional request, please send your feedback to the
following url:
http://connect.microsoft.com/sqlserver.
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Percentage of occurrences of a value

I want to be able to retrieve the percentage that one value occurs in a field in a group. For example, a field that has either "Yes" or "No", if there are 3 "Yes" values and 1 "No" value (using mixed real and fake SQL..)

SELECT DATEPART(m,datefield), PECENTAGEOFAVALUE(YesNoField, 'Yes')
FROM sampletable
GROUP BY DATEPART(m,datefield)
Result
75

How can I do this? I know I can get the denominator by just doing a COUNT(*), but how do I count "Yes" only?

Thanks,
Kayda

Here it is..

Code Snippet

Create Table #data (

[Id] int ,

[Response] Char

);

Insert Into #data Values('1','Y');

Insert Into #data Values('2','N');

Insert Into #data Values('3','Y');

Insert Into #data Values('4','Y');

Insert Into #data Values('5','N');

Insert Into #data Values('6','N');

Insert Into #data Values('7','N');

Select

Isnull(Sum(Case When Response='Y' Then 1 End),0) / Sum(1.0) *100,

Isnull(Sum(Case When Response='N' Then 1 End),0) / Sum(1.0) *100

From

#Data

Code Snippet

--For Your query

SELECT

DATEPART(m,datefield)

Isnull(Sum(Case When YesNoField='Yes' Then 1 End),0) / Sum(1.0) * 100 PecentageofYes,

Isnull(Sum(Case When YesNoField='No' Then 1 End),0) / Sum(1.0) * 100 PecentageofNo

FROM

sampletable

GROUP BY

DATEPART(m,datefield)

|||

Here is an example:

Code Snippet

create table #t (

c1 datetime not null,

c2 char(1) not null

);

insert into #t values('20070101','y');

insert into #t values('20070115','n');

insert into #t values('20070125','y');

insert into #t values('20070205','y');

insert into #t values('20070217','n');

insert into #t values('20070225','n');

insert into #t values('20070227','n');

;with sums

as

(

select

convert(char(6), c1, 112) as ym,

sum(case when c2 = 'y' then 1 else 0 end) as sum_y,

sum(case when c2 = 'n' then 1 else 0 end) as sum_n,

count(*) over() as cnt

from

#t

group by

convert(char(6), c1, 112)

)

select

ym,

(sum_y * 100.00) / nullif(cnt, 0) as avg_y,

(sum_n * 100.00) / nullif(cnt, 0) as avg_n

from

sums

order by

ym

drop table #t

go

AMB

Percentage Calucated Measure across several dimensions

> You can use tuple. For example,
> [Measures].[expenditure amount] / ([Expenditure].[All expe
nditures],
> [Measures].[expenditure amount])
I used this formular but in my case it only works properly with one
dimension. The formular I want to write is quite simple
[Measures].[Value] / <Queryresult>
where <Queryresult> is the sum of all values. But I am a complete newby in
MDX and don't know how to accomplish it. Can anyone give me a hand?
OliverYou can use tuple again.
[Measures].[Value] / ([Measures].[Value], [All Dim1], &#
91;All Dim2], [All
Dim3], ..., [All DimN])
Next version will support Root() for this purpose.
Ohjoo Kwon
"Oliver Kerber" <okerber@.bigfoot.de> wrote in message
news:d1jsjh$pvr$02$1@.news.t-online.com...
> I used this formular but in my case it only works properly with one
> dimension. The formular I want to write is quite simple
> [Measures].[Value] / <Queryresult>
> where <Queryresult> is the sum of all values. But I am a complete newby in
> MDX and don't know how to accomplish it. Can anyone give me a hand?
> Oliver
>