November 2, 2016 at 6:21 pm
I've been trying to use dynamic unpivot and pivot (thanks to some code examples from someone named "bluefoot" on stackoverflow that was posted in answer to a question three years ago) to solve the problem I have with trying to create a T-SQL query that will ultimately provide counts for patients readmitted, patients readmitted within 30 days, # days from hospital discharge to readmission date, number of patients readmitted within 30 days who had a PCP follow-up appointment within 7-14-21-30 days, and # ED admits after hospital discharge.
The incoming data has admit, discharge, deceased, and transferred separate rows. The [Visit ID] column value "ties" the admits and discharges together. As in most health care data, there are admits without discharges (could be fine if patient just hasn't been discharged yet but, the again, the discharge data could be missing forever). There can also be discharges without a corresponding admit row. The data is a combination of normalized and denormalized. The vendor won't change how the data is exported. The status column containes "Admitted", "Discharged", "Deceased", or "Transferred". The [Status Date] column contains the date that corresponds to the status event. There are some columns that pertain to either admissions only or discharges only. These are: "Discharged Disposition", "Discharge Location", "Admitted From", "Admit Care Coordinator", and "Discharge Care Coordinator". I would like to carry all columns in the following table into the results but obviously I need to pivot the admit and discharge columns so that I can make the calculations I described above.
I use this table for the data exported from another application:
USE [MPPPPDManagement]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[WorkingTableTest](
[Facility] [varchar](45) NULL,
[Facility Type] [varchar](3) NULL,
[Visit ID] [bigint] NULL,
[Status] [varchar](11) NULL,
[Status Date] [date] NULL,
[Setting] [varchar](25) NULL,
[Patient ID] [bigint] NULL,
[Last Name] [varchar](150) NULL,
[First Name] [varchar](150) NULL,
[DOB] [date] NULL,
[Patient Phone Number] [varchar](12) NULL,
[Health Insurance Number] [varchar](255) NULL,
[Health Insurance Type] [varchar](255) NULL,
[Practice] [varchar](150) NULL,
[Provider] [varchar](150) NULL,
[Program] [varchar](150) NULL,
[Admitted From] [varchar](25) NULL,
[Discharged Disposition] [varchar](33) NULL,
[Discharge Location] [varchar](50) NULL,
[Admit Care Coordinator] [varchar](150) NULL,
[Discharge Care Coordinator] [varchar](150) NULL,
[Ping Active] [varchar](1) NULL,
[Entry Delay] [bigint] NULL,
[Ping Resolution Status] [varchar](8) NULL,
[Visit Duration (days)] [bigint] NULL,
[Active Roster Patient] [varchar](1) NULL
) ON [PRIMARY]
GO
Here is the code I customized from the example above (please see hyperlink) that doesn't quite get me to my end goal because I've added all the columns from my table, which causes the results to be in separate admit and discharge rows again.
select [Patient ID],
[Visit ID],
Facility, [Facility Type], [Visit ID], [Setting], [Last Name], [First Name], [DOB], [Patient Phone Number], [Health Insurance Number], [Health Insurance Type], [Practice],[Program], [Admitted From], [Discharged Disposition], [Discharge Location], [Admit Care Coordinator], [Discharge Care Coordinator], [Ping Active], [Entry Delay], [Ping Resolution Status], [Visit Duration (days)], [Active Roster Patient],
[status]+'_'+col col,
value,
rn
from
(
select [Patient ID],
[Visit ID],
Facility, [Facility Type], [Setting], [Last Name], [First Name], [DOB], [Patient Phone Number], [Health Insurance Number], [Health Insurance Type], [Practice],[Program], [Admitted From], [Discharged Disposition], [Discharge Location], [Admit Care Coordinator], [Discharge Care Coordinator], [Ping Active], [Entry Delay], [Ping Resolution Status], [Visit Duration (days)], [Active Roster Patient],
convert(varchar(100), [Status Date], 101) [Status Date],
cast(provider as varchar(100)) provider,
[status],
row_number() over(partition by [Visit ID], [status], Facility, [Facility Type], [Setting], [Last Name], [First Name], [DOB], [Patient Phone Number], [Health Insurance Number], [Health Insurance Type], [Practice],[Program], [Admitted From], [Discharged Disposition], [Discharge Location], [Admit Care Coordinator], [Discharge Care Coordinator], [Ping Active], [Entry Delay], [Ping Resolution Status], [Visit Duration (days)], [Active Roster Patient]
order by case [status] when 'Admit' then 1 end, [Status Date]) rn
from WorkingTableTest
) d
unpivot
(
value
for col in ([Status Date], provider)
) unpiv
--Now pivot:
select [Patient ID], [Visit ID],
Facility, [Facility Type], [Setting], [Last Name], [First Name], [DOB], [Patient Phone Number], [Health Insurance Number], [Health Insurance Type], [Practice],[Program], [Admitted From], [Discharged Disposition], [Discharge Location], [Admit Care Coordinator], [Discharge Care Coordinator], [Ping Active], [Entry Delay], [Ping Resolution Status], [Visit Duration (days)], [Active Roster Patient],
[Admitted_Status Date], Admitted_provider, [Discharged_Status Date], Discharged_provider
from
(
select [Patient ID],
[Visit ID],
Facility, [Facility Type], [Setting], [Last Name], [First Name], [DOB], [Patient Phone Number], [Health Insurance Number], [Health Insurance Type], [Practice],[Program], [Admitted From], [Discharged Disposition], [Discharge Location], [Admit Care Coordinator], [Discharge Care Coordinator], [Ping Active], [Entry Delay], [Ping Resolution Status], [Visit Duration (days)], [Active Roster Patient],
[status]+'_'+col col,
value,
rn
from
(
select [Patient ID],
[Visit ID],
Facility, [Facility Type], [Setting], [Last Name], [First Name], [DOB], [Patient Phone Number], [Health Insurance Number], [Health Insurance Type], [Practice],[Program], [Admitted From], [Discharged Disposition], [Discharge Location], [Admit Care Coordinator], [Discharge Care Coordinator], [Ping Active], [Entry Delay], [Ping Resolution Status], [Visit Duration (days)], [Active Roster Patient],
convert(varchar(100), [Status Date], 101) [Status Date],
cast(provider as varchar(100)) provider,
[status],
row_number() over(partition by [Visit ID], [status]
order by case [status] when 'Admit' then 1 end, [Status Date]) rn
from WorkingTableTest
) d
unpivot
(
value
for col in ([Status Date], provider)
) unpiv
) src
pivot
(
max(value)
for col in ([Admitted_Status Date], Admitted_provider, [Discharged_Status Date], Discharged_provider)
) piv;
I would also like to filter out any data that doesn't have 'admitted' or 'discharged' in the status column but I tried various locations for a where clause like [status] in ('Admitted', 'Discharged') but I'm not quite sure where to put it or if I need to do it another way.
Thanks in advance if you're still reading.
November 3, 2016 at 9:43 am
This looks like actual patient data. If so, it is a serious HIPAA violation. We don't need the name and phone number to solve your issue and it should not have been included in the sample data. If you did want to include it anyhow, you should have anonymized it, such as replacing it with obviously fake data like the names of characters from the Harry Potter books/movies.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 3, 2016 at 10:20 am
I think that UNPIVOT/PIVOT is overkill for this particular situation. I was able to get very close to your results using four simple CASE expressions.
SELECT *
,CASE WHEN wtt.Status = 'Admitted' THEN wtt.[Status Date] END AS Admitted_Status_date
,CASE WHEN wtt.Status = 'Admitted' THEN wtt.Provider END AS Admitted_provider
,CASE WHEN wtt.Status = 'Discharged' THEN wtt.[Status Date] END AS Discharged_Status_date
,CASE WHEN wtt.Status = 'Discharged' THEN wtt.Provider END AS Discharged_provider
FROM #WorkingTableTest wtt
WHERE wtt.Status IN ('Admitted', 'Discharged')
ORDER BY wtt.[Patient ID], wtt.[Visit ID]
Also, you can use the windowed functions in conjunction with these CASE statements to find the previous admission data to determine whether it falls within the 30 day window.
WITH patient_data AS (
SELECT *
,CASE WHEN wtt.Status = 'Admitted' THEN wtt.[Status Date] END AS Admitted_Status_date
,CASE WHEN wtt.Status = 'Admitted' THEN wtt.Provider END AS Admitted_provider
,CASE WHEN wtt.Status = 'Discharged' THEN wtt.[Status Date] END AS Discharged_Status_date
,CASE WHEN wtt.Status = 'Discharged' THEN wtt.Provider END AS Discharged_provider
FROM #WorkingTableTest wtt
WHERE wtt.Status IN ('Admitted', 'Discharged')
ORDER BY wtt.[Patient ID], wtt.[Visit ID]
)
SELECT *, MAX(p.Admitted_Status_date) OVER(PARTITION BY [Patient ID] ORDER BY [Status Date] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) END AS prev_admitted_dt
FROM patient_data p
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 3, 2016 at 11:39 am
This is absolutely not patient data (real names, real DOB, etc.). But as it appears that way I will delete it now that I have a solution. Thanks.
November 3, 2016 at 11:41 am
Thanks very much drew.allen! This is what I needed.
November 3, 2016 at 1:00 pm
Thanks again but I have a question about the second query with the WITH CTE: When I execute it I get a couple of errors. The first one says the ORDER BY clause is invalid in views, inline functions, derived tables, and common table expressions, unless TOP, OFFSET or FOR XML is also specified (Msg 1033, Level 15, line {the line number below the ORDER BY clause where the closing parenthesis is. The second error is "incorrect syntax near 'prev_admitted_dt I tried removing the 'AS' after the END before prev_admitted_dt. Before I did that the error was 'Incorrect syntax near 'prev_admitted_dt'. I am running the WITH CTE as a separate step from the first code because I didn't use temporary tables if that matters at all.
November 3, 2016 at 2:00 pm
Yes, I forgot to move the ORDER BY clause to the main query when I copied the first query to create the second query.
There is an extra END in the line for prev_admitted_dt. I'm not sure how it got in there.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 3, 2016 at 2:22 pm
Thank you! I didn't know about UNBOUNDED AND PRECEDING.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply