Average Of Middle 90%

Sep 21, 2007

Hello all!

This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:
1
1 <-From here
1
2
3
4
4
5
6 <- To here.
7
I thought I could solve it by subqueries and do the following:
Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9
which would give me what I want, but I realised that when aggregating I cant order the subqueries.

This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.

I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?

View 9 Replies


ADVERTISEMENT

Analysis :: Calculation Of average Using DAX AVERAGE And AVERAGEX

Jun 21, 2015

Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:

Avg_AMP:=AVERAGEX('Fct Sales';'Fct Sales'[_AMP]/100)
Avg_AMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_AMPdollar]/100)
Avg_FMP:=AVERAGEX('Fct Sales';'Fct Sales'[_FMP]/100)
Avg_FMPdollar:=AVERAGEX('Fct Sales';'Fct Sales'[_FMPdollar]/100)

The results were, respectively: 701,68; 2120,60...; -669,441; and  finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:

test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17
test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...

So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.

I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.

View 2 Replies View Related

Need An Average By Year Of An Average By Month

Feb 15, 2008

I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged...
I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25.
The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement.
?confused?

Here's the original query.
accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '

SELECT name, country_cd from weather_station where platformId=&&platformId;

SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS
FROM daily
WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime
GROUP BY to_char(datetime,'MM')
ORDER BY to_char(datetime,'MM');

with a result of:

NAME_________________CO
-------------------- --
OFFUTT AFB___________US

MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS
-- ---------- ---------- ---------- -----------
01_________21______-5.31________-30__________60
02_________26______-2.19______-28.3__________61
03_______31.1_______3.61______-26.1__________60
04_______35.6______11.07______-12.2__________60
05_______37.2_______17.2_______-3.3__________60
06_______41.1______22.44__________5__________60
07_______43.3______24.92________7.2__________60
08_______40.6______23.71________5.6__________60
09_________40______18.84_______-2.2__________59
10_______34.4_______12.5_______-8.9__________59
11_________29_______4.13______-23.9__________60
12_________21______-2.52______-28.3__________60

View 4 Replies View Related

How Can I Select Middle .. From?

Oct 25, 2007

I have a table Customer with 200000 records.

When I run query

DECLARE @posTable TABLE(idkey INT IDENTITY(1,1), value VARCHAR(128))
INSERT INTO @posTable (value) SELECT CustID FROM Customer ORDER BY CustID

At the first time it takes 1 minute 42 seconds, at the second time it only takes 3 seconds.

When i run the query

SELECT IDENTITY(INT, 1, 1) AS idkey, CustID INTO #posTable FROM Customer ORDER BY CustID
The order in column idkey is not exacts


Pls. tell me why? and How can i rewrite it.

View 8 Replies View Related

Returning Middle Records

Dec 7, 2006

I'm writing a page that will return data from my database to the user based on their search paramaters, over several pages with 20 matching records showing per page, and a next button on the bottom. Similar to the format of any search engine.
However, I'd like to write this into the query, and I'm not sure how I would go about doing so. For example:
 "SELECT TOP 20 to 40 * FROM Northwind"
 Hopefully this makes sense. Is there any way of doing this?
Thanks in advance,Russ

View 2 Replies View Related

Only Put A Period . If There Is A Middle Intial.

Dec 29, 2003

I am trying to concatenate three fields into one in a View. I have the following data:
Last Name
First Name
Middle Initial (can be null)

I need my resultant field data to look like the following:
"Doe, John P."

I'm having a problem writing SQL that is sensitive to placing the period after the middle initial only if there is a middle initial present. If there isn't a middle initial, I just want the following: "Doe, John".

I have tried the following CASE statement:

CASE WHEN middleInitial IS NOT NULL THEN ' ' + middleInitial + '.' ELSE '' END

However, I get an error indicating that the CASE statement is not supported in the Query Designer.

How can I resolve this problem in a View? Is there a function similar to ISNULL(middleInitial, '') that would allow for the "."?

View 4 Replies View Related

Extract Value From Middle Of String

Sep 9, 2005

Lets say I have a column of type varchar and need to extract an integer value from the middle of it.  The string looks like this:'this part is always the same' + integer of varying length + 'this part is different but always the same length'Is there a way to trim the constant string lengths from the beginning and end?

View 2 Replies View Related

Can You Trim In The Middle Of A Join?

Dec 12, 2001

Is it possible to use a TRIM(){or similar} function when JOINing 2 tables via a NVARCHAR field in T-SQL?

I need to trim the field first because some of the data contains trailing spaces (GRRR) and so might future data...

View 2 Replies View Related

Selecting A Middle Date

May 3, 2006

So I have this query where i need to get the average date of about five different dates... Is there any way to do this or am I screwed. I looked at using the avg function but SQL server 2005 did not like that.

Thanks in advance for any help.

View 3 Replies View Related

Retreive The Middle Of A String

Apr 5, 2007

Im having a dumb moment... what is the easiest way to get the third and fourth character of a string???

Thanks :)

View 7 Replies View Related

Get Middle Initial From A Name Field

Nov 15, 2013

I need to display the middle initial from a name field that contains the last name, comma, and the middle name or initial.

Example data:

Jane,Smith Ron
John,Dow L
Mary Jane,Dow Welsh

The result I am looking for is to capture only the first letter of the middle name. In this data example, I would need to display the following on a separate column:

R
L
W

View 5 Replies View Related

Getting The Middle Initial From A Name Column

Jan 7, 2004

-- I have a first name field that sometimes contains only the first name,
-- sometimes contains the first name and the middle initial, and
-- sometimes contains the first name, a space, followed by NMI (for no middle initial)
-- how do I correctly grab the first letter of the middle initial in all cases?
-- I have been playing with patindex but its harder than I thought. guess I need a case
-- statement in addition to this. Any idea how I can do this?
-- thanks!

create table UHS_t1 (c1 varchar(20))
insert UHS_t1 select 'john a'
insert UHS_t1 select 'jeff b'
insert UHS_t1 select 'sue z'
insert UHS_t1 select 'joe nmi'
insert UHS_t1 select 'jamie'


select *, substring(c1, patindex('%[ ]%', c1)+1, 1) as middle_name
from UHS_t1
go
drop table UHS_t1

View 10 Replies View Related

How To Stop A Restore In The Middle

Aug 13, 2012

How do I stop the restore in the middle?

restore database blah
from disk='mybackups hismonthbackup1.BAK'
with replace, recovery, stats=5, maxtransfersize=1048576

View 3 Replies View Related

Trim Spaces In Middle

Jun 20, 2006

In a table I have a coulmn of postal-codes where there is a space in the middle of the postcode. How do I do to trim them away?

View 6 Replies View Related

How To Get String Value In A Middle Position?

Feb 26, 2007

khosara writes "I have one parameter @String with value "My name is Khosara".
How to get the value only "Is khos".
Could you please help me, witch method shold i use.
Thank in advance."

View 3 Replies View Related

Apostrophe In The Middle Dilemma

Nov 16, 2007

How do you get the following to work properly?

WHERE Location = 'John's House'

The apostrophe between n and s has me stumped. Thanks.



View 6 Replies View Related

Hello See This Command For Name Split How To Get MIDDLE NAME

Apr 14, 2008

Hello The following command is for Name Split as First Name and Second Name. Please let me know based on the following only how to get Middle name. SELECT name,
CASE WHEN CHARINDEX(',',Name) > 0 THEN SUBSTRING(Name,CHARINDEX(',',Name)+1,LEN(Name)-CHARINDEX(',',Name)) ELSE '' END AS Fname,
CASE WHEN CHARINDEX(',',Name) > 0 THEN LEFT(Name,CHARINDEX(',',Name)-1) ELSE Name

END AS Lname
FROM YourTable

View 1 Replies View Related

Middle Search Support

Oct 26, 2006

Some of the databases that I inherited contain search that are based on finding a string anywhere within a last name such as:


WHERE lastName like '%smith%'

It is desired that each of these names be returned:


Smith
Smithson
Nesmith

What is presently done is that updates to the last name fields trigger that substrings of the last name be sent off into a substring table wtih retention of no 2-char substrings. For these three last names the following would be kept:


(1) Smith, (2) mith, (3) ith and (4) th
(1) Smithson, (2) mithson, (3) ithson, ..., (n) on
(1) Nesmith, (2) esmith, (3) smith, ... (n) th

The where now becomes


WHERE lastNameSub like 'smith%'

This seems to make the search routine by last name faster. I would like to improve on this if I can. Suggestions?


Dave

View 1 Replies View Related

How To Insert A Record In The Middle Of A DB

Dec 17, 2005

Well, how do you do it?  I've not seen any report on this... so could it be next to impossible? 

Say you have 10 records, 1-10 and you want to insert a new record between record 4 and record 5 in a SQL database, just how do you code it?   And oh, each record uses the auto ID increment feature.

View 26 Replies View Related

Function Returning Middle Values

Mar 7, 2007

I have this assignment where i have a table full of two digit exam scores and I have to write a function that eliminate x number of top values and x number of bottom values and return all the middle values. When the function is called, obviously a number is entered such as 3 and the top 3 and bottom 3 scores are not returned.
i.e. SELECT * FROM GetMiddleValues (3);

If anyone has any ideas on how to accomplish this, that would be great.

Thanks

View 1 Replies View Related

Updating Data In Middle Of Field

Sep 6, 2013

I have a table with 100,000 addresses and I need to change 'street' to 'st', 'avenue' to 'ave' and 'road' to 'rd'. These words can appear anywhere in the field. example

10 Main street & Washington avenue

Trying to get it to be

10 Main st & Washington ave

View 6 Replies View Related

SSIS Hanging In The Middle Of Processing

Jun 25, 2006

Hi,

We have a problem importing the flat file data using SSIS into sql database.

For some erroneous files the package is just hanging with out any activity (Disk Reads/sec, Disk Writes/sec, Page faults/sec, Workingset Memory counters are const when the package hung). The package is giving the following messages before it is hung.

The column data for column "ServerName" overflowed the disk I/O buffer.
An error occurred while processing file "M:Tani1APS-PRXY-02APS-PRXY-02-ISALOG_20060621_FWS_001-2006062106.Log" on data row 1.
The PrimeOutput method on component "Firewall Data" (6109) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Thread "SourceThread0" has exited with error code 0xC0047038.
Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Thread "WorkThread0" has exited with error code 0xC0047039.

Even though it says has SourceThread0 has exited, it package is not terminating itself. We just want to ignore the errorneous file and move on to other files.

I tried altering DefaultBufferMaxRows in between 1000-10000, DefaultBufferSize to 1 MB €“ 20 MB which did not work out. Can one of you suggest us any resolution/work around for this issue? This is really a burning issue in our project would really appreciate any help!

-Syam.

View 3 Replies View Related

SQL Query Question- Querying The Middle Table In A M To M

May 17, 2000

I need help with a query, and I haven't seen anything like this in a SQL book.

I will use the Northwind database for this example. The tables I am using are Products, Order_Detail, and Orders. There is a many to many relationship between Products and Orders, that is resolved by the Order_Detail table. Suppose I want to find out which products are related to each order. How would I query the tables?

Thanks everyone,
Nathan

View 1 Replies View Related

SQL Server 2008 :: Splitting A Field In The Middle?

Mar 19, 2015

I have a field which looks like "LastName, FirstName (DOB: 01/01/1900)"

How do I get the "01/01/1900" between ":" and ")"

View 6 Replies View Related

Retrieve Only Strings That Hold Sub-string In Middle

Nov 24, 2013

I need retrieve only strings that hold the sub-string in middle. no first no last just middle.

See: the sub-string is "test"

Yes: test test test
Yes: take test test
Yes: test test take
Yes: take test take
No: test test
No: test take take
No: take take test
No: test

I prepare sample data...

declare @sample table(s varchar(50));
insert @sample values
('test test test'),
('test test'),
('test'),
('test take test'),
('test take take'),
('take test test'),
('take test take');

View 4 Replies View Related

Code In The Database Or Middle Tier (the CLR Controversy)

Jul 23, 2005

There doesn't seem to be consensus about when to put code in thedatabase or in the middle tier. There was a long discussion about thisin an Oracle newsgroup (message ID:ULcQb.466$KU5.37@nwrddc02.gnilink.net).Elsewhere there's been discussion about Microsoft SQL Server 2005adding the CLR to support stored procedures in languages such as C#. Ascan of the Web and discussion forums finds differing opinions aboutthis.Two authors have written articles that fall on different sides of thedebate."Keys to the Database"http://www.intelligententerprise.co...icleID=50500830"SOA, Multi-Tier Architectures and Logic in the Database"http://www.sqlsummit.com/Articles/L...TheDatabase.HTMJoe Celko wrote the first article, but his objections point toMicrosoft SQL Server 2005:"I have an article at WWSUG.com on how much I hate the CLR stuff thatMicrosoft is putting out."http://blog.intelligententerprise.c...ves/002419.html"The bad news is that SQL Server 2005 will you define your ownaggregatefunctions in a CLR language."Message id: Join Bytes!IBM DB2 and Oracle are doing the same thing with the .NET CLR. Is thisa non-issue or are all three companies misguided?

View 49 Replies View Related

Best Way To Order Results Sequentially Starting From Somewhere In The Middle

Apr 19, 2007

I'm working with SQL Server 2005, and I'm trying to sort the results based on a user selected letter. Say the user selects 'D' to filter his results. I'd like to return the results starting from D followed by E, F, G...Z, A, B, C. What I'm getting is the results for the D entries at the top of the result set, followed by A, B, C, E...Z.

A solution comes to mind that would be very long and db intensive, by querying on 'like 'D', followed by like 'E', followed by like 'F', etc, but I'm sure that there is a much more efficient way to do this. Below is the code that I'm using now.





' where @SortString = 'd' and @Test is a temp Table



BEGIN

Insert into @Test

Select CompanyName,ContactId, CompanyId

from vContacts where CompanyName like @SortString +'%'

Order by CompanyName



Insert into @Test

Select CompanyName,ContactId, CompanyId

from vContacts where CompanyName not like @SortString +'%'

Order by CompanyName

END



Thanks in advance for your help

View 3 Replies View Related

How To Enable Auto-scroll By Middle Clicking

Oct 8, 2015

In SQL Server 2008 when I click the middle button I could scroll up and down(scroll wheel) by just flicking the mouse. We just upgraded to 2012 and I can't do that on my Lenovo ThinkPad. I am using an external wired usb mouse(Intelli Mouse) to scroll.

I have SQL server 2008 and 2012 installed on the same machine scrolling works in 2008 but not in 2012.

View 5 Replies View Related

Need Call A DB Function In The Middle Of The Dataflow Process

Jun 19, 2006


All,

I have to use a field that is calculated in a data flow process and call a database function (return a value) to do anther calculation; then return a value back to the data flow. I tried OLD DB Command but I cannot configure to return a value back to the same data flow.
If there any transformations that can call a DB function and get a value from the function in the middle of the data flow process? Need more detailed instruction.

The data flow is Like:

SourceDB Ã New_filed 1 = field1 + filed2 Ã New_filed 2= DB_function (New_filed 1) Ã Destination DB


Thanks in Advance
Jessie

View 4 Replies View Related

Extract Data From Middle Of String In SQL Server

Jan 11, 2008

I am trying to write a query in sql query analyzer that will extract a date that appears after the first comma in the string. An example of the data is below:

DAVE,12/31/03,17:18,PERIODENDP
AS,01/16/06,16:51,CUSTOMERS_VW
EM,11/09/07,15:08,ORDER_ENTRY_

I want to select the 8 characters after the first comma in the field, and then convert it to a date format.

I am a novice, so any help with the correct function and syntax would be greatly appreciated.

thanks,
Dave

View 8 Replies View Related

Using SQLCE As A Middle Tier Caching Solution

Dec 26, 2007

Hi All,
Im trying to migrate a N tiered solution written in Java and Berkeley DB(in memory DB product) to MS stack. ie the in memory DB is being used as a middle tier caching solution.

Im considering using SQLCE where Berkely DB was being used, im guessing that SQLCE being an inproc DB may be faster than using SQLExpress etc.

Has any one tried to do this before? and if so can you share your experience?
Alternatively if you can provide any resources on the web that discuss SQLCE performance(ie not when run on WinCE etc)/ comparison of query performance between SQLCE and SQL Express/Standard that would be great.

Thanks in advance

-Melvin

View 1 Replies View Related

Error 17824 - Aborts My Script In The Middle Of Loop

Dec 18, 1998

Hi,

I´m running the script below:

DECLARE @DTstart SMALLDATETIME, @DTend SMALLDATETIME
SELECT @DTstart = "980801", @DTend = "980810"
SELECT ' processing from ', @DTstart , ' to' , @DTend
SELECT GETDATE(), 'start LOOP'
DO WHILE @DTstart <= @DTend
BEGIN
exec p_pmp_charg_unif_csg @DTstart
WAITFOR DELAY "00:01:00"
SELECT @DTstart = DATEADD(DAY,1,@DTstart )
END
SELECT GETDATE(), 'start LOOP'

I´m having the following problem with SQL 7.
When running the loop, all in a sudden it stops - there´s no
error message on result.
But checking out the current error log, I´ve found the error 17824
reported .

According to the BOL, this error is related to 'priority boost' and
troubles in connection between server and client.

This SQL server 7 has the following configuration:

- 128Mb of RAM, and SQL7 can use from 0 to 128MB
- boost SQL Server priority on Windows NT
- maximum concurrent user connection is 0

Is there someone that had similar problem in SQL 7?

TVMIA

Masa

View 2 Replies View Related

SQL 2012 :: Removing Consecutive Characters From Middle Of A String

Apr 14, 2015

I usually do this through Access so I'm not too familiar with the string functions in SQL. My question is, how do you remove characters from the middle of a string?

Ex:
String value is 10 characters long.
The string value is X000001250.
The end result should look like, X1250.

I've tried mixing/matching multiple string functions with no success. The only solution I have come up with removes ALL of the zeros, including the tailing zero. The goal is to only remove the consecutive zeroes in the middle of the string.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved