1

I have some data - grouped by col LineNum - What I need to be able to do - is to remove all rows that off set each other with the negative and positive totalOrg values - so in any event that a row has 2 positive and one negative - remove both the positive and negative (totalOrg) row (where all other values beyond claimDetailID are equal) and leave the remaining positive...

DROP TABLE #table
CREATE TABLE #table (linenum int,   HCPCSOrg varchar(10),   reimbOrg money, unitsOrg           int, totalorg money, claimdetailID int,  comments varchar(500))
INSERT INTO #table
SELECT 1,   '84443',    22.93,  1,  -82.00  ,1072766,   'Status: N - No Other Lab Codes         In Claim - Reimb ClinLab'
UNION ALL
SELECT 1,   '84443',    22.93,  1,  82.00,  1072767,    'Status: N - No Other Lab Codes In Claim - Reimb ClinLab'
UNION ALL
SELECT 1,   '84443',    22.93,  1,  82.00,  1072768,    'Status: N - No Other Lab Codes     In Claim - Reimb ClinLab'
UNION ALL
SELECT 2,   '36415',    3.00,   1,  -15.00, 1072769,    'Status: N - No Other Lab Codes In Claim - Reimb ClinLab'
UNION ALL
SELECT 2,   '36415',    3.00,   1,  15.00,  1072770,    'Status: N - No Other Lab Codes In Claim - Reimb ClinLab'
UNION ALL
SELECT 2,   '36415',    3.00,   1,  15.00,  1072771,    'Status: N - No Other Lab Codes In Claim - Reimb ClinLab'
UNION ALL
SELECT 3,   '87621',    47.87,  1,  227.00, 1072772,    'Status: N - No Other Lab Codes In Claim - Reimb ClinLab'
UNION ALL
SELECT 3,   '85025',    10.61,  1,  40.00,  1072773,    'Status: N - No Other Lab Codes In Claim - Reimb ClinLab'
UNION ALL
SELECT 3,   '85025',    10.61,  1,  -40.00, 1072774,    'Status: N - No Other Lab Codes In     Claim - Reimb ClinLab'
UNION ALL
SELECT 4,   'G0123',    27.64,  1,  -74.00, 1072775,    'Status: A - Found in ClinLab'
UNION ALL
SELECT 4,   'G0123',    27.64,  1,  74.00,  1072776,    'Status: A - Found in ClinLab'
UNION ALL
SELECT 4,   '85025',    10.61,  1,  40.00,  1072777,    'Status: N - No Other Lab Codes     In Claim - Reimb ClinLab'
UNION ALL
SELECT 5,   'G0123',    27.64,  1,  74.00,  1072778,    'Status: A - Found in ClinLab'


SELECT * FROM #table
user3833239
  • 49
  • 2

1 Answers1

2

Try this:

delete from [table]
from
(
  select p.claimdetailid p
  , n.claimdetailid n 
  from (
    select claimdetailid
    , linenum
    , hcpcsorg
    , reimborg
    , unitsorg
    , 0-totalorg totalorg
    , row_number() over (partition by linenum, hcpcsorg, reimborg, unitsorg, totalorg order by claimdetailid) r
    from [table]
    where totalorg >= 0
  ) p
  inner join (
    select claimdetailid
    , linenum
    , hcpcsorg
    , reimborg
    , unitsorg
    , totalorg
    , row_number() over (partition by linenum, hcpcsorg, reimborg, unitsorg, totalorg order by claimdetailid) r
    from [table]
    where totalorg <= 0
  ) n
  on n.linenum = p.linenum
  and n.hcpcsorg = p.hcpcsorg
  and n.reimborg = p.reimborg
  and n.unitsorg = p.unitsorg
  and n.totalorg = p.totalorg
  and n.r = p.r
) x
where [table].claimdetailid in (x.p, x.n);

SQL Fiddle: http://sqlfiddle.com/#!6/d8910/4

Explanation

  • The p and n subqueries list the positive and negative values (I've also includes zeros in both, since I guess these reconcile against themselves).

  • The row_number() over (partition by .... ensures that where there is more than a 1:1 match, only matching pairs are removed (e.g. if I have 2 negative and 4 positive values with all else being equal, after deletion 2 positive values will remain.

  • the double from at the start is a cheat's inner join for delete statements (How to Delete using INNER JOIN with SQL Server?) which allows me to compare the claimdetailid against both positive and negative values easily and the most efficiently of the available options (e.g. compared to doing an exists statement or having to have the x subquery repeated, once to return positives and once for negatives.

  • 0-totalorg converts the negative value to a positive one, so it can be compared to the reconciling (offsetting) positive value in the inner join.

Community
  • 1
  • 1
JohnLBevan
  • 22,735
  • 13
  • 96
  • 178
  • ps. I used [table] instead of #table simply to make it work in SQL Fiddle; in SQL Server you should be able to happily use either. – JohnLBevan Jul 12 '14 at 23:46
  • You're counting `0` valued rows in both subqueries (unlikely to be an issue here, but worth mentioning). This fails if the first positive row doesn't match the first negative row - that is, if amounts were `15`, `74`, `-74`. – Clockwork-Muse Jul 12 '14 at 23:58
  • @Clockwork-Muse counting zeros is deliberate (see first point in explanation); I assume the idea is to remove any rows which are for the same item and total zero; if you have a single row with value 0 it (literally) adds no value, so should be deleted. – JohnLBevan Jul 13 '14 at 00:01
  • @Clockwork-Muse for your second point, I don't think it does. I include `totalorg` in the `join` and the `partition by`, so rows with values `74` and `-74` are unaware of / unaffected by the row with value `15`. – JohnLBevan Jul 13 '14 at 00:02
  • 1
    The problem is your row numbers won't match for the `74`/`-74` pair. You have tuples like `(1, 15)`, `(2, 74)`, `(1, -74)`, so they'll be thrown out by the join. I missed that bit about 0. – Clockwork-Muse Jul 13 '14 at 00:11
  • @Clockwork-Muse I'm not sure I follow. `hcpcsorg = 15` won't affect `hcpcsorg in (74, -74)`. You've also put `(1,-74)`, `(2, 74)`; what are 1 and 2 there? If they're line numbers / other fields from `[table]` it's correct that they don't join; they're in different sets. If I've misunderstood please can you provide a sql fiddle link to demonstrate the potential issue? – JohnLBevan Jul 13 '14 at 00:16
  • @Clockwork-Muse; apologies - you're right - just noticed that contrary to what I'd said above, I didn't include `hcpcsorg` in the `partition by`; good spot! – JohnLBevan Jul 13 '14 at 00:17
  • You need the reversal of| and n.totalorg = p.totalorg | to| and 0-n.totalorg = p.totalorg | – Paul Maxwell Jul 13 '14 at 00:21
  • @user2067753 correct - only I take care of that in the select statement of the `p` subquery (I figured it's probably more efficient to do it there than on the join itself; though in reality SQL's probably smart enough to optimise either way). – JohnLBevan Jul 13 '14 at 00:24
  • No I mean - assuming the rest of the `PARTITION BY` matches, if there's any differing amount it's going to throw the generation of the row number off. [I've modified your fiddle](http://sqlfiddle.com/#!6/554c4/1), so that all `linenum = 3` have the same partition, so only the amount is different. – Clockwork-Muse Jul 13 '14 at 00:25
  • 1
    ok, I see it now, but the sqlfiddle I visited didn't do it that way (a mismatch) – Paul Maxwell Jul 13 '14 at 00:28
  • @Clockwork-Muse - think we crossed threads; I corrected myself & the code in my later comment. Thanks again for spotting. – JohnLBevan Jul 13 '14 at 00:31
  • @user2067753 - in my original version I had the `0-n.totalorg=p.totalorg` code in the join; later I revised both the code above and SQL Fiddle to include the `0-totalorg` in the subquery instead (after a rethink on which was likely to be more efficient; though as mentioned I doubt there's anything in it). Please check latest versions above. – JohnLBevan Jul 13 '14 at 00:33