Exclude if returned value is ZERO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Exclude if returned value is ZERO

Author

Topic

SQLGurl
Starting Member

2 Posts

Posted - 2012-01-27 : 08:56:16

Hi Everyone,

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
SELECT
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_CodeIt 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.

Can you help? I've tried every way that I can think of

many many thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-27 : 09:02:25

Are you using SQL Server? The syntax doesn't seem like T-SQL. If you are using SQL server, add a having clause at the very end like this:


...
GROUP BY
	T.Client_CodeIt
HAVING
	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
	) <> 0

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-27 : 10:41:13

Couple of things...

Your code is malformed..please post the actual code you have run (Is this SQL Server?)

I cleaned it up some here, but the FROM Clause makes no sense


  SELECT 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
    JOIN FX TABLE
      ON ???????????
   WHERE fx.Currency = 'USD'
     AND T.Salesman_Name in ('X','Y','Z')
GROUP BY T.Client_Code

Also, I'm guessing that you want to do this when Transaction Date is equal to today...

Also, you might want to make sure that fx.Exchange_Bid + fx.Exchange_Ask is never 0, or you'll have a divide by zero error

And again...the FROM clause

FROM TABLE T
JOINING FX TABLE

What is that? Are you naming the FX Table alias TABLE?? KOINING?

And you have no JOIN Predicate (ON)

We need your actual Query, The table DDL would help, sample data in DML form and the expected results

Read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources