Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

select where patient has more than 1 provider Expand / Collapse
Author
Message
Posted Tuesday, June 25, 2013 8:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 219, Visits: 725
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??
Post #1467167
Posted Tuesday, June 25, 2013 8:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:49 AM
Points: 2,674, Visits: 4,739
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/
Post #1467173
Posted Tuesday, June 25, 2013 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:18 PM
Points: 5,189, Visits: 12,053
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1467174
Posted Tuesday, June 25, 2013 8:47 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:34 AM
Points: 1,917, Visits: 19,613
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
Post #1467209
Posted Tuesday, June 25, 2013 12:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 219, Visits: 725
I have added more details that I hope will clearly explain what it is I am looking to gather... Thank you!
Post #1467324
Posted Tuesday, June 25, 2013 12:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 13,093, Visits: 12,573
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1467325
Posted Tuesday, June 25, 2013 1:05 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:34 AM
Points: 1,917, Visits: 19,613
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
Post #1467331
Posted Tuesday, June 25, 2013 1:39 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 219, Visits: 725
The tables are views and scripting out the create to would not work here... Should I create a custom one for this purpose?
Post #1467356
Posted Tuesday, June 25, 2013 1:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 13,093, Visits: 12,573
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1467369
Posted Tuesday, July 9, 2013 7:56 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 219, Visits: 725
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'
Post #1471634
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse