T-SQL Query | [ The Patient Puzzle ]  – In this puzzle 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.

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

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

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 ```

Please leave a comment if you need solution to the above puzzle

Keep Learning