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
ADVERTISEMENT
Jun 20, 2006
I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList);
13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
View Related
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
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
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
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
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
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
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
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
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
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
Jun 20, 2014
Using MS ACCESS I have:
TRANSFORM Avg(SAP_CALD.[CASE_DAYS]) AS AvgOfCASE_DAYS
SELECT SAP_CALD.[OPN_YEAR], SAP_CALD.[CLD_MONTH], Avg(SAP_CALD.[CASE_DAYS]) AS [Total Of CASE_DAYS]
FROM SAP_CALD
GROUP BY SAP_CALD.[OPN_YEAR], SAP_CALD.[CLD_MONTH]
PIVOT SAP_CALD.[Model];
which works fine. I want to look at the query SAP_CALD and disclude any record in the field 'department' that has a record of any {DPSG, System, Dealer, DealerIT, Inbound}..I can do this by piggybacking a another query that removes these but wondered if it would be easier to exclude them in the crosstab code.
View 1 Replies
View Related
Jun 6, 2007
i have a query:
select ordernumber,amount from orders
where ordernumber is not null
but i am getting some results where, ordernumber is still blank
ORDERNUMBER Amount
11 1200
12 500
400
15 600
how do i make sure that the fields where order number is blank does not comes in the result.
Ashley Rhodes
View 3 Replies
View Related
Mar 6, 2008
I have data that looks like below (columns are Timestamp, Offered, Answered and Delay). I'm looking to exclude returning records that have a value for Delay that are within the top 10% of values of that column. Are there any 2005 tricks where this can be accomplished in a simple statement?
2008-02-18 08:30:002322173
2008-02-18 08:45:002120174
2008-02-18 09:00:002425230
2008-02-18 09:15:002828277
2008-02-18 09:30:002522159
View 3 Replies
View Related
Dec 26, 2007
I am creating a query which will show patients that are enrolled in more than one program, but I need to exclude those patients that enrolled more than once in the same program. Here's part of the code:
SELECT member_id, service_id
FROM pat_prg_info ppi
This query produces results like the following:
member_id service_id
1001 1
1001 2
1003 9
1003 9
1004 2
1004 9
I would like to exclude 1003, since this member_id is enrolled twice in service_id 9. How can I accomplish this?
View 1 Replies
View Related
Oct 25, 2007
Hello,
I have a TSQL (SQL SERVER 2005) code inside a Transaction,
I would like to exclude a piece of that code from the transaction (I do need to recovere it in the trasaction log).
For instance
BEGIN TRAN
SQL Statement 1
SQL Statement 2 -- out of the transaction
SQL Statement 3
COMMIT TRAN
I would like to exlude SQL Statement 2 (SQL statement1 and 3 must be inside the same transaction)
Is it possible? How to do it?
Thank
View 8 Replies
View Related
Mar 20, 2008
Hello,
I have created a procedure with several SQL statement, all are included in a transaction.
Some of the code are "insert" in tables that are in remote server.
for example:
procedure sp1 is on SERVER B.
From SERVERA by linked server we have:
exec serverB.db_B.dbo.sp1 (from serverA the procedure sp1 is called)
CREATE procedures sp1
AS
BEGIN TRANSACTION
insert into T1 (col1) values (1)
insert into serverA.db_A.dbo.T2 (col1) vales ('8vhrfvrf')
insert into T3 (col1) values (3)
COMMIT TRANSACTION
GO
I would like to make that these piece of code (
insert into serverA.db_A.dbo.T2 (col1) vales ('8vhrfvrf')
) is out of transaction.
My question is: Is it possible to exclude piece of code from a transction?
You ask me why this: because SQL server do not allow loopback transaction
Thank
View 7 Replies
View Related
Mar 3, 2008
Hi. I'm creating a new publication for replication. There are a few columns on our main database I do not want published as they contain some confidential information. When I go to create a new publication, I go through the wizard and I select the option to filter rows vertically. I uncheck the columns I don't want published and the wizard completes without any errors.
When I go to start the Snapshot agent to create the database on new server, it gets error saying it cannot find certain columns--the columns I do not want published to begin with.
Am i doing something wrong? I appreciate any help you may provide!
View 7 Replies
View Related
