June 25, 2013 at 8:04 am
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??
June 25, 2013 at 8:08 am
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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 25, 2013 at 8:08 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 25, 2013 at 8:47 am
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
June 25, 2013 at 12:53 pm
I have added more details that I hope will clearly explain what it is I am looking to gather... Thank you!
June 25, 2013 at 12:55 pm
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/
June 25, 2013 at 1:05 pm
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
June 25, 2013 at 1:39 pm
The tables are views and scripting out the create to would not work here... Should I create a custom one for this purpose?
June 25, 2013 at 1:53 pm
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/
July 9, 2013 at 7:56 am
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'
July 9, 2013 at 8:07 am
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/
July 9, 2013 at 8:17 am
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.
July 9, 2013 at 8:38 am
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