LD16 LD16 - 10 months ago 49
SQL Question

SQL Server select query assistance needed

I'm working on building a query but have a difficult time coming up with the right solutions. Here's what my data looks like...

I have about 10,000 records - bills in my


  • Each record has a unique ID number ranging from about 10 - 13000.

  • However my primary key is the
    which in my records is a 1.

  • In case that bill was refunded, another line is added into the tbl but in this case the BillNo is 2.

  • Additionally I have another column called
    which can either be A, B, C

    • when BillNo is 2, AdjType = B

    • but for normal BillNo 1 - the value is NULL.

So it looks like this...


ID BillNo Units TotalPaid AdjType
10 2 17 230 NULL

So this is a normal Bill where BillNO is 1 - this means that it was a PAID Bill.

Additionally you might have BIllNO2 which is most likely a refund that would look like this....

ID BillNo Units TotalPaid AdjType
10 2 -17 -230 B

So here we are negating Units because its a refund and in this case ADJType = B

Both together if I SELECT * from tblBilling where ID=10 I get this....

ID BillNo Units TotalPaid AdjType
10 1 17 230 NULL
10 2 -17 -230 B

Now my main goal is to select all records from
, however I don't want to select any records that have a BillNo 2 associated with them. Basically if a record has BillNO = 1 - that's the record I need. No records that have BillNo=2. Please help!

select *
from tblBilling t1
where t1.AdjType is NULL
  and not exists(select 1 from tblBilling t2
    where t2.id = t1.id and t2.AdjType = 'B')