Extracting unique records based on two columns

Question

Extracting unique records based on two columns

lulu_lutz

SSCrazy

Points: 2155
More actions
May 23, 2012 at 1:29 pm

#261477

I've attached the ddl for the table, the insert, and the desired results. I need to write a query that does this for duplicate Patient_nbr's:
- If the status is the same, Pulls the record with the max tracking_id
- If status is different, pull both records
So status and patient_nbr make the record unique. There shouldn't be a line that has the same patient_nbr and status.

Viewing 11 posts - 1 through 10 (of 10 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

sqluser_8119 (5/23/2012)
I've attached the ddl for the table, the insert, and the desired results. I need to write a query that does this for duplicate Patient_nbr's:
- If the status is the same, Pulls the record with the max tracking_id
- If status is different, pull both records
So status and patient_nbr make the record unique. There shouldn't be a line that has the same patient_nbr and status.

Two things. Your Sample output file does not match your sample data AND SECOND YOUR SAMPLE OUTPUT DATA LOOKS LIKE REAL DATA?!?!?!?!?!?!?

The second thing is that you should just include your code inside the IFCode blocks instead of files. They are over there on the left when you are posting.

I will post the first two.

CREATE TABLE [dbo].[claim_report](

[claim_stat] [char](1) NOT NULL,

[status_note_code] [varchar](10) NULL,

[biller_cd] [varchar](20) NOT NULL,

[form_id] [varchar](10) NOT NULL,

[patient_nbr] [varchar](24) NOT NULL,

[patient_name] [varchar](123) NULL,

[entry_date] [datetime] NULL,

[process_date] [datetime] NOT NULL,

[tracking_id] [decimal](12, 0) NULL,

[claim_id] [decimal](12, 0) NULL

) ON [PRIMARY]

INSERT INTO claim_report (claim_stat, status_note_code, biller_cd, form_id, patient_nbr, patient_name, entry_date, process_date, tracking_id, claim_id)

VALUES ('H', 'NBN', 'B78', 'FUB04MICOO', 'P99999999999', 'DOE, FIRSTNAME' , '2012-05-02 08:07:26.433', '2012-05-02 08:07:26.367', 20010103, 1661791),

('H', 'INFO', 'B43', 'GUB04MIBCO', 'P99999999999', 'DOE, FIRSTNAME', '2012-02-13 09:18:04.380', '2012-02-13 09:18:03.547', 19478021, 1661791),

('H', 'INFO', 'B43', 'GUB04MIBCO', 'P88888888888', 'LASTNAME,TEST', '2012-02-13 09:17:36.490', '2012-02-13 09:17:35.657', 19478017, 1617819),

('I', 'LATE', 'B78', 'FUB04MICOO', 'P88888888888', 'LASTNAME,TEST', '2012-05-04 07:57:11.870', '2012-05-04 07:57:11.697', 20027081, 1617819),

('H', 'CODE', 'B13', 'CUB04MIMCO', 'P88888888888', 'LASTNAME,TEST' , '2012-04-19 07:04:33.643', '2012-04-19 07:04:33.160', 19921521, 1617819),

('H', 'INFO', 'B78', 'FUB04MICOO', 'P44444444444', 'SMITH, FIRSTNAME', '2012-05-21 08:30:57.440', '2012-05-21 08:30:57.427', 20141186, 1671804),

('H', 'LAB', 'B78', 'FUB04MILBO', 'P44444444444', 'SMITH, FIRSTNAME' , '2012-05-23 08:17:31.080', '2012-05-23 08:17:30.933', 20160758, 1987898),

('H', 'LATE', 'B43', 'GUB04MIBCO', 'P55555555555', 'COOPER, TESTING', '2012-04-04 09:01:49.617', '2012-03-30 16:00:16.000', 19828745, 1644578),

('I', 'COB', 'B43', 'GUB04MIBCO', 'P55555555555', 'COOPER, TESTING', '2012-05-14 09:46:20.487', '2012-05-14 09:46:20.290', 20094188, 1644578),

('H', 'LATE', 'B78', 'FUB04MICOO', 'P22222222222', 'MILLER, FIRSTTEST', '2012-05-11 08:21:00.870', '2012-05-11 08:21:00.673', 20078803, 1667260)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

There is no status column. There is a column called claim_stat. Is this column you mean when you say status?

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

I think something along these lines is what you are after?

select * from

(

select claim_stat, patient_nbr, status_note_code, biller_cd, form_id, patient_name, entry_date, process_date, tracking_id, claim_id,

ROW_NUMBER() over(partition by patient_nbr, claim_stat order by Tracking_id desc) as RowNum

from claim_report

) x

where x.RowNum = 1

order by patient_nbr, claim_stat

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Here is my first shot at your request.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[claim_report](

[claim_stat] [char](1) NOT NULL,

[status_note_code] [varchar](10) NULL,

[biller_cd] [varchar](20) NOT NULL,

[form_id] [varchar](10) NOT NULL,

[patient_nbr] [varchar](24) NOT NULL,

[patient_name] [varchar](123) NULL,

[entry_date] [datetime] NULL,

[process_date] [datetime] NOT NULL,

[tracking_id] [decimal](12, 0) NULL,

[claim_id] [decimal](12, 0) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT INTO claim_report (claim_stat, status_note_code, biller_cd, form_id, patient_nbr, patient_name, entry_date, process_date, tracking_id, claim_id)

VALUES ('H', 'NBN', 'B78', 'FUB04MICOO', 'P99999999999', 'DOE, FIRSTNAME' , '2012-05-02 08:07:26.433', '2012-05-02 08:07:26.367', 20010103, 1661791)

INSERT INTO claim_report (claim_stat, status_note_code, biller_cd, form_id, patient_nbr, patient_name, entry_date, process_date, tracking_id, claim_id)

VALUES ('H', 'INFO', 'B43', 'GUB04MIBCO', 'P99999999999', 'DOE, FIRSTNAME', '2012-02-13 09:18:04.380', '2012-02-13 09:18:03.547', 19478021, 1661791)

INSERT INTO claim_report (claim_stat, status_note_code, biller_cd, form_id, patient_nbr, patient_name, entry_date, process_date, tracking_id, claim_id)

VALUES ('H', 'INFO', 'B43', 'GUB04MIBCO', 'P88888888888', 'LASTNAME,TEST', '2012-02-13 09:17:36.490', '2012-02-13 09:17:35.657', 19478017, 1617819)

INSERT INTO claim_report (claim_stat, status_note_code, biller_cd, form_id, patient_nbr, patient_name, entry_date, process_date, tracking_id, claim_id)

VALUES ('I', 'LATE', 'B78', 'FUB04MICOO', 'P88888888888', 'LASTNAME,TEST', '2012-05-04 07:57:11.870', '2012-05-04 07:57:11.697', 20027081, 1617819)

INSERT INTO claim_report (claim_stat, status_note_code, biller_cd, form_id, patient_nbr, patient_name, entry_date, process_date, tracking_id, claim_id)

VALUES ('H', 'CODE', 'B13', 'CUB04MIMCO', 'P88888888888', 'LASTNAME,TEST' , '2012-04-19 07:04:33.643', '2012-04-19 07:04:33.160', 19921521, 1617819)

INSERT INTO claim_report (claim_stat, status_note_code, biller_cd, form_id, patient_nbr, patient_name, entry_date, process_date, tracking_id, claim_id)

VALUES ('H', 'INFO', 'B78', 'FUB04MICOO', 'P44444444444', 'SMITH, FIRSTNAME', '2012-05-21 08:30:57.440', '2012-05-21 08:30:57.427', 20141186, 1671804)

INSERT INTO claim_report (claim_stat, status_note_code, biller_cd, form_id, patient_nbr, patient_name, entry_date, process_date, tracking_id, claim_id)

VALUES ('H', 'LAB', 'B78', 'FUB04MILBO', 'P44444444444', 'SMITH, FIRSTNAME' , '2012-05-23 08:17:31.080', '2012-05-23 08:17:30.933', 20160758, 1987898)

INSERT INTO claim_report (claim_stat, status_note_code, biller_cd, form_id, patient_nbr, patient_name, entry_date, process_date, tracking_id, claim_id)

VALUES ('H', 'LATE', 'B43', 'GUB04MIBCO', 'P55555555555', 'COOPER, TESTING', '2012-04-04 09:01:49.617', '2012-03-30 16:00:16.000', 19828745, 1644578)

INSERT INTO claim_report (claim_stat, status_note_code, biller_cd, form_id, patient_nbr, patient_name, entry_date, process_date, tracking_id, claim_id)

VALUES ('I', 'COB', 'B43', 'GUB04MIBCO', 'P55555555555', 'COOPER, TESTING', '2012-05-14 09:46:20.487', '2012-05-14 09:46:20.290', 20094188, 1644578)

INSERT INTO claim_report (claim_stat, status_note_code, biller_cd, form_id, patient_nbr, patient_name, entry_date, process_date, tracking_id, claim_id)

VALUES ('H', 'LATE', 'B78', 'FUB04MICOO', 'P22222222222', 'MILLER, FIRSTTEST', '2012-05-11 08:21:00.870', '2012-05-11 08:21:00.673', 20078803, 1667260)

;

GO

WITH BaseData AS (

SELECT

claim_stat,

status_note_code,

biller_cd,

form_id,

patient_nbr,

patient_name,

entry_date,

process_date,

tracking_id,

claim_id,

ROW_NUMBER() OVER (PARTITION BY patient_nbr, claim_stat ORDER BY tracking_id DESC) AS RowNum

FROM

dbo.claim_report

)

SELECT

claim_stat,

status_note_code,

biller_cd,

form_id,

patient_nbr,

patient_name,

entry_date,

process_date,

tracking_id,

claim_id

FROM

BaseData

WHERE

RowNum = 1

;

GO

DROP TABLE [dbo].[claim_report];

GO

lulu_lutz SSCrazy Points: 2155 More actions · Answer 5

lulu_lutz

SSCrazy

Points: 2155

May 23, 2012 at 1:46 pm

#1492092

yes claim_stat...sorry

lulu_lutz SSCrazy Points: 2155 More actions · Answer 6

lulu_lutz

SSCrazy

Points: 2155

May 23, 2012 at 1:46 pm

#1492093

This isn't real data so relax

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

sqluser_8119 (5/23/2012)
This isn't real data so relax

LOL. I meant the info in your desired output. Obvious the inserts is fake stuff.

So it looks like Lynn and I posted the exact same code, well actually we aliased the subquery with different names. 😉 Does that work for what you need?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Just a slight mod to my code. I added an ORDER BY.