Implement two filters in one condition - SQL

Question

Hi, I am creating a report. In that I am displaying total no of leads, leads sent via Fax and Leads sent via SMS. I wrote a procedure to fetch the records . Here I have to check an condition that both Leads sent via SMS and Leads sent via Fax should not be zero. If both are zero I should not fetch the record. If one of them has any value i should fetch the record. Is it possible implement it through SQL Query.

FROM OP comment to astander

SELECT  C.ClientID ,
     C.ClientName ,
     C.OrganizationName,
     C.FirstName ,
     S.SMSOverageRate ,
     '' as Cost ,
     Count(*) as TotalLeads ,
     Sum(CASE DeliveryViaFax WHEN 'Y' THEN 1 ELSE 0 END) AS FaxCount ,
     Sum(CASE DeliveryViaSMSEmail WHEN 'Y' THEN 1 ELSE 0 END) AS SMSCount 
FROM    CMN_LeadSaleDetails S INNER JOIN 
     CMN_LeadClients C ON C.ClientID = S.BuyerID 
WHERE   C.SellerTenantId = @TenantId 
AND  S.SellerJournalID = CASE @JournalId WHEN 0 THEN S.SellerJournalID ELSE @JournalId END 
GROUP BY C.ClientID ,
     C.ClientName ,
     C.OrganizationName,
     C.FirstName ,
     S.SMSOverageRate

Answer 1

You can try something like this

SELECT *
FROM TABLE
WHERE (TotalSMSLeads != 0
OR TotalFaxLeads != 0)

Provide the example query and we can assist you further.

OK, from what you commented i would try something like this

SELECT  C.ClientID ,
     C.ClientName ,
     C.OrganizationName,
     C.FirstName ,
     S.SMSOverageRate ,
     '' as Cost ,
     Count(*) as TotalLeads ,
     Sum(CASE DeliveryViaFax WHEN 'Y' THEN 1 ELSE 0 END) AS FaxCount ,
     Sum(CASE DeliveryViaSMSEmail WHEN 'Y' THEN 1 ELSE 0 END) AS SMSCount 
FROM    CMN_LeadSaleDetails S INNER JOIN 
     CMN_LeadClients C ON C.ClientID = S.BuyerID 
WHERE   C.SellerTenantId = @TenantId 
AND  S.SellerJournalID = CASE @JournalId WHEN 0 THEN S.SellerJournalID ELSE @JournalId END 
GROUP BY C.ClientID ,
     C.ClientName ,
     C.OrganizationName,
     C.FirstName ,
     S.SMSOverageRate
HAVING  ( Sum(CASE DeliveryViaFax WHEN 'Y' THEN 1 ELSE 0 END) != 0
     OR Sum(CASE DeliveryViaSMSEmail WHEN 'Y' THEN 1 ELSE 0 END) != 0)

Answer 2

Alternatively..

Select *
From TABLE
Where (SMSLeads + FaxLeads) > 0

Using your SQL

Select C.ClientID ,C.ClientName ,C.OrganizationName 
C.FirstName ,S.SMSOverageRate ,'' as Cost,
Count(*) as TotalLeads ,
Sum(CASE DeliveryViaFax WHEN 'Y' THEN 1 ELSE 0 END) AS FaxCount ,
Sum(CASE DeliveryViaSMSEmail WHEN 'Y' THEN 1 ELSE 0 END) AS SMSCount

FROM CMN_LeadSaleDetails S INNER JOIN CMN_LeadClients C ON C.ClientID = S.BuyerID WHERE C.SellerTenantId = @TenantId AND S.SellerJournalID = CASE @JournalId WHEN 0 THEN S.SellerJournalID ELSE @JournalId Group By C.ClientID ,C.ClientName ,C.OrganizationName C.FirstName ,S.SMSOverageRate Having (FaxCount+SMSCount) > 0