May 23, 2012 at 1:29 pm
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.
May 23, 2012 at 1:33 pm
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/
May 23, 2012 at 1:39 pm
There is no status column. There is a column called claim_stat. Is this column you mean when you say status?
May 23, 2012 at 1:40 pm
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/
May 23, 2012 at 1:44 pm
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
May 23, 2012 at 1:46 pm
yes claim_stat...sorry
May 23, 2012 at 1:46 pm
This isn't real data so relax
May 23, 2012 at 1:49 pm
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/
May 23, 2012 at 1:51 pm
Just a slight mod to my code. I added an ORDER BY.
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
ORDER BY
patient_nbr,
tracking_id DESC;
GO
DROP TABLE [dbo].[claim_report];
GO
May 23, 2012 at 1:53 pm
Sean Lange (5/23/2012)
sqluser_8119 (5/23/2012)
This isn't real data so relaxLOL. 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?
Didn't look at yours before adding my order by, but looks like we chose different columns.
May 23, 2012 at 1:53 pm
I removed it so no one else would think that 🙂 Thank you so much...this works perfectly. I can always count on someone from here to help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply