CODEWITHSUNDEEP
sqlsql-servert-sql
user3833239
user3833239

Reputation: 49

SQL Server - remove offsetting (money) rows

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

Upvotes: 1

Views: 1247

Answers (1)

JohnLBevan
JohnLBevan

Reputation: 24470

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.

Upvotes: 2

Related Questions