Unable to get the required result using update statement

Question

Post reply

Unable to get the required result using update statement

nagendra2509

Valued Member

Points: 52
More actions
June 18, 2014 at 7:10 am

#287004

Dear All,
I am unable to update the data using record by record below scenario.
Required output: patient will able to Admit/Re-admit multiple times in hospital, if a patient readmitted multiple times in hospital after the first visit, first visit record will get Re-admission=0 and Index=1. This visit should cal Index_Admission of that patient. using this index_admission should calculate the 30-day readmission.
Current Output:
Calculation: From index_admission discharge date to next admit_visit date,
1) if the diff is having less than 30 days, readmission=1 and Index=0
else readmission=0 and Index=1 should be update.
For checking this every time should check using the latest index_admission discharge_date.
To get this result i written below logic, but it's updating readmission=0 and Index=1 after 30-day post discharge of using first index admission.
UPDATE Readmission
SET Index_AMI = (CASE WHEN DATEDIFF(DD, (SELECT Sub.Max_Index_Dis FROM
(SELECT Patient_ID, MAX(Discharge_Date_Time) Max_Index_Dis FROM Readmission
WHERE Index_AMI = 1 AND FPR.Patient_ID = Patient_ID GROUP BY Patient_ID) Sub)
, FPR.Admit_Date_Time) between 0 and 31 THEN 0 ELSE 1 END),
Is_AMI_Readmission = (CASE WHEN DATEDIFF(DD, (SELECT Sub.Max_Index_Dis FROM
(SELECT Patient_ID, MAX(Discharge_Date_Time) Max_Index_Dis FROM Readmission
WHERE Index_AMI = 1 AND FPR.Patient_ID = Patient_ID GROUP BY Patient_ID) Sub)
, FPR.Admit_Date_Time) between 0 and 31 THEN 1 ELSE 0 END)
FROM Readmission FPR
WHERE fpr.index_ami IS NULL
Expected Result:

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Hi and welcome to the forums. Your images did not upload correctly.

In order to help we will need a few things:

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

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

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/

nagendra2509 Valued Member Points: 52 More actions · Answer 2

Hi,

I have attached Current output and Expected Output screen shots in below. please check this and help me to resolve.

Table Structure and Sample Data:

Create Table #Patient_Readmission

(Patent_Code int,

Encounter_Code int,

Admit_Date_Time datetime,

Discharge_Date_Time datetime,

Is_AMI_Readmission bit,

Index_AMI bit)

GO

insert into #Patient_Readmission(Patient_Code,Encounter_Code,Admit_Date_Time,Discharge_Date_Time)

select 13282,10591,'2013-02-10 09:06:00.600','2013-02-15 09:06:00.600'

Union ALL

select 13282,8967,'2013-03-03 07:35:00.350','2013-03-08 07:35:00.350'

Union ALL

select 13282,9829,'2013-03-14 19:40:00.400','2013-03-21 19:40:00.400'

Union ALL

select 19015,15202,'2013-03-20 19:55:00.550','2013-03-25 19:55:00.550'

Union ALL

select 19015,16180,'2013-05-07 22:42:00.420','2013-05-13 22:42:00.420'

Union ALL

select 19015,16702,'2013-05-16 00:44:00.440','2013-05-22 00:44:00.440'

Union ALL

select 19015,15924,'2013-05-25 13:43:00.430','2013-05-27 13:43:00.430'

Union ALL

select 13282,7272,'2013-05-26 05:42:00.420','2013-06-05 05:42:00.420'

Union ALL

select 19015,15764,'2013-05-29 17:50:00.500','2013-06-02 17:50:00.500'

Union ALL

select 19015,16260,'2013-06-28 05:33:00.330','2013-07-02 05:33:00.330'

Union ALL

select 19015,16148,'2013-07-15 22:30:00.300','2013-07-23 22:30:00.300'

Union ALL

select 13282,9884,'2014-03-02 02:57:00.570','2014-03-12 02:57:00.570'

Union ALL

select 13282,8313,'2014-03-25 21:27:00.270','2014-03-30 21:27:00.270'

Union ALL

select 13282,8454,'2014-11-28 20:15:00.150',NULL

Regards,

Nagendra

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 3

Why should the value of Index_AMI be 1 for encounter id=16260 though the previous discharge date is less than 30 days of the admit date ?

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

nagendra2509 Valued Member Points: 52 More actions · Answer 4

Sorry.....

For that encounter Index_AMI should get "0" and Is_Readmission should get "1"

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 5

CREATE TABLE #patient_readmission

(

id INT IDENTITY,

patient_code INT,

encounter_code INT,

admit_date_time DATETIME,

discharge_date_time DATETIME,

is_ami_readmission BIT,

index_ami BIT

)

go

INSERT INTO #patient_readmission

(patient_code,

encounter_code,

admit_date_time,

discharge_date_time)

SELECT 13282,

10591,

'2013-02-10 09:06:00.600',

'2013-02-15 09:06:00.600'

UNION ALL

SELECT 13282,

8967,

'2013-03-03 07:35:00.350',

'2013-03-08 07:35:00.350'

UNION ALL

SELECT 13282,

9829,

'2013-03-14 19:40:00.400',

'2013-03-21 19:40:00.400'

UNION ALL

SELECT 19015,

15202,

'2013-03-20 19:55:00.550',

'2013-03-25 19:55:00.550'

UNION ALL

SELECT 19015,

16180,

'2013-05-07 22:42:00.420',

'2013-05-13 22:42:00.420'

UNION ALL

SELECT 19015,

16702,

'2013-05-16 00:44:00.440',

'2013-05-22 00:44:00.440'

UNION ALL

SELECT 19015,

15924,

'2013-05-25 13:43:00.430',

'2013-05-27 13:43:00.430'

UNION ALL

SELECT 13282,

7272,

'2013-05-26 05:42:00.420',

'2013-06-05 05:42:00.420'

UNION ALL

SELECT 19015,

15764,

'2013-05-29 17:50:00.500',

'2013-06-02 17:50:00.500'

UNION ALL

SELECT 19015,

16260,

'2013-06-28 05:33:00.330',

'2013-07-02 05:33:00.330'

UNION ALL

SELECT 19015,

16148,

'2013-07-15 22:30:00.300',

'2013-07-23 22:30:00.300'

UNION ALL

SELECT 13282,

9884,

'2014-03-02 02:57:00.570',

'2014-03-12 02:57:00.570'

UNION ALL

SELECT 13282,

8313,

'2014-03-25 21:27:00.270',

'2014-03-30 21:27:00.270'

UNION ALL

SELECT 13282,

8454,

'2014-11-28 20:15:00.150',

NULL

SELECT patient_code,

encounter_code,

admit_date_time,

discharge_date_time,

CASE

WHEN ind IS NULL

OR ind > 30 THEN 1

ELSE 0

END New_Index_AMI

FROM (SELECT T.*,

p.*,

Datediff(dd, T.dis, p.discharge_date_time)IND

FROM #patient_readmission p

OUTER APPLY(SELECT TOP 1 discharge_date_time DIS

FROM #patient_readmission r

WHERE p.patient_code = r.patient_code

AND p.id = r.id + 1

ORDER BY patient_code)T)T1

DROP TABLE #patient_readmission

Based on the above logic now you can try formulating the value for Is_AMI_Readmission.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server