CODEWITHSUNDEEP
sql-serversql-server-2008
Vishal Suthar
Vishal Suthar

Reputation: 17193

Show only non-zero rows count

Table Structures:
tblCustomer

Customer_id  created                 field1            field2        cardno       field14
------------------------------------------------------------------------------------------------    
1014         2010-05-25 12:51:59.547 Cell Phone        [email protected]   1234567890   Test Card
1015         2010-08-15 12:51:59.547 Email             [email protected]   2345678891   NULL

tbl_TransactionDishout

Trnx_id   offerNo   TerminalID      Created                  VirtualCard
-------------------------------------------------------------------
1         1014      170924690436418 2010-05-25 12:51:59.547  1234567890

Relation between tbl_transaction and tblCustomer is having same cardno.

Existing query 

SELECT 
convert(varchar, CAST(t2.created AS DATETIME), 111) created, 
COUNT(CASE WHEN t2.DishoutResponseCode = '0000' and t1.field14 <> 'Test card' THEN 1      END) as  Deals_Redeemed,
COUNT(CASE WHEN t2.DishoutResponseCode <> '0000' and t1.field14 <> 'Test card' THEN 1   END) as Non_Deals 
FROM tblCustomer AS t1, tbl_TransactionDishout t2 
where t1.cardno = t2.VirtualCard
and convert(varchar, CAST(t2.created AS DATETIME), 111) >= (select        MAX(convert(varchar, a.Created, 111)) 
from tbl_Offer as a,tbl_TransactionDishout as b where cast(a.OFID as varchar) =     b.OfferNo and a.Live = '1')
GROUP BY convert(varchar, CAST(t2.created AS DATETIME), 111) 
ORDER BY convert(varchar, CAST(t2.created AS DATETIME), 111) DESC       



created     Deals_Redeemed  Non_Deals    
-------------------------------------
2012/03/02  0               0
2012/03/01  0               1
2012/02/28  2               0
2012/02/27  0               0

Now I want the rows with non-zero counts..

Upvotes: 0

Views: 682

Answers (1)

JNK
JNK

Reputation: 65147

Use a HAVING clause.

Just after your GROUP BY put:

HAVING  COUNT(CASE WHEN t2.DishoutResponseCode = '0000' and t1.field14 <> 'Test card' THEN 1      END) > 0
AND COUNT(CASE WHEN t2.DishoutResponseCode <> '0000' and t1.field14 <> 'Test card' THEN 1   END) > 0

Upvotes: 2

Related Questions