SQL Query: I need both rows if values exist

I have an accounting situation where we’re both paid (policy Transaction Type NBS) and negative (policy Transaction Type cancellation or XLN) on the same accounting situation. I need rows that only have both Tran Type of ‘NBS’ and ‘XLN’ for the same customer.

Example Data:

  • How to change the data type of a column without dropping the column with query?
  • How to connect to SQL Server database from JavaScript in the browser?
  • Possible to get SqlTransaction from ambient Transaction?
  • Generate Backup Of Table Through SQL Query
  • Dapper.NET and stored proc with multiple result sets
  • multiple transactions within a certain time period, limited by date range
  •     Client     PolicyNo  Revenue   TranType
       -------------------------------------------
        John Doe   Pol1234   1000.00     NBS
        John Doe   Pol1234  -1000.00     XLN
        Jane Loe   Pol5675   9000.00     NBS
        Jane Loe   Pol5676   8000.00     NBS
        Bill Foe   Pol0985   1000.00     NBS
        Bill Foe   Pol0987  -1000.00     XLN
    

    The query needs to only return the first two rows as both Client and PolicyNo match but also because each one is NBS and XLN. If both lines for the same customer are NBS or they are both XLN it needs to be excluded. If the PolicyNo does not match it needs to be excluded.

    Right now, my query returns the full set. (I omitted some columns from my example above for simplicity.

    SELECT 
    Company_1.Name, 
    isnull(Customer.FirmNameCust, 
    isnull(Customer.FirstName + ' ', '') +  isnull(Customer.LastName, '')) AS Client,
    BasicPolInfo.PolNo,                            
    DirectBillEntryHeader.StmtDate, 
    irectBillEntryHeader.Description, 
    DirectBillEntryDetail.*
    FROM DirectBillEntryHeader 
    INNER JOIN 
    DirectBillEntryDetail 
    ON DirectBillEntryHeader.DBEHId = DirectBillEntryDetail.DBEHId 
    INNER JOIN BasicPolInfo 
    ON DirectBillEntryDetail.PolId = BasicPolInfo.PolId 
    INNER JOIN Customer 
    ON DirectBillEntryDetail.CustId = Customer.CustId 
    AND BasicPolInfo.CustId = Customer.CustId 
    INNER JOIN Company 
    ON DirectBillEntryDetail.WritingCoCode = Company.CoCode 
    INNER JOIN Employee 
    ON Customer.Prod1Code = Employee.EmpCode 
    INNER JOIN Company Company_1 
    ON Company.ParentCoCode = Company_1.CoCode
           WHERE (DirectBillEntryDetail.TranType = 'NBS') 
           AND (DirectBillEntryHeader.StmtDate >= DateAdd(DD, - 30, GETDATE())) 
           OR (DirectBillEntryDetail.TranType = 'XLN')
           AND (DirectBillEntryHeader.StmtDate >= DateAdd(DD, - 30, GETDATE()))
    

    2 Solutions collect form web for “SQL Query: I need both rows if values exist”

    You could use EXISTS:

    SELECT t1.*
    FROM dbo.TableName t1
    WHERE t1.TranType IN('NBS','XLN')
    AND EXISTS
    (
        SELECT 1 FROM dbo.TableName t2
        WHERE t1.Client = t2.Client
          AND t1.PolicyNo = t2.PolicyNo
          AND t1.TranType <> t2.TranType
          AND t2.TranType IN('NBS','XLN')   
     )
    

    Find all client/policies that have both types by use of GROUP BY and HAVING. Then use this in your from clause so you get only those client/policies.

    select ...
    from
    (
        select client, policyno 
        from mytable
        where trantype in ('NBS','XLN')
        group by client, policyno 
        having count(distinct trantype) = 2
    ) wanted
    inner join sometable on sometable.client = wanted.client and sometable.policyno = wanted.policyno
    ...
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.