How do we eliminate the pairs and only display the single rows ? - SQL HELP

Question

How do we eliminate the pairs and only display the single rows ? - SQL HELP

mw_sql_developer

SSCoach

Points: 19445
More actions
May 30, 2018 at 1:35 pm

#368610

My question is listed within the SQL code.
IF OBJECT_ID('tempdb..#RX') IS NOT NULL DROP TABLE #RX;
create table #RX(EmployeeNbr VARCHAR(20) , Claim_Number VARCHAR(20) , Rx_Number VARCHAR(20), AmtPaid MONEY, Adj MONEY, NDC VARCHAR(10), Date_Filled VARCHAR(10) ,CLAIM_TYPE CHAR(1) )
INSERT INTO #RX VALUES ( '1', '123', '12345', 100.00, 0.00, '9876', '20170101', 'P' ); INSERT INTO #RX VALUES ( '1', '123', '12345', 0.00, -100.00, '9876', '20170101', 'A' ); INSERT INTO #RX VALUES ( '1', '123', '12345', 100.00, 0.00, '9876', '20170101', 'P' );
INSERT INTO #RX VALUES ( '2', '456', 'XXY34', 100.00, 0.00, '5555', '20170101', 'P' ); INSERT INTO #RX VALUES ( '2', '456', 'XXY34', 0.00, -100.00, '5555', '20170101', 'A' ); INSERT INTO #RX VALUES ( '2', '456', 'XXY34', 100.00, 0.00, '5555', '20170101', 'P' );
Select * FROM #RX -- ?????
--What I need is the output to just have 2 rows. One for Claim 123 and one for Claim 456. --Why ? One of the lines get canclelled by the other ( CLAIM_TYPE = 'A' ) and the sum of AmtPaid is ZERO. --Now, my table has manay rows ( not just 6 ). But I included 6 rows for this example. --So I expect the solution to work for anythinhg greater than 6 rows.
--Objective: Exliminate the pairs ( A, P ) that cancel each other and display the remainder.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246962 More actions · Answer 1

mw_sql_developer - Wednesday, May 30, 2018 1:35 PM
My question is listed within the SQL code.
IF OBJECT_ID('tempdb..#RX') IS NOT NULL DROP TABLE #RX;
create table #RX(EmployeeNbr VARCHAR(20) , Claim_Number VARCHAR(20) , Rx_Number VARCHAR(20), AmtPaid MONEY, Adj MONEY, NDC VARCHAR(10), Date_Filled VARCHAR(10) ,CLAIM_TYPE CHAR(1) )
INSERT INTO #RX VALUES ( '1', '123', '12345', 100.00, 0.00, '9876', '20170101', 'P' ); INSERT INTO #RX VALUES ( '1', '123', '12345', 0.00, -100.00, '9876', '20170101', 'A' ); INSERT INTO #RX VALUES ( '1', '123', '12345', 100.00, 0.00, '9876', '20170101', 'P' );
INSERT INTO #RX VALUES ( '2', '456', 'XXY34', 100.00, 0.00, '5555', '20170101', 'P' ); INSERT INTO #RX VALUES ( '2', '456', 'XXY34', 0.00, -100.00, '5555', '20170101', 'A' ); INSERT INTO #RX VALUES ( '2', '456', 'XXY34', 100.00, 0.00, '5555', '20170101', 'P' );
Select * FROM #RX -- ?????
--What I need is the output to just have 2 rows. One for Claim 123 and one for Claim 456. --Why ? One of the lines get canclelled by the other ( CLAIM_TYPE = 'A' ) and the sum of AmtPaid is ZERO. --Now, my table has manay rows ( not just 6 ). But I included 6 rows for this example. --So I expect the solution to work for anythinhg greater than 6 rows.
--Objective: Exliminate the pairs ( A, P ) that cancel each other and display the remainder.

Something like this, maybe?
SELECT EmployeeNbr = MAX(r.EmployeeNbr) , r.Claim_Number , Rx_Number = MAX(r.Rx_Number) , AmtPaid = SUM(r.AmtPaid + r.Adj) , NDC = MAX(r.NDC) , Date_Filled = MAX(r.Date_Filled) FROM #RX r GROUP BY r.Claim_Number;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sgmunson SSC Guru Points: 110640 More actions · Answer 2

Phil Parkin - Wednesday, May 30, 2018 1:43 PM
mw_sql_developer - Wednesday, May 30, 2018 1:35 PM
My question is listed within the SQL code.
IF OBJECT_ID('tempdb..#RX') IS NOT NULL DROP TABLE #RX;
create table #RX(EmployeeNbr VARCHAR(20) , Claim_Number VARCHAR(20) , Rx_Number VARCHAR(20), AmtPaid MONEY, Adj MONEY, NDC VARCHAR(10), Date_Filled VARCHAR(10) ,CLAIM_TYPE CHAR(1) )
INSERT INTO #RX VALUES ( '1', '123', '12345', 100.00, 0.00, '9876', '20170101', 'P' ); INSERT INTO #RX VALUES ( '1', '123', '12345', 0.00, -100.00, '9876', '20170101', 'A' ); INSERT INTO #RX VALUES ( '1', '123', '12345', 100.00, 0.00, '9876', '20170101', 'P' );
INSERT INTO #RX VALUES ( '2', '456', 'XXY34', 100.00, 0.00, '5555', '20170101', 'P' ); INSERT INTO #RX VALUES ( '2', '456', 'XXY34', 0.00, -100.00, '5555', '20170101', 'A' ); INSERT INTO #RX VALUES ( '2', '456', 'XXY34', 100.00, 0.00, '5555', '20170101', 'P' );
Select * FROM #RX -- ?????
--What I need is the output to just have 2 rows. One for Claim 123 and one for Claim 456. --Why ? One of the lines get canclelled by the other ( CLAIM_TYPE = 'A' ) and the sum of AmtPaid is ZERO. --Now, my table has manay rows ( not just 6 ). But I included 6 rows for this example. --So I expect the solution to work for anythinhg greater than 6 rows.
--Objective: Exliminate the pairs ( A, P ) that cancel each other and display the remainder.
Something like this, maybe?
SELECT EmployeeNbr = MAX(r.EmployeeNbr) , r.Claim_Number , Rx_Number = MAX(r.Rx_Number) , AmtPaid = SUM(r.AmtPaid + r.Adj) , NDC = MAX(r.NDC) , Date_Filled = MAX(r.Date_Filled) FROM #RX r GROUP BY r.Claim_Number;

Grouping will show the net resulting values, but may lose other record information, or potentially mix values due to MAX aggregation. I wouldn't want to rely on this method. I do know that the alternatives are seriously ugly, but I think they may well be essential if this is a financial application. Considering the identity of the original poster, I would avoid grouping like the plague. The specific transactions really need to be identified without grouping. This will require matching pairs of offsetting transactions, and the given data is oversimplified for that, as there's no time value in the date column, and that could have been used to help make each row unique. Time to ask the OP whether or not the actual scenario has any kind of unique-making property for a given transaction. It's a necessary thing in order to solve this problem the right way.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

mw_sql_developer SSCoach Points: 19445 More actions · Answer 3

sgmunson - Wednesday, May 30, 2018 1:59 PM
Phil Parkin - Wednesday, May 30, 2018 1:43 PM
mw_sql_developer - Wednesday, May 30, 2018 1:35 PM
My question is listed within the SQL code.
IF OBJECT_ID('tempdb..#RX') IS NOT NULL DROP TABLE #RX;
create table #RX(EmployeeNbr VARCHAR(20) , Claim_Number VARCHAR(20) , Rx_Number VARCHAR(20), AmtPaid MONEY, Adj MONEY, NDC VARCHAR(10), Date_Filled VARCHAR(10) ,CLAIM_TYPE CHAR(1) )
INSERT INTO #RX VALUES ( '1', '123', '12345', 100.00, 0.00, '9876', '20170101', 'P' ); INSERT INTO #RX VALUES ( '1', '123', '12345', 0.00, -100.00, '9876', '20170101', 'A' ); INSERT INTO #RX VALUES ( '1', '123', '12345', 100.00, 0.00, '9876', '20170101', 'P' );
INSERT INTO #RX VALUES ( '2', '456', 'XXY34', 100.00, 0.00, '5555', '20170101', 'P' ); INSERT INTO #RX VALUES ( '2', '456', 'XXY34', 0.00, -100.00, '5555', '20170101', 'A' ); INSERT INTO #RX VALUES ( '2', '456', 'XXY34', 100.00, 0.00, '5555', '20170101', 'P' );
Select * FROM #RX -- ?????
--What I need is the output to just have 2 rows. One for Claim 123 and one for Claim 456. --Why ? One of the lines get canclelled by the other ( CLAIM_TYPE = 'A' ) and the sum of AmtPaid is ZERO. --Now, my table has manay rows ( not just 6 ). But I included 6 rows for this example. --So I expect the solution to work for anythinhg greater than 6 rows.
--Objective: Exliminate the pairs ( A, P ) that cancel each other and display the remainder.
Something like this, maybe?
SELECT EmployeeNbr = MAX(r.EmployeeNbr) , r.Claim_Number , Rx_Number = MAX(r.Rx_Number) , AmtPaid = SUM(r.AmtPaid + r.Adj) , NDC = MAX(r.NDC) , Date_Filled = MAX(r.Date_Filled) FROM #RX r GROUP BY r.Claim_Number;
Grouping will show the net resulting values, but may lose other record information, or potentially mix values due to MAX aggregation. I wouldn't want to rely on this method. I do know that the alternatives are seriously ugly, but I think they may well be essential if this is a financial application. Considering the identity of the original poster, I would avoid grouping like the plague. The specific transactions really need to be identified without grouping. This will require matching pairs of offsetting transactions, and the given data is oversimplified for that, as there's no time value in the date column, and that could have been used to help make each row unique. Time to ask the OP whether or not the actual scenario has any kind of unique-making property for a given transaction. It's a necessary thing in order to solve this problem the right way.

Cool, It works...

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

mw_sql_developer - Wednesday, May 30, 2018 3:16 PM
sgmunson - Wednesday, May 30, 2018 1:59 PM
Phil Parkin - Wednesday, May 30, 2018 1:43 PM
mw_sql_developer - Wednesday, May 30, 2018 1:35 PM
My question is listed within the SQL code.
IF OBJECT_ID('tempdb..#RX') IS NOT NULL DROP TABLE #RX;
create table #RX(EmployeeNbr VARCHAR(20) , Claim_Number VARCHAR(20) , Rx_Number VARCHAR(20), AmtPaid MONEY, Adj MONEY, NDC VARCHAR(10), Date_Filled VARCHAR(10) ,CLAIM_TYPE CHAR(1) )
INSERT INTO #RX VALUES ( '1', '123', '12345', 100.00, 0.00, '9876', '20170101', 'P' ); INSERT INTO #RX VALUES ( '1', '123', '12345', 0.00, -100.00, '9876', '20170101', 'A' ); INSERT INTO #RX VALUES ( '1', '123', '12345', 100.00, 0.00, '9876', '20170101', 'P' );
INSERT INTO #RX VALUES ( '2', '456', 'XXY34', 100.00, 0.00, '5555', '20170101', 'P' ); INSERT INTO #RX VALUES ( '2', '456', 'XXY34', 0.00, -100.00, '5555', '20170101', 'A' ); INSERT INTO #RX VALUES ( '2', '456', 'XXY34', 100.00, 0.00, '5555', '20170101', 'P' );
Select * FROM #RX -- ?????
--What I need is the output to just have 2 rows. One for Claim 123 and one for Claim 456. --Why ? One of the lines get canclelled by the other ( CLAIM_TYPE = 'A' ) and the sum of AmtPaid is ZERO. --Now, my table has manay rows ( not just 6 ). But I included 6 rows for this example. --So I expect the solution to work for anythinhg greater than 6 rows.
--Objective: Exliminate the pairs ( A, P ) that cancel each other and display the remainder.
Something like this, maybe?
SELECT EmployeeNbr = MAX(r.EmployeeNbr) , r.Claim_Number , Rx_Number = MAX(r.Rx_Number) , AmtPaid = SUM(r.AmtPaid + r.Adj) , NDC = MAX(r.NDC) , Date_Filled = MAX(r.Date_Filled) FROM #RX r GROUP BY r.Claim_Number;
Grouping will show the net resulting values, but may lose other record information, or potentially mix values due to MAX aggregation. I wouldn't want to rely on this method. I do know that the alternatives are seriously ugly, but I think they may well be essential if this is a financial application. Considering the identity of the original poster, I would avoid grouping like the plague. The specific transactions really need to be identified without grouping. This will require matching pairs of offsetting transactions, and the given data is oversimplified for that, as there's no time value in the date column, and that could have been used to help make each row unique. Time to ask the OP whether or not the actual scenario has any kind of unique-making property for a given transaction. It's a necessary thing in order to solve this problem the right way.
Cool, It works...

Unless I'm flat out missing something, you need to do some more testing.... you're trying to simply eliminate duplicate rows and the GROUP BY and MAX stuff isn't going to do that correctly except for the explicit items you posted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)