Geek Answers Handbook

SQL Statement to exclude rows that match certain criteria

I have a table of financial fees that has the following data:

tx_id    tdl_id     tx_num     detail_type_name     charges     payor_id    orig_payor
73765    737650000  1          CHARGE               320.00      1019        MEDI_CAL
7395     739500000  1          CHARGE               320.00      1253        THIPA/TORRANCE
7444     744400000  1          CHARGE               350.00      NULL        NULL
7444     744410000  1          VOID                 -350.00     NULL        NULL
7498     749800000  1          CHARGE               225.00      1011        UNITED HEALTH
750023   750023000  1          CHARGE               225.00      NULL        NULL
750023   750023100  1          VOID                 -225.00     NULL        NULL

I need a query that excludes VOIDs and their corresponding CHARGES, in other words, an sql statement that returns these records:

tx_id    tdl_id     tx_num     detail_type_name     charges     payor_id    orig_payor
73765    737650000  1          CHARGE               320.00      1019        MEDI_CAL
7395     739500000  1          CHARGE               320.00      1253        THIPA/TORRANCE
7498     749800000  1          CHARGE               225.00      1011        UNITED HEALTH

Excluding these entries:

tx_id    tdl_id     tx_num     detail_type_name     charges     payor_id    orig_payor
7444     744400000  1          CHARGE               350.00      NULL        NULL
7444     744410000  1          VOID                 -350.00     NULL        NULL
750023   750023000  1          CHARGE               225.00      NULL        NULL
750023   750023100  1          VOID                 -225.00     NULL        NULL

At first I tried to compare tdl_id with an operator like:

 SELECT * FROM tablename
 WHERE LEFT(TDL_ID,4) IN (SELECT LEFT(TDL_ID,4)
               FROM tablename
               GROUP BY LEFT(TDL_ID,4)
               HAVING COUNT(*) < 2)

But this was not reflected, because some tdl_ids have 4 identical numbers, some 5, some 6 and some 7.

, 2 "detail_type_name" "charge", "CHARGES" ( ), "", "VOID". , . INNER JOINS ( http://bit.ly/1a70mc2), . !

UPDATE: tx_id, payor_id, orig_payor NULL, NULL.

+4
2

NOT EXISTS , VOID tx_id ?

SELECT * FROM tablename O
WHERE detail_type_name = 'CHARGE'
    AND NOT EXISTS(
        SELECT TOP 1 1 FROM tablename I 
        WHERE I.detail_type_name = 'VOID' 
              AND I.tx_id = O.tx_id
    )
+2

, :

select tx_id, sum(Charges) from tablename 
group by tx_id
having sum(charges)>0

, , , , , , ,

0

More articles:


All Articles