trying to exclude records 1

[shirley35]

I have 3 tables.
One is a visit table. Visits have charges connected with them.
The second is a charges table and one is a transactions table. Transactions are always connected to charges.
The transactions table has payments and adjustments in two separate fields, in separate records.
There can be an adjustment and a payment for any one charge. (charges are positive figures and payments and adjustments can be positive or negative figures depending on whether they are being reversed or not)

I want to exclude any transactions on a charge if there is a payment on it. And I want to include adjustments greater than zero if there are no payments or a payment of zero.

I tried joining them all together and putting adjustments > 0 and payments < 0 but I get no records returned because they are on separate line items (records).

So then I tried a union query but it treats them separately and I can eliminate payments but still get the adjustments on the charge.

example:
Visit 1
Charge 100.00, adjustment 50.00, payment -40.00
(I don't want to see any of those because there is a payment)

Visit 2
Charge 150.00, adjustment 55.00, payment 0.00
I want to see this one listed because there is a zero payment.

Visit 3
Charge 125.00, adjustment 10.00
I want to see this one because there is no payment

SELECT visit.enc_nbr,
charges.service_item_id,
charges.amt,
trans_detail.paid_amt,
trans_detail.adj_amt,
payer_mstr.payer_name,
payer_mstr.payer_id,
tran_code_mstr.type,
transactions.trans_id,
tran_code_mstr.tran_code_id,
charges.charge_id,
transactions.type

FROM

NGProd.dbo.transactions transactions
INNER JOIN NGProd.dbo.visit visit ON transactions.source_id=visit.enc_id
INNER JOIN NGProd.dbo.tran_code_mstr tran_code_mstr ON transactions.tran_code_id=tran_code_mstr.tran_code_id
INNER JOIN NGProd.dbo.trans_detail trans_detail ON transactions.trans_id=trans_detail.trans_id
LEFT OUTER JOIN NGProd.dbo.person_payer person_payer ON visit.cob1_person_payer_id=person_payer.person_payer_id
INNER JOIN NGProd.dbo.payer_mstr payer_mstr ON person_payer.payer_id=payer_mstr.payer_id
LEFT OUTER JOIN NGProd.dbo.charges charges ON trans_detail.charge_id=charges.charge_id

WHERE payer_mstr.payer_id='1139ECCF-2662-4139-89C7-2AA65789032C'
AND trans_detail.adj_amt>0
AND tran_code_mstr.tran_code_id<>'7F50E9A1-5B3D-4BDB-8D53-67490629AFC8'

Thank you...

 

[gmmastros]

It's hard to visualize your table structure, so the following query may not do what you want. It's worth a try though.

SELECT visit.enc_nbr,
       charges.service_item_id,
       charges.amt,
       trans_detail.paid_amt,
       trans_detail.adj_amt,
       payer_mstr.payer_name,
       payer_mstr.payer_id,
       tran_code_mstr.type,
       transactions.trans_id,
       tran_code_mstr.tran_code_id,
       charges.charge_id,
       transactions.type
 
FROM   NGProd.dbo.transactions transactions
       INNER JOIN NGProd.dbo.visit visit 
         ON transactions.source_id=visit.enc_id
       INNER JOIN NGProd.dbo.tran_code_mstr tran_code_mstr 
         ON transactions.tran_code_id=tran_code_mstr.tran_code_id
       INNER JOIN NGProd.dbo.trans_detail trans_detail 
         ON transactions.trans_id=trans_detail.trans_id
       LEFT OUTER JOIN NGProd.dbo.person_payer person_payer 
         ON visit.cob1_person_payer_id=person_payer.person_payer_id
       INNER JOIN NGProd.dbo.payer_mstr payer_mstr 
         ON person_payer.payer_id=payer_mstr.payer_id
       LEFT OUTER JOIN NGProd.dbo.charges charges 
         ON trans_detail.charge_id=charges.charge_id
[red]       INNER JOIN (
           Select Charge_Id
           From   Trans_Detail
           Group By Charge_ID
           Having Count(Case When Payments < 0 Then 1 End) = 0
           ) as PaymentsMade
           On charges.charge_id = PayementsMade.Charge_Id
[/red]
WHERE  payer_mstr.payer_id='1139ECCF-2662-4139-89C7-2AA65789032C'
       AND trans_detail.adj_amt>0
       AND tran_code_mstr.tran_code_id<>'7F50E9A1-5B3D-4BDB-8D53-67490629AFC8'

The real "trick" is here.

Select Charge_Id
From Trans_Detail
Group By Charge_ID
Having Count(Case When Payments < 0 Then 1 End) = 0

Basically we count the number of payments that are less than zero (for each charge_id). If the payment count = 0, then the charge_id is returned, otherwise it isn't.

Then notice how this is inner joined to your existing query. Basically, this little query (considered a derived table) will only return charges that we care about. By inner joining this to the full query, we are essentially filtering out charges that are not in the list returned by the derived table.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[shirley35]

wow - I don't know how you made sense of what I said - but that is what I meant! I didn't know how to stop it from filtering out the charge given that criteria.

Now it is not including charges where there has been a payment which is what I wanted.

Next I want to filter out those that have a particular adjustment code and then I will only want the ones where the adjustments are greater than zero.

Will I need to add two more inner joins beneath the one you showed me? It kind of makes sense how you explained it but I still have to wrap my head around doing grouping, etc.

For the adj greater than zero, I would think I could copy the inner join and switch out payments for adjustments and reverse the sign and for the join - to keep getting a smaller subset do I join to PaymentsMade or back to Charges?

And then for the transaction code I'm afraid I'll have to join a couple of tables within that inner join - I hope that is allowed. The transaction code isn't in the trans_detail, it is in the transactions table.

Thanks - I am wondering how you learned to do this.

 

[gmmastros]

I will only want the ones where the adjustments are greater than zero.

....

For the adj greater than zero, I would think I could copy the inner join and switch out payments for adjustments and reverse the sign and for the join - to keep getting a smaller subset do I join to PaymentsMade or back to Charges?

That would probably work, and I encourage you to try it. Not necessarily because it's the best way to do it, but because doing so is a great learning experience. You can join to either one. Since this is an inner join, it doesn't really matter how the list is filtered, just that it is. If it were me, I would probably join to the charges table.

I am wondering how you learned to do this.

I learned to do this by "hanging out" in forums like this one. I read almost every thread (and participate in many of them).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[shirley35]

Ok, thank you. I added one inner join removing the charges associated with the adjustment code we didn't want and turns out that satisfied all the criteria.
I appreciate the help.