CODEWITHSUNDEEP
sqlsql-server
RoverRoll
RoverRoll

Reputation: 135

subtract two rows and keep the latest row with the same ID in Sql Server

I have a select query where i need to remove or subtract two rows and keep the latest chargeID for the same patid initially i used in my where condtion e.g

 where transactiontype!=void and transactiontype='charges'.

I need to keep the latest 'charges' row. below is the table table name tranMaster

Upvotes: 1

Views: 139

Answers (2)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

Try this 

DECLARE @T TABLE
(
    PatId INT,
    ChargeId INT IDENTITY(1,1),
    TranType VARCHAR(20),
    SomeText VARCHAR(20)
)

INSERT INTO @T
(
    PatId,
    TranType,
    SomeText
)
VALUES(598,'Void','Sample 11'),
    (598,'Charges','Sample 12'),
    (598,'Charges','Sample 13'),
    (611,'Void','Sample 21'),
    (611,'Void','Sample 22'),
    (611,'Charges','Sample 23')

;WITH CTE
AS
(
    SELECT
       RN = ROW_NUMBER() OVER(PARTITION BY PatId,TranType ORDER BY ChargeId DESC),
       *
       FROM @T
)
SELECT
    *
    FROM CTE
       WHERE RN = 1

Sample Input

enter image description here

Sample Output

enter image description here

Upvotes: 2

JERRY
JERRY

Reputation: 1173

Use ranking function with partition by patid and order by chargeid DESC then filter out unnecessary records from where clause.

Upvotes: 1

Related Questions