How To Exclude Data From An Average?

Apr 8, 2008

I am selecting the following fields

AVG_Back
AVG_Yield

I want to select both fields, like this

Select AVG(AVG_Back), AVG(AVG_Yield)
FROM tblUser
WHERE Date Between '3/1/2008' AND '3/31/2008'

I want to limit the AVG_Back field to exclude all values of 0. So only average AVG_Back if the value > 0. What is the best way to accomplish this? I can't just put it in the where clause or the AVG_Yield will be excluded too.

View 5 Replies


ADVERTISEMENT

How To Exclude Null Data?

Apr 28, 2006

I'm new to MS SQL server. Is there a select statement that will include fields that are not null?

View 2 Replies View Related

Exclude Data From Query

Nov 24, 2014

I have a column that is VARCHAR(30) this column is supposed to contain values that "look" like a date in the form mm/dd/yyyy - however because it is a free-text character field often times data is entered other then a date -- "text" -

How do I return only the data that is in the format of mm/dd/yyyy

View 3 Replies View Related

How Do I Exclude Data From One Table Based On Data From Another?

Feb 28, 2008

I have a table called MasterSkillList which is a list of skills and attributes, eg: [Appraise, INT], [Bluff, CHA] etc
I have a table called Classes, which is a list of all classes available (and some details which are irrelevant), eg: [Fighter], [Assassin] etc.
I also have a table called ClassSkills which holds a list of classes and their applicable skills, eg: [Assassin, Bluff], [Assassin, Open Lock], [Fighter, Appraise], [Fighter, Bluff] etc.
 What I have is a gridview which shows all my classes from the class table. i want to be able to select a class on that gridview and create a checkbox list of all available skills that are NOT allready associated with that class. Eg: assassin has bluff and open lock, so those two skills shouldn't appear on my checkbox list. So i want to show all the skill from the master skills list, excluding all the skills the selected class allready has.
 Alternatively, It would be better if there was a way to display all the skills in existance on my checkboxlist and the ones that class allready has to be checked. Any suggestions?
Here's the query I have: 
SELECT     MasterSkillsList.SkillFROM         ClassSkills INNER JOIN                      MasterSkillsList ON ClassSkills.Skill = MasterSkillsList.SkillWHERE     (MasterSkillsList.Skill <> ClassSkill.Skill)
 Edit:
I just added the following sql query, but when i run it i get no results even though it should show everything except 2 skills. Have I written it wrong?
SELECT     SkillFROM         MasterSkillsListWHERE     (NOT EXISTS                          (SELECT     Skill                            FROM          ClassSkills                            WHERE      (ClassName = @ClassName)))

View 3 Replies View Related

How Do You Exclude Data That Doesn't Exist?

Apr 1, 2005

I have a cube that is showing measures that don't exist. Let me give an example. This example will include 3 dimensions, product, location, and time. The fact table measure will be sales.

Here are the distinct values if you were to write a sql query against the dimensionl model that feeds the cube.

Product Location Time Sales
A X 1/04 200
B Y 1/04 100

A X 2/04 300

In the cube, if you were to look at product by location for just 2/04, you would see:

Product Location Sales
All Loc 300
A X 300
Y

All Loc
B X
Y

How do you get rid of the zero's or combinations that don't exist?

Thanks,

Doug

View 1 Replies View Related

Exclude Data From Partial String In Predicate

May 8, 2015

I have to write one query to exclude data from partial string in the predicate.

Select * from table where col NOT in ('%abc%,%xyz%).

So the data where col value doesn't contain the ABC and xyz string value only should be selected. I am also getting xyz data in the result.

View 4 Replies View Related

How Do I Exclude Certain Unwanted Data From A Query Result

Oct 12, 2007

Hi All,

We've got a basic query that pulls a list of some parts out of our inventory database:

SELECT p21_view_inv_mast.item_id, p21_view_inv_mast.item_desc, p21_view_inv_loc.gl_account_no
FROM dbo.p21_view_inv_loc p21_view_inv_loc, dbo.p21_view_inv_mast p21_view_inv_mast
WHERE p21_view_inv_loc.inv_mast_uid = p21_view_inv_mast.inv_mast_uid

This returns a lot of info over three columns: "item_id" "item_desc" and "gl_account_no"

What I want to do is remove any entries where the "gl_account_no" shows as 011500000

I am by no means a TSQL person, I mostly run the canned queries that our vendor gives us so any help with this would be greatly appreciated.

View 4 Replies View Related

Power Pivot :: Comparing YTD Data To Average Of Data In Last 6 Months

Jun 9, 2015

I am trying to build various reports that compares data over time. I have one that measures Year Over Year % difference for number of incoming projects. I managed to do that easily by calculating the following

YTDProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(SUM('TrendData '[Projects]),DATESYTD(CalendarDate[FullDate])))
PYProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(sum('TrendData '[Projects]),SAMEPERIODLASTYEAR(DATESYTD(CalendarDate[FullDate]))))
YoYDifference:=[YTDProjects]-[PYProjects]
YoYPercProjects:=IF([PYProjects]=0, BLANK(), [YoYDifference]/[PYProjects])

Where Projects is the metric in question, TrendData is the table that contains project data and CalendateDate is the Date Table. But now I am trying to compare the same YTD projects data to number of projects that came in the last 6 months. How do our projects compare to average number of projects that came in last 6 month period.

I tried the the DATEADD function instead but got no luck and data came out wrong!

PrevProjects:=CALCULATE(SUM([Projects]),DATEADD(CalendarDate[FullDate],-1,QUARTER))

For some reason, this also returns blank in my model:

QTDProjects:=TOTALQTD(SUM('TrendData'[Projects]),CalendarDate[FullDate])

View 2 Replies View Related

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

Create Query To Average Data & Total/sum Data

Apr 21, 2008

Hello,
I am very new to SQL and just getting to learn this stuff. To make this question easier I will scale down the fields dramatically.

I have about 8000 records close to 2000 records for the last 4 years
and I would like to create a query that will create a table on my SQL server. I need to bind the data based on two items the Year and the Name and average several records. However, one record needs it's own calculation.

Here are my field names:
[year] ***4 choices 2007, 2006, 2005, 2004***
[name]
[rush_no] ***integer***
[rush_net] ***integer***
[YPC] *** This field needs to be calculated by [rush_net] divided by [rush_no]***decimal***

I also need to create the same table that will "total/sum" the same records.

View 7 Replies View Related

Reporting Services :: Report Builder - Set Parameter To Include Or Exclude Columns From Data Presentation

Oct 2, 2015

Based on a table like below I have created a report so that I can compare number of items in the main warehouse (LOCATION1) and the outlets (LOCATION2 and LOCATION3).

___________________________________
| ID | PRODUCT_INDEX | LOCATION   | VALUE |
___________________________________
| 1  | INDEX1             | LOCATION1 | 1         |
___________________________________
| 2  | INDEX1             | LOCATION2 | 1         |
___________________________________
| 3  | INDEX1             | LOCATION3 | 0         |
___________________________________
| 4  | INDEX2             | LOCATION1 | 0         |
___________________________________
| 5  | INDEX2             | LOCATION2 | 0         |
___________________________________
| 6  | INDEX2             | LOCATION3 | 1         |
___________________________________
| 7  | INDEX3             | LOCATION1 | 1         |
___________________________________
| 8  | INDEX3             | LOCATION2 | 0         |
___________________________________
| 9  | INDEX3             | LOCATION3 | 1         |
___________________________________

The way I present data in my Report is as such. I want to show items that are available in the warehouse that should be moved to the outlets.

select 
 a.PRODUCT_INDEX
, a.LOCATION1(VALUE)
, b.LOCATION2(VALUE)
, c.LOCATION3(VALUE)  
from 

[Code] .....

__________________________________________________________________
| PRODUCT_INDEX | LOCATION1 (VALUE) | LOCATION2 (VALUE) | LOCATION3 (VALUE)|
__________________________________________________________________
| INDEX1               | 0                              | 1                               | 0                             |
__________________________________________________________________
| INDEX2               | 1                              | 0                               | 1                             |
__________________________________________________________________
| INDEX3               | 1                              | 0                               | 1                             |
__________________________________________________________________

I have added some parameters in my report to filter out products that are not available in warehouse (LOCATION1) and this works great.

select * from VIEW where 'LOCATION1(VALUE)' > 0 and ('LOCATION2(VALUE)' = 0 or 'LOCATION3(VALUE)' = 0)

__________________________________________________________________
| PRODUCT_INDEX | LOCATION1 (VALUE) | LOCATION2 (VALUE) | LOCATION3 (VALUE)|
__________________________________________________________________
| INDEX1               | 1                              | 1                               | 0                             |
__________________________________________________________________
| INDEX3               | 1                              | 0                               | 1                             |
__________________________________________________________________

Now the issue starts when I add a parameter to my report for user to choose which outlets (LOCATIONs) he wants in the equation. I know how to make a column disappear based on parameter value but how to take it out of equation? At the moment when user selects only LOCATION2 and not LOCATION3 then data is not filtered correctly:

__________________________________________________
| JOIN_ON_VALUES | LOCATION1 (VALUE) | LOCATION2 (VALUE) |
__________________________________________________
| INDEX1                 | 1                              | 1          |
__________________________________________________
| INDEX3                 | 1                              | 0          |
__________________________________________________

Ideally I would like a user to select random outlets (warehouse would be static on the report) and compare one or multiple and only show records that are 0 in the outlets.

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

The Sum Or Average Aggregate Operation Cannot Take A Nvarchar Data Type As An Argument.

Feb 6, 2007

how to solve this problem ....
I want to select te sum of nvarchar column and this error always happen
 
what is the solution this is my query
 
"SELECT Datein AS Date,SUM(Durm) AS DurationMin, COUNT (*) AS 'Number of Hits' FROM Rayyan_Log WHERE (DNIS = '"+DropDownList1.SelectedItem.Text +"') AND (Datein between '"+TextBox2.Text+"' and '"+TextBox3.Text+"') GROUP BY Datein ORDER BY Datein"

View 2 Replies View Related

Exclude Rows

Feb 16, 2004

Table 1 is just a reference table. Users add values to table 2.

I need to select/exclude records from table1 where the id2 in table2 = 1.

How get the following results:


table 1
----------
id
----------
a
b
c
d
e
f
g


table 2
----------
id / id2
----------
b / 1
c / 1
d / 1
f / 1
c / 2
d / 2
a / 4
b / 4


need results
----------
id
----------
a
e
g


any suggestions?


thanks

View 1 Replies View Related

Exclude Condition

Jul 26, 2005

i have two tables: "Person" and "Year". "Person" can have many "Year"
(one to many relation). i want a query which returns all the records
from "Person" where "Year" is 2005 but exclude if there is any "Year"
with 2004. how can i write that query? any help will be appreciated.
i did try
<code>
SELECT * FROM Person JOIN Year ON Person.Id = Year.PersonID WHERE Year.Year = 2005 AND Year.Year <> 2004
</code>
but it doesn't seem to work. i want this query to return records from
Person where there is no any year with 2004 but only 2005. If a person
has both 2004 and 2005 exclude that person.

View 1 Replies View Related

MDX - Exclude Condition

Dec 15, 2005

I have to built a query to get the % for all the Region (Americas, Asia and Europe) from a cube.

But in these regions some countries are excluded and treated seperate.

Like Asia does not include India and Japan.

How do I get the ASIA query using an EXCLUDE condition.

Please help.

View 1 Replies View Related

Exclude If Returned Value Is 0

Jan 27, 2012

I am running a query that works just fine however, I would like it to exclude value that are equal to zero.

Basically my query looks at the commission that clients pay over a number of periods.

So it goes like this

T.Client_Code as Client
,SUM(CASE t.Transaction_Date WHEN DATEADD(day, DATEDIFF(day, 1, GETDATE()),0)THEN (ABS (t.transaction_commission) /((fx.Exchange_Bid + fx.Exchange_Ask)/2 )) ELSE 0 END)as Commission_Day

FROM TABLE T
JOINING FX TABLE

WHERE
fx.Currency = 'USD'

And T.Salesman_Name in ('X''Y'Z)

Group BY
T.Client_Code

It works perfectly fine however, we dont transact with our clients everyday so therefore this list will return all of our clients in the database and many will have generate zero commission. I want to keep the query along those lines I just need to insert something that says "ONLY SHOW WHEN Commission is not ZERO.

View 2 Replies View Related

Exclude Status Appear More Than Once In Same ID

Jul 16, 2013

How can i exclude those with more than one status? i tried using rows but it seems doesn't work.

ID status
1 new
1 new
1 close
2 new
2 close
3 close
3 close
4 new
4 close

the result should appear as:

ID status
2 new
2 close
4 new
4 close

View 4 Replies View Related

Exclude Columns

May 13, 2006

Hi

i wanna exclude some columns from my select statement. i do NOT wanna columns starting with 1,2,3,4,5 and I. How can i do it?

thanks

View 5 Replies View Related

Exclude From Query

Jan 9, 2008

Hi. hope someone can help me with this thing.

I have a list of numbers that I get from a query to my database. how could I make the query so that I can exclude a couple of numbers. For example the numbers 1234 and 8888. My list is in the range of 1 - 99999.

Thank you
Mr. Newbie

View 1 Replies View Related

Exclude Query

Jul 23, 2005

I have 16,000 rows in tblClient and 3000 rows in NewTable.SELECT tblClient.*FROM tblClient INNERJOIN [New Table] ON tblClient.NoDossier <> [New Table].NoDossierif I use = (equal) instead of <> (exclude), the query returns 3000 rowswhen I use <> it returns 160000 rows,if I try group by, the query bugswhat is my problem

View 4 Replies View Related

Exclude Row If A Particular Field Is Null

Jun 12, 2008

Is there any way of excluding an entire row if a particular field contains a null value? Even if other fields in the row aren't null...  This is in SQL Server 2005 

View 2 Replies View Related

How To Exclude Zero Values When Sorting?

Feb 9, 2005

I have a datagrid with a “sort� field I want to use to sort the rows in ascending order. However, I want values with a 0 or NULL value to be displayed last. I can’t figure out how to do a sort (preferably in the SQL) that returns the empty values last. Is this possible?

View 7 Replies View Related

How Do I Exclude Null Fields?

Apr 28, 2006

Is there a way to write a select statement that will pull only fields that are not null?

View 2 Replies View Related

Exclude Rows In SQL Statement

Oct 31, 2001

I have a table (tblAction) that contains customer account numbers (Account)
and actions taken on the account in a given day (AcctCode). So each account
can have multiple actions taken on it (one row for each action) in a day.

I have a request to present a result set that displays all action 52, 53,54.
But if a given account number has at least one action 28, then they want me to exclude all the rows for that account number from the result set. Can someone help with this?

View 1 Replies View Related

Exclude Row If Calculated Column Is Zero?

Oct 3, 2013

I am attempting to create an SQL statement that will query a file and give me amount totals by company number/customer number. The totals have to be combined into 4 groups (1/2/3/4) for each amount total in company number/customer number combination. In effect it will look something like this:

COMPANY | CUSTOMER | SORT | AMOUNT
==================================
00001 | 11111 | 1 | $55
00001 | 11111 | 2 | $12
00001 | 11111 | 3 | $19
00001 | 11111 | 4 | $ 0
00001 | 22222 | 1 | $99
00001 | 22222 | 2 | $53
...and so on.

I HAVE THIS PART WORKING ALREADY. The problem is that I am trying to exclude the rows that have 0 (zero) in the amount column from showing up in the output. The amount is a calculated field of all the invoice for that company number/customer number combination for that sort (eg: Company 00001/Customer 11111/Sort 1 has $55 associated to it). I cannot use the calculated field in my where clause.

I will include a simplified version of my select statement so you can see how I got as far as I have and where to go so I pretty much say "WHERE SUM(SubTBL.Amount) <> 0".

----SELECT STATEMENT-----
SELECT
MainTBL.Cust#,
SUM(SubTBL.Amount) As TotAmt,
CASE
WHEN (days (currdate) - days (MainTBL.DateFLD)) <= 30 THEN '1'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 31 AND 60 THEN '2'
WHEN (days (currdate) - days (MainTBL.DateFLD)) BETWEEN 61 AND 90 THEN '3'
WHEN (days (currdate) - days (MainTBL.DateFLD))> 90 THEN '4'

[code]....

View 1 Replies View Related

How To Exclude Value When Account Have Different Values

Oct 24, 2013

exclude values when Account have different values (rooms both A and B), i.e

Account Room
10122 A
10122 B
10130 B
10131 C

View 17 Replies View Related

Exclude Duplicates From Result Set

Apr 2, 2008

This should be trivial but I'm ignorant so I'm hoping someone can assist. I can find lots of code snippets for removing duplicates, but I can't find a variation that works for my case.

Overview: I'm doing a name look-up, combining first & last names and if it matches against an employee table, getting the employeeID for that person. I need to only return the unique matches, and exclude any names that happen more than once in the employee table.

I have 3 possible results from my select (snippet below):
1) Single match
2) No match
3) Multiple matches (2 or more people, same first and last name)

My simple code below does exactly what I need for case 1 & 2. If there is a single match on the name, it's returns the res_ID and emp_ID as expected, if no match, no record and that works for me.

The problem I can't solve is if there are two John Smith employees, both records are returned, which is what my query requests, but not what I need. I want ONLY return data which has ONE exclusive match, and exclude all others.

Code for case 1 & 2, doesn't handle 3rd case:

Select distinct ot.res_ID, e.emp_ID
From employee e, @OutputTable ot
Where (e.fName + ' ' + e.lName) = ot.empFullNameText

Employee table has first & list names, plus emp_ID
@OutputTable is a table variable from my proc which has "John Smith" type text names as one string.


This has to be simple, but I'm over my head on this one. All ideas, reference links or other assistance appreciated.

View 10 Replies View Related

Exclude Duplicate Records

Jan 10, 2007

Hello

I'm developing my fist Integration Service and I have this operations:

Reading from a XML
Check for duplicate records and discard them
Insert the result into the database

The XML I don't control and could came with duplicate records that I have to discard. How can I find them?

I want to find the duplicates in the XML and not in database.



tkx for the help
Paulo Aboim Pinto
Odivelas - Portugal

View 14 Replies View Related

Select Form Table And Exclude Another

Mar 18, 2005

Hello,

I've a query wich with I select buildings form a table.
I've a second table with a few building I don't want in my select (exclude form select).
How can I make this?

Thx a lot.
Dom

PS: I'm a sql newbie...

View 2 Replies View Related

Query To Exclude Named Logins

Nov 22, 2011

I have named logins and generic logins. For example, somebody and test. I need to write a query to exclude named logins. Not sure how to do it.

View 3 Replies View Related

SQL 2012 :: Search IP Ranges And Exclude

Jun 1, 2015

I need to remove some IP addresses from a total list of IP addresses. The ones I need to remove are in certain IP ranges. I could create a temp table with all these IP addresses I need to exclude.

Example IP Ranges:

128.134.2.100 to 128.134.2.200

135.234.12.60 to 135.234.12.120

210.10.140.150 to 210.10.140.200

View 7 Replies View Related

Exclude All ID Which Doesn't Purchase Any Code

Aug 30, 2013

how can i exclude all id which doesn't purchase any code.

codeA,codeB,codeC,codeD,.....
ID 1 purchase codeA, this will not be pulled out.
ID 2 didnt purchase any code, this will be pulled out.

I tried select id,code from tableA where code not in ('codeA','codeB','codeC','codeD',...)

but it still pull out ID 1.

View 1 Replies View Related







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