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