how to compare and retrieve one row from two row.

Question

User1878568433 posted

Claim_Number    DOSFrom    DOSTo         VC_Box24NPI   VC_Box33NPI        VC_Tax_ID    VC_Billing_ProviderName
315144                   NULL           NULL         9879837598      7823648375          NULL                    NULL
315145                   NULL            NULL        9879837598      7823648375          NULL                   NULL
315145                2019-05-01  2019-05-31 4306947527       7623473530      209437538       PROVIDER, TEST

Here I need 1st and 3 rd row .Because 2nd and 3 row claim number is same.but DOS FROm and Dos To is different.so when DOS FROm and Dos To is not null ,this row i need.

So here I need 1 st and 3 rd row.

Please help me ..how to do.

Answer 1

User-2146987983 posted

Here is the one way to get the desired result:

DECLARE @TAB TABLE (Claim_Number INT, DOSFrom DATE, DOSTo DATE, VC_Box24NPI VARCHAR(20), VC_Box33NPI VARCHAR(20), VC_Tax_ID VARCHAR(20), VC_Billing_ProviderName VARCHAR(20))

INSERT INTO @TAB 
VALUES (315144, NULL, NULL, '9879837598', '7823648375', NULL, NULL),
(315145, NULL, NULL, '9879837598', '7823648375', NULL, NULL),
(315145, '2019-05-01', '2019-05-31', '4306947527', '7623473530', '209437538', 'PROVIDER, TEST')

SELECT * FROM @TAB

;WITH CTE AS (
	SELECT ROW_NUMBER() OVER(PARTITION BY Claim_Number ORDER BY DOSFrom DESC, DOSTo DESC) AS rn, * FROM @TAB
)
SELECT Claim_Number, DOSFrom, DOSTo, VC_Box24NPI, VC_Box33NPI, VC_Tax_ID, VC_Billing_ProviderName FROM CTE WHERE rn =  1