Unable to get the required result using update statement

Question

Post reply

Unable to get the required result using update statement

nagendra2509

SSC Rookie

Points: 42
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: 286562 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 SSC Rookie Points: 42 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

Sachin Nandanwar

SSCertifiable

Points: 7699

June 18, 2014 at 8:43 am

#1722831

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 SSC Rookie Points: 42 More actions · Answer 4

nagendra2509

SSC Rookie

Points: 42

June 19, 2014 at 1:49 am

#1723091

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