June 29, 2015 at 3:33 pm
Hello-
I've been struggling with this for some time. we have to group data based on Patients admission date and discharge date. If any Patients discharge date + 1 = admission date then we have group both rows into one row and sum costs from both the rows. Please check out the sample input and expected output for details. Any help will be highly appreciated
Sample Input
PatientID AdmissionDate DischargeDate Cost
1009 27-07-2014 31-07-2014 1050
1009 01-08-2014 23-08-2014 1070
1009 31-08-2014 31-08-2014 1900
1009 01-09-2014 14-09-2014 1260
1009 01-12-2014 31-12-2014 2090
1024 07-06-2014 28-06-2014 1900
1024 29-06-2014 31-07-2014 2900
1024 01-08-2014 02-08-2014 1800
Expected Output
PatientId AdminssionDate DischargeDate Cost
1009 27-07-2014 23-08-2014 2120
1009 31-08-2014 14-09-2014 3160
1009 01-12-2014 31-12-2014 2090
1024 07-06-2014 02-08-2014 6600
Please Use the below script to generate the source table and fill them up with the sample data.
--Create Table
CREATE TABLE PatientProblem
(
PatientID INT,
AdmissionDate DATETIME,
DischargeDate DATETIME,
Cost MONEY
)
GO
--Insert Data
INSERT INTO PatientProblem(PatientID,AdmissionDate,DischargeDate
,Cost)
VALUES
(1009,'2014-07-27','2014-07-31',1050.00),
(1009,'2014-08-01','2014-08-23',1070.00),
(1009,'2014-08-31','2014-08-31',1900.00),
(1009,'2014-09-01','2014-09-14',1260.00),
(1009,'2014-12-01','2014-12-31',2090.00),
(1024,'2014-06-07','2014-06-28',1900.00),
(1024,'2014-06-29','2014-07-31',2900.00),
(1024,'2014-08-01','2014-08-02',1800.00)
--Verify Data
SELECT PatientID,AdmissionDate,DischargeDate,Cost
FROM PatientProblem
June 29, 2015 at 4:20 pm
Try this.
WITH TEMP_CTE AS(
SELECT PAT_ONE.PatientID, PAT_ONE.AdmissionDate, PAT_ONE.DischargeDate, PAT_ONE.Cost
FROM PatientProblem PAT_ONE
LEFT OUTER JOIN PatientProblem PAT_TWO ON PAT_ONE.PatientId = PAT_TWO.PatientID AND PAT_ONE.DischargeDate = DATEADD(day, -1, PAT_TWO.AdmissionDate)
WHERE
PAT_TWO.PatientID IS NULL
UNION ALL
SELECT PAT_TWO.PatientId, PAT_TWO.AdmissionDate, TEMP_CTE.DischargeDate, PAT_TWO.Cost + TEMP_CTE.Cost
FROM PatientProblem PAT_TWO, TEMP_CTE
WHERE
PAT_TWO.PatientId = TEMP_CTE.PatientId AND TEMP_CTE.AdmissionDate = DATEADD(day, 1, PAT_TWO.DischargeDate)
),
CTE_TWO AS
(SELECT *, ROW_NUMBER() OVER(PARTITION BY PatientId, DischargeDate ORDER BY AdmissionDate ASC) ROW_NUM FROM TEMP_CTE)
SELECT * FROM CTE_TWO
WHERE ROW_NUM = 1
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply