Comparing Current to Subsequent Dates - Duplicate and Erroneous Data

Below is the query I have written using MS SQL to capture subsequent admission dates for a patient population.

Sometimes the data is correct and sometimes when I have several admissions for the same patient (MedRecNumber) I get duplicate lines with the incorrect subsequent date.  Or I may get the previous admission date on a current admission.

I have attached a sample of the output I received.  The RED lines are where the T2AdmitDate (Subsequent Admit Date) is actually the previous date).  The YELLOW lines are where I have duplicate records with the subsequent date and the next subsequent admission date.

Please reveiew the code and let me know how to fix the query.

Thank you

Glen

SELECT     TOP (100) PERCENT 
T.MedRecNumber, 
T.AcctNumber,
T.DisYr, 
T.DisMo,
T.LaceScore, 
T.LaceDate, 
T.AdmDate as TAdmitDate,
T.DischDate AS DischargeDate,

CONVERT(char, T2.AdmDate, 101)AS T2AdmitDate, 

DATEDIFF(d, T.DischDate, T2.AdmDate) AS ReAdmDays

 

FROM         
                      dbo.vw_Lace_Discharge_Summary as T LEFT OUTER JOIN
                      dbo.vw_Lace_Discharge_Summary AS T2 ON T.MedRecNumber = T2.MedRecNumber AND T.AdmDate < T2.AdmDate

GROUP BY T.MedRecNumber, 
T.AcctNumber,
T.DisYr, 
T.DisMo,
T.LaceScore, 
T.LaceDate, 
T.AdmDate ,
T.DischDate,
CONVERT(char, T2.AdmDate, 101) 
ORDER BY T.MedRecNumber,T.AcctNumber

Open in new window

Readmissions.xlsx
GPSPOWAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
Eyeballeth thy article on SQL Server Delete Duplicate Rows Solutions, as it looks like it speaks to your situation, once you copy-paste the code, change it to fit your situation, and execute.

>sometimes when I have several admissions for the same patient (MedRecNumber) I get duplicate lines with the incorrect subsequent date.
>Or I may get the previous admission date on a current admission.
You'll have to spell out the logic for determining the 'correct' and 'incorrect' date.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:
I see how to eliminate the duplicate rows.  Why am not getting the correct date on the two records where the T2.AdmDate is coming over as the date prior.

MedRecNumber      AcctNumber      DisYr      DisMo      LaceScore      LaceDate      TAdmitDate      DischargeDate      T2AdmitDate      ReAdmDays      ARank
      NULL      NULL      1
M000000206      A00000261780      2014      12      4      2014-12-28 14:37:00.000      12/27/2014                          12/31/2014                          NULL      NULL      1
M000000206      A00000263130      2015      1      5      2015-01-11 09:42:00.000      01/11/2015                          01/14/2015                          12/27/2014                          -18      1


Thanks

Glen
PortletPaulEE Topic AdvisorCommented:
I do not know if you really do have duplicates in that table.

But  more reliable method of aligning visits by a person would be to use ROW_NUMBER() through a CTE like this:
;with CTE as (
    select *
    , ROW_NUMBER() OVER(PARTITION BY MedRecNumber ORDER BY AdmDate desc) AS RN
    from vw_Lace_Discharge_Summary
    )
select
    T.MedRecNumber, T.AcctNumber, T.DisYr, T.DisMo, T.LaceScore, T.LaceDate, T.AdmDate, T.DischDate
  , CONVERT(char, T2.AdmDate, 101) AS T2AdmitDate
  , DATEDIFF(D, T.DischDate, T2.AdmDate) AS ReAdmDays
from CTE T
inner join CTE T2 ON T.MedRecNumber = t2.MedRecNumber
                 AND T.RN + 1 = T2.RN
;

Open in new window


using that a result like this should be the outcome:
| MedRecNumber |   AcctNumber | DisYr | DisMo | LaceScore |                       LaceDate |                     TAdmitDate |                  DischargeDate |                    T2AdmitDate | ReAdmDays |
|--------------|--------------|-------|-------|-----------|--------------------------------|--------------------------------|--------------------------------|--------------------------------|-----------|
|   M000000206 | A00000263130 |  2015 |     1 |         5 | 01/11/2015                     | 01/11/2015                     | 01/14/2015                     | 12/27/2014                     |       -18 |
|   M000001173 | A00000269416 |  2015 |     3 |         6 | 03/08/2015                     | 03/08/2015                     | 03/11/2015                     | 02/18/2015                     |       -21 |
|   M000001173 | A00000267408 |  2015 |     2 |         2 | 02/20/2015                     | 02/18/2015                     | 02/26/2015                     | 01/05/2015                     |       -52 |
|   M000001252 | A00000266432 |  2015 |     2 |        10 | 02/13/2015                     | 02/09/2015                     | 02/13/2015                     | 01/14/2015                     |       -30 |
|   M000002322 | A00000266945 |  2015 |     2 |         9 | 02/14/2015                     | 02/13/2015                     | 02/20/2015                     | 01/15/2015                     |       -36 |
        

Open in new window


Change the INNER JOIN to LEFT JOIN if you want all the unmatched records as well.

{edit}
I used your spreadsheet for inserts details here:
    CREATE TABLE vw_Lace_Discharge_Summary
        ([ID] int IDENTITY(1,1) primary key, [MedRecNumber] varchar(10), [AcctNumber] varchar(12), [DisYr] int, [DisMo] int, [LaceScore] int, [LaceDate] datetime, [AdmDate] datetime, [DischDate] datetime)
    ;
        
    INSERT INTO vw_Lace_Discharge_Summary
        ([MedRecNumber], [AcctNumber], [DisYr], [DisMo], [LaceScore], [LaceDate], [AdmDate], [DischDate])
    VALUES
        ('M000000166', 'A00000261751', 2014, 12, 4, '2014-12-30 07:28:00', '2014-12-27 00:00:00', '2014-12-30 00:00:00'),
        ('M000000206', 'A00000261780', 2014, 12, 4, '2014-12-28 14:37:00', '2014-12-27 00:00:00', '2014-12-31 00:00:00'),
        ('M000000206', 'A00000263130', 2015, 1, 5, '2015-01-11 09:42:00', '2015-01-11 00:00:00', '2015-01-14 00:00:00'),
        ('M000000291', 'A00000268016', 2015, 3, 2, '2015-02-28 14:22:00', '2015-02-24 00:00:00', '2015-03-03 00:00:00'),
        ('M000000329', 'A00000262006', 2015, 1, 1, '2015-01-01 13:14:00', '2014-12-31 00:00:00', '2015-01-01 00:00:00'),
        ('M000000362', 'A00000258661', 2014, 12, 1, '2014-12-12 14:21:00', '2014-12-11 00:00:00', '2014-12-12 00:00:00'),
        ('M000000464', 'A00000258668', 2014, 12, 9, '2014-12-16 14:46:00', '2014-12-15 00:00:00', '2014-12-22 00:00:00'),
        ('M000000531', 'A00000261814', 2014, 12, 2, '2014-12-29 11:29:00', '2014-12-28 00:00:00', '2014-12-30 00:00:00'),
        ('M000000702', 'A00000265624', 2015, 3, 1, '2015-03-04 08:12:00', '2015-03-03 00:00:00', '2015-03-06 00:00:00'),
        ('M000000963', 'A00000263136', 2015, 1, 4, '2015-01-13 10:11:00', '2015-01-11 00:00:00', '2015-01-15 00:00:00'),
        ('M000001045', 'A00000260533', 2014, 12, 5, '2014-12-15 12:08:00', '2014-12-14 00:00:00', '2014-12-19 00:00:00'),
        ('M000001173', 'A00000262444', 2015, 1, 6, '2015-01-09 14:09:00', '2015-01-05 00:00:00', '2015-01-16 00:00:00'),
        ('M000001173', 'A00000267408', 2015, 2, 2, '2015-02-20 13:52:00', '2015-02-18 00:00:00', '2015-02-26 00:00:00'),
        ('M000001173', 'A00000269416', 2015, 3, 6, '2015-03-08 14:48:00', '2015-03-08 00:00:00', '2015-03-11 00:00:00'),
        ('M000001252', 'A00000263559', 2015, 1, 3, '2015-01-15 15:08:00', '2015-01-14 00:00:00', '2015-01-18 00:00:00'),
        ('M000001252', 'A00000266432', 2015, 2, 10, '2015-02-13 15:12:00', '2015-02-09 00:00:00', '2015-02-13 00:00:00'),
        ('M000001268', 'A00000263918', 2015, 1, 5, '2015-01-20 06:31:00', '2015-01-18 00:00:00', '2015-01-22 00:00:00'),
        ('M000001457', 'A00000258637', 2014, 12, 1, '2014-12-12 13:55:00', '2014-12-11 00:00:00', '2014-12-12 00:00:00'),
        ('M000001989', 'A00000261542', 2015, 1, 3, '2015-01-01 12:16:00', '2014-12-30 00:00:00', '2015-01-04 00:00:00'),
        ('M000002216', 'A00000260512', 2015, 1, 6, '2015-01-09 14:15:00', '2014-12-13 00:00:00', '2015-01-30 00:00:00'),
        ('M000002322', 'A00000263581', 2015, 1, 2, '2015-01-20 14:29:00', '2015-01-15 00:00:00', '2015-01-26 00:00:00'),
        ('M000002322', 'A00000266945', 2015, 2, 9, '2015-02-14 10:58:00', '2015-02-13 00:00:00', '2015-02-20 00:00:00')
    ;
    
**Query 1**:

    ;with CTE as (
        select *
        , ROW_NUMBER() OVER(PARTITION BY MedRecNumber ORDER BY AdmDate desc) AS RN
        from vw_Lace_Discharge_Summary
        )
    select
        T.MedRecNumber
      , T.AcctNumber
      , T.DisYr
      , T.DisMo
      , T.LaceScore
      , CONVERT(char, T.LaceDate, 101) AS LaceDate
      , CONVERT(char, T.AdmDate, 101) AS TAdmitDate
      , CONVERT(char, T.DischDate, 101) AS DischargeDate
      , CONVERT(char, T2.AdmDate, 101) AS T2AdmitDate
      , DATEDIFF(D, T.DischDate, T2.AdmDate) AS ReAdmDays
    from CTE T
    inner join CTE T2 ON T.MedRecNumber = t2.MedRecNumber
                     AND T.RN + 1 = T2.RN
    

**[Results][2]**:
    | MedRecNumber |   AcctNumber | DisYr | DisMo | LaceScore |                       LaceDate |                     TAdmitDate |                  DischargeDate |                    T2AdmitDate | ReAdmDays |
    |--------------|--------------|-------|-------|-----------|--------------------------------|--------------------------------|--------------------------------|--------------------------------|-----------|
    |   M000000206 | A00000263130 |  2015 |     1 |         5 | 01/11/2015                     | 01/11/2015                     | 01/14/2015                     | 12/27/2014                     |       -18 |
    |   M000001173 | A00000269416 |  2015 |     3 |         6 | 03/08/2015                     | 03/08/2015                     | 03/11/2015                     | 02/18/2015                     |       -21 |
    |   M000001173 | A00000267408 |  2015 |     2 |         2 | 02/20/2015                     | 02/18/2015                     | 02/26/2015                     | 01/05/2015                     |       -52 |
    |   M000001252 | A00000266432 |  2015 |     2 |        10 | 02/13/2015                     | 02/09/2015                     | 02/13/2015                     | 01/14/2015                     |       -30 |
    |   M000002322 | A00000266945 |  2015 |     2 |         9 | 02/14/2015                     | 02/13/2015                     | 02/20/2015                     | 01/15/2015                     |       -36 |
**Query 2**:

    
    
    
    
    
    
    SELECT TOP (100) PERCENT
        T.MedRecNumber
      , T.AcctNumber
      , T.DisYr
      , T.DisMo
      , T.LaceScore
      , T.LaceDate
      , T.AdmDate AS TAdmitDate
      , T.DischDate AS DischargeDate
      , CONVERT(char, T2.AdmDate, 101) AS T2AdmitDate
      , max(DATEDIFF(D, T.DischDate, T2.AdmDate)) AS ReAdmDays
    
    FROM dbo.vw_Lace_Discharge_Summary AS T
    LEFT OUTER JOIN dbo.vw_Lace_Discharge_Summary AS T2
        ON T.MedRecNumber = T2.MedRecNumber
        AND T.AdmDate < T2.AdmDate
    
    GROUP BY
        T.MedRecNumber
      , T.AcctNumber
      , T.DisYr
      , T.DisMo
      , T.LaceScore
      , T.LaceDate
      , T.AdmDate
      , T.DischDate
      , CONVERT(char, T2.AdmDate, 101)
    ORDER BY
        T.MedRecNumber
      , T.AcctNumber
      

**[Results][3]**:
    | MedRecNumber |   AcctNumber | DisYr | DisMo | LaceScore |                   LaceDate |                 TAdmitDate |              DischargeDate |                    T2AdmitDate | ReAdmDays |
    |--------------|--------------|-------|-------|-----------|----------------------------|----------------------------|----------------------------|--------------------------------|-----------|
    |   M000000166 | A00000261751 |  2014 |    12 |         4 | December, 30 2014 07:28:00 | December, 27 2014 00:00:00 | December, 30 2014 00:00:00 |                         (null) |    (null) |
    |   M000000206 | A00000261780 |  2014 |    12 |         4 | December, 28 2014 14:37:00 | December, 27 2014 00:00:00 | December, 31 2014 00:00:00 | 01/11/2015                     |        11 |
    |   M000000206 | A00000263130 |  2015 |     1 |         5 |  January, 11 2015 09:42:00 |  January, 11 2015 00:00:00 |  January, 14 2015 00:00:00 |                         (null) |    (null) |
    |   M000000291 | A00000268016 |  2015 |     3 |         2 | February, 28 2015 14:22:00 | February, 24 2015 00:00:00 |    March, 03 2015 00:00:00 |                         (null) |    (null) |
    |   M000000329 | A00000262006 |  2015 |     1 |         1 |  January, 01 2015 13:14:00 | December, 31 2014 00:00:00 |  January, 01 2015 00:00:00 |                         (null) |    (null) |
    |   M000000362 | A00000258661 |  2014 |    12 |         1 | December, 12 2014 14:21:00 | December, 11 2014 00:00:00 | December, 12 2014 00:00:00 |                         (null) |    (null) |
    |   M000000464 | A00000258668 |  2014 |    12 |         9 | December, 16 2014 14:46:00 | December, 15 2014 00:00:00 | December, 22 2014 00:00:00 |                         (null) |    (null) |
    |   M000000531 | A00000261814 |  2014 |    12 |         2 | December, 29 2014 11:29:00 | December, 28 2014 00:00:00 | December, 30 2014 00:00:00 |                         (null) |    (null) |
    |   M000000702 | A00000265624 |  2015 |     3 |         1 |    March, 04 2015 08:12:00 |    March, 03 2015 00:00:00 |    March, 06 2015 00:00:00 |                         (null) |    (null) |
    |   M000000963 | A00000263136 |  2015 |     1 |         4 |  January, 13 2015 10:11:00 |  January, 11 2015 00:00:00 |  January, 15 2015 00:00:00 |                         (null) |    (null) |
    |   M000001045 | A00000260533 |  2014 |    12 |         5 | December, 15 2014 12:08:00 | December, 14 2014 00:00:00 | December, 19 2014 00:00:00 |                         (null) |    (null) |
    |   M000001173 | A00000262444 |  2015 |     1 |         6 |  January, 09 2015 14:09:00 |  January, 05 2015 00:00:00 |  January, 16 2015 00:00:00 | 02/18/2015                     |        33 |
    |   M000001173 | A00000262444 |  2015 |     1 |         6 |  January, 09 2015 14:09:00 |  January, 05 2015 00:00:00 |  January, 16 2015 00:00:00 | 03/08/2015                     |        51 |
    |   M000001173 | A00000267408 |  2015 |     2 |         2 | February, 20 2015 13:52:00 | February, 18 2015 00:00:00 | February, 26 2015 00:00:00 | 03/08/2015                     |        10 |
    |   M000001173 | A00000269416 |  2015 |     3 |         6 |    March, 08 2015 14:48:00 |    March, 08 2015 00:00:00 |    March, 11 2015 00:00:00 |                         (null) |    (null) |
    |   M000001252 | A00000263559 |  2015 |     1 |         3 |  January, 15 2015 15:08:00 |  January, 14 2015 00:00:00 |  January, 18 2015 00:00:00 | 02/09/2015                     |        22 |
    |   M000001252 | A00000266432 |  2015 |     2 |        10 | February, 13 2015 15:12:00 | February, 09 2015 00:00:00 | February, 13 2015 00:00:00 |                         (null) |    (null) |
    |   M000001268 | A00000263918 |  2015 |     1 |         5 |  January, 20 2015 06:31:00 |  January, 18 2015 00:00:00 |  January, 22 2015 00:00:00 |                         (null) |    (null) |
    |   M000001457 | A00000258637 |  2014 |    12 |         1 | December, 12 2014 13:55:00 | December, 11 2014 00:00:00 | December, 12 2014 00:00:00 |                         (null) |    (null) |
    |   M000001989 | A00000261542 |  2015 |     1 |         3 |  January, 01 2015 12:16:00 | December, 30 2014 00:00:00 |  January, 04 2015 00:00:00 |                         (null) |    (null) |
    |   M000002216 | A00000260512 |  2015 |     1 |         6 |  January, 09 2015 14:15:00 | December, 13 2014 00:00:00 |  January, 30 2015 00:00:00 |                         (null) |    (null) |
    |   M000002322 | A00000263581 |  2015 |     1 |         2 |  January, 20 2015 14:29:00 |  January, 15 2015 00:00:00 |  January, 26 2015 00:00:00 | 02/13/2015                     |        18 |
    |   M000002322 | A00000266945 |  2015 |     2 |         9 | February, 14 2015 10:58:00 | February, 13 2015 00:00:00 | February, 20 2015 00:00:00 |                         (null) |    (null) |

  [1]: http://sqlfiddle.com/#!6/54196/7
  [2]: http://sqlfiddle.com/#!6/54196/7/0
  [3]: http://sqlfiddle.com/#!6/54196/7/1

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Vitor MontalvãoMSSQL Senior EngineerCommented:
GPSPOW, do you still need help on this question?
GPSPOWAuthor Commented:
I've requested that this question be deleted for the following reason:

Thanks to all who responded.

I reworked the solution with a Rank() function which is working now.

Glen
PortletPaulEE Topic AdvisorCommented:
whether you use RANK() or ROW_NUMBER() the technique is the same....

row_number is more reliable for this need.
it is possible to rank() to allocate multiple rows the same value which is not possible with row_number()
GPSPOWAuthor Commented:
I was able to create my query from this article.

Thanks

Glen
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.