LD16 LD16 - 5 months ago 19
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

tblBilling
.


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

  • However my primary key is the
    BillNo
    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
    AdjType
    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...

tblBilling

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
tblBilling
, 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!

Answer
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')