select where patient has more than 1 provider

  • Can someone show me how to select all patients that hasve seen more than one provider over the past month please? Would this be a cte?

    Additional Info Update:

    We would like the results to look like this...

    Patient Name | Specialty | Total # of Visits in past 30 days | Visit Date | Provider Name | Event ID

    Smith | PT | 3 | 5/19/2013 | Dr. Gray | 005

    Smith | PT | 3 | 5/16/2013 | Dr. Jackson | 003

    Smith | PT | 3 | 5/17/2013 | Dr. Ellen | 004

    In the event that Patient Smith saw the same provider each time then we do not want to see those results since that is what we want. We are trying to improve of continuity of care by identifying patients who are seeing different providers for the same issue. So something like this is fine and we wouldn't want to see these results.

    Patient Name | Specialty | Total # of Visits in past 30 days | Visit Date | Provider Name

    Smith | PT | 3 | 5/1/2013 | Dr. Gray

    Smith | PT | 3 | 5/12/2013 | Dr. Jackson

    Smith | PT | 3 | 5/17/2013 | Dr. Ellen

    I will be updating this thread with the Tables soon. Thank you!

    Ok back on track...

    I have two Views to join that should do the trick... However one of the views I need to run a select distinct on since it looks like this... and I can only join on EventID

    VProvider A

    ChargeHistoryID (Pkey, UniqID) | EventID (UniqID) | ProviderName | ProviderSpecialty

    01234 | 001 | Dr.Gray | PT

    43210 | 001 | Dr.Gray | PT

    43212 | 002 | Dr.Brown | PT

    43215 | 003 | Dr.Jackson | OT

    43217 | 004 | Dr.Ellen | PT

    43219 | 005 | Dr.Gray | PT

    VEvent B

    EventID (UniqID) | PatientName | EventDate | EventName | Facility

    001 | Cooper | 05/10/13 | Follow Up | 0090

    002 | Martin | 05/15/13 | Follow Up | 0090

    003 | Smith | 05/16/13 | Follow Up |0090

    004 | Smith | 05/17/13 | Follow Up |0090

    005 | Smith | 05/19/13 | Follow Up | 0090

    A normal distinct join would return:

    001 |Dr.Gray | PT |Cooper | 05/10/13 | Follow Up | 0090

    002 |Dr.Brown|PT |Martin | 05/15/13 | Follow Up | 0090

    003 |Dr.Jackson|OT|Smith |05/16/13|Follow Up | 0090

    004 |Dr.Ellen | PT |Smith | 05/17/13 |Follow Up |0090

    005 |Dr.Gray | PT |Smith | 05/19/13 |Follow Up |0090

    But what I really want to see is Patient Cooper who saw three different Providers within a 1 month time period. Like this...

    Patient Name | Specialty | Total # of Visits in past 30 days | Visit Date | Provider Name | Event ID

    Smith | PT | 3 | 5/19/2013 | Dr. Gray | 005

    Smith | PT | 3 | 5/16/2013 | Dr. Jackson | 003

    Smith | PT | 3 | 5/17/2013 | Dr. Ellen | 004

    Is this possible??

  • We would definitely need more information to help you

    Please explain the issue a bit more and also provide the DDL of the tables involved, some sample data and the expected results based on the sample data

    If you are not sure about how to do this, please check the link in my signature


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Vertigo44 (6/25/2013)


    Can someone show me how to select all patients that hasve seen more than one provider over the past month please? Would this be a cte?

    You're not new here, so you should know that we require more from you than a plain English description of what you want which assumes that we magically understand your database's DDL.

    Please read the link in my signature - it describes how to post questions of this nature to get the best responses.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • as we have nothing to base code on...then here is a very simple view to get you started

    SELECT TOP 1000

    CustomerID = CAST(Abs(Checksum(Newid()) % 9000 + 1) AS INT),

    ProvID = CAST(Abs(Checksum(Newid()) % 90 + 1) AS INT),

    TransDate = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2013-05-01', getdate()), '2013-05-01')

    INTO #D

    FROM sys.all_columns ac1

    CROSS JOIN sys.all_columns ac2

    CROSS JOIN sys.all_columns ac3

    SELECT CustomerID

    FROM #D

    WHERE (TransDate > GETDATE() - 32)

    GROUP BY CustomerID

    HAVING (COUNT(ProvID) > 1)

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • I have added more details that I hope will clearly explain what it is I am looking to gather... Thank you!

  • Vertigo44 (6/25/2013)


    I have added more details that I hope will clearly explain what it is I am looking to gather... Thank you!

    You have been around here long enough to know that what you posted is not consumable ddl and sample data. Turn that into create table statements and insert statements and you will be rewarded with a number of people jumping in to help you.

    _______________________________________________________________

    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/

  • as you say...."I will be updating this thread with the Tables soon. Thank you!"

    hopefully with the requested data scripts...until then...........

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • The tables are views and scripting out the create to would not work here... Should I create a custom one for this purpose?

  • Vertigo44 (6/25/2013)


    The tables are views and scripting out the create to would not work here... Should I create a custom one for this purpose?

    That is probably your best bet. Without something to code against it is pretty tough to do this stuff. 😀

    _______________________________________________________________

    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/

  • Ok I have finally had time to circle back around to this query. In my attempts to follow the Forum Etiquette thread I ran into a slight problem in that my unique ID column is not of type INT. My unique id column is an Event_ID. I am getting an error saying that

    "Table '#vEvent' does not have the identity property. Cannot perform SET operation." and if I try and skip the SET operation I get this error.

    "Conversion failed when converting from a character string to uniqueidentifier." Thank you!

    CREATE TABLE #vEvent

    (

    [EventID] [uniqueidentifier] NOT NULL,

    [PatientId] [uniqueidentifier] NOT NULL,

    [PatientLastName] [varchar](50) NOT NULL,

    [PatientFirstName] [varchar](50) NOT NULL,

    [PatientMRN] [varchar](20) NULL,

    [EventDate] [datetime] NOT NULL,

    [EventDescription] [varchar](100) NULL,

    [FacilityName] [varchar](100) NOT NULL

    )

    SET IDENTITY_INSERT #vEvent ON

    INSERT INTO #vEvent

    (EventID, PatientId, PatientLastName, PatientFirstName, PatientMRN, EventDate, EventDescription, FacilityName)

    SELECT '69D917F8-15EF-4FBB-B615-752853BE32C5','7B191670-BE35-4685-B092-39A72E3621F2','RIGGS','ELLIS','000544249','Mar 23 2012 12:00AM','Follow-up 45 min','WMC - Cork' UNION ALL

    SELECT 'DF9796A1-2045-4FAF-AEBF-B31072B8F788','7B191670-BE35-4685-B092-39A72E3621F2','RIGGS','ELLIS','000544249','Mar 30 2012 12:00AM','Follow-up Visit','WMC - Cork' UNION ALL

    SELECT 'AF5156BD-4033-49D7-A215-30A3ECCD155B','7B191670-BE35-4685-B092-39A72E3621F2','RIGGS','ELLIS','000544249','Apr 4 2012 12:00AM','Follow-up 45 min','WMC - Cork' UNION ALL

    SELECT 'D210E82D-D2C9-46FB-AFC4-C7286CA0A97B','7B191670-BE35-4685-B092-39A72E3621F2','RIGGS','ELLIS','000544249','Apr 6 2012 12:00AM','Follow-up 45 min','WMC - Cork' UNION ALL

    SELECT 'CDCC47C7-BEA4-4736-8ED8-D23BC797DD32','7B191670-BE35-4685-B092-39A72E3621F2','RIGGS','ELLIS','000544249','Apr 11 2012 12:00AM','Follow-up 45 min','WMC - Cork' UNION ALL

    SELECT '94BF7693-696B-4AB0-8F15-A682A742C17B','7B191670-BE35-4685-B092-39A72E3621F2','RIGGS','ELLIS','000544249','Apr 13 2012 12:00AM','Follow-up 45 min','WMC - Cork' UNION ALL

    SELECT '29FE3A60-18C4-4CE6-B819-529F85F7BF1E','7B191670-BE35-4685-B092-39A72E3621F2','RIGGS','ELLIS','000544249','Apr 20 2012 12:00AM','Follow-up 45 min','WMC - Cork' UNION ALL

    SELECT '773ACB33-C830-40E1-87DC-058ACF2E61GE','7B191670-BE35-4685-B092-39A72E3621F2','RIGGS','ELLIS','000544249','Apr 25 2012 12:00AM','Follow-up 45 min','WMC - Cork' UNION ALL

    SELECT 'A2E85417-DFB0-4292-A3F4-FC7ED04F1672','7B191670-BE35-4685-B092-39A72E3621F2','RIGGS','ELLIS','000544249','May 2 2012 12:00AM','5/2/2012','WMC - Cork' UNION ALL

    SELECT '69D917F8-15EF-4FBB-B615-752853BE32A3','7B191670-BE35-4685-B092-39A72E3621F2','REX','WOOD','000544249','Mar 23 2012 12:00AM','Follow-up 45 min','WMC - Cork' UNION ALL

    SELECT 'DF9796A1-2045-4FAF-AEBF-B31072B8F766','7B191670-BE35-4685-B092-39A72E3621F2','REX','WOOD','000544249','Mar 30 2012 12:00AM','Follow-up Visit','WMC - Cork' UNION ALL

    SELECT 'AF5156BD-4033-49D7-A215-30A3ECCD153Z','7B191670-BE35-4685-B092-39A72E3621F2','REX','WOOD','000544249','Apr 4 2012 12:00AM','Follow-up 45 min','WMC - Cork' UNION ALL

    SELECT 'D210E82D-D2C9-46FB-AFC4-C7286CA0A95Z','7B191670-BE35-4685-B092-39A72E3621F2','REX','WOOD','000544249','Apr 6 2012 12:00AM','Follow-up 45 min','WMC - Cork' UNION ALL

    SELECT 'CDCC47C7-BEA4-4736-8ED8-D23BC797DD10','7B191670-BE35-4685-B092-39A72E3621F2','REX','WOOD','000544249','Apr 11 2012 12:00AM','Follow-up 45 min','WMC - Cork' UNION ALL

    SELECT '94BF7693-696B-4AB0-8F15-A682A742C15Z','7B191670-BE35-4685-B092-39A72E3621F2','REX','WOOD','000544249','Apr 13 2012 12:00AM','Follow-up 45 min','WMC - Cork' UNION ALL

    SELECT '29FE3A60-18C4-4CE6-B819-529F85F7BFNC','7B191670-BE35-4685-B092-39A72E3621F2','REX','WOOD','000544249','Apr 20 2012 12:00AM','Follow-up 45 min','WMC - Cork' UNION ALL

    SELECT '773ACB33-C830-40E1-87DC-058ACF2E61EC','7B191670-BE35-4685-B092-39A72E3621F2','REX','WOOD','000544249','Apr 25 2012 12:00AM','Follow-up 45 min','WMC - Cork' UNION ALL

    SELECT 'A2E85417-DFB0-4292-A3F4-FC7ED04F1650','7B191670-BE35-4685-B092-39A72E3621F2','REX','WOOD','000544249','May 2 2012 12:00AM','5/2/2012','WMC - Cork'

  • Vertigo44 (7/9/2013)


    Ok I have finally had time to circle back around to this query. In my attempts to follow the Forum Etiquette thread I ran into a slight problem in that my unique ID column is not of type INT. My unique id column is an Event_ID. I am getting an error saying that

    "Table '#vEvent' does not have the identity property. Cannot perform SET operation." and if I try and skip the SET operation I get this error.

    "Conversion failed when converting from a character string to uniqueidentifier." Thank you!

    So basically you came back with a table and no data? And none of the data looks even remotely like your original post? I would be willing to help you but you have to provide enough information to allow us to help.

    _______________________________________________________________

    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/

  • Sean Lange (7/9/2013)


    Vertigo44 (7/9/2013)


    Ok I have finally had time to circle back around to this query. In my attempts to follow the Forum Etiquette thread I ran into a slight problem in that my unique ID column is not of type INT. My unique id column is an Event_ID. I am getting an error saying that

    "Table '#vEvent' does not have the identity property. Cannot perform SET operation." and if I try and skip the SET operation I get this error.

    "Conversion failed when converting from a character string to uniqueidentifier." Thank you!

    So basically you came back with a table and no data? And none of the data looks even remotely like your original post? I would be willing to help you but you have to provide enough information to allow us to help.

    ?? My data is in my previous post. I need help getting the data into that temp table like Jeff's thread describes. Jeffs thread uses an ID column of type int. That is not the case with my data. So if I were able to get my data into that one table I could then do it for the other table and post the code to create both temp tables and fill both temp tables with the data you need to see what i need.

  • Vertigo44 (7/9/2013)


    Sean Lange (7/9/2013)


    Vertigo44 (7/9/2013)


    Ok I have finally had time to circle back around to this query. In my attempts to follow the Forum Etiquette thread I ran into a slight problem in that my unique ID column is not of type INT. My unique id column is an Event_ID. I am getting an error saying that

    "Table '#vEvent' does not have the identity property. Cannot perform SET operation." and if I try and skip the SET operation I get this error.

    "Conversion failed when converting from a character string to uniqueidentifier." Thank you!

    So basically you came back with a table and no data? And none of the data looks even remotely like your original post? I would be willing to help you but you have to provide enough information to allow us to help.

    ?? My data is in my previous post. I need help getting the data into that temp table like Jeff's thread describes. Jeffs thread uses an ID column of type int. That is not the case with my data. So if I were able to get my data into that one table I could then do it for the other table and post the code to create both temp tables and fill both temp tables with the data you need to see what i need.

    Your #vEvent inserts have invalid data. Some of your GUIDs are invalid. Please go back to your original post and turn all of that into something that is readily consumable.

    1. Sample DDL in the form of CREATE TABLE statements

    2. Sample data in the form of INSERT INTO statements

    3. Expected results based on the sample data

    _______________________________________________________________

    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/

Viewing 13 posts - 1 through 12 (of 12 total)

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