Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    3

    Red face Unanswered: Exclude if returned value is 0

    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

    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.

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

    many many thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Use the HAVING clause.
    Code:
    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_Code
    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

  3. #3
    Join Date
    Jan 2012
    Posts
    3
    brilliant!!!!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •