Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, SSRS, SSRS Interview Questions, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
Improving my SQL BI Skills 
;WITH UPP_CTE AS(
SELECT PatientID,AdmissionDate,DischargeDate,Cost ,ROW_NUMBER() OVER (partition by patientid ORDER BY AdmissionDate) AS Rnk
FROM PatientProblem
),Prv_CTE AS (
SELECT *,LAG(DischargeDate) OVER (PARTITION BY PATIENTID ORDER BY RNK) AS PrvDt FROM UPP_CTE
),Diff_CTE AS (
SELECT *,CASE WHEN DATEDIFF(DD,AdmissionDate,PrvDt) = -1 THEN 0 ELSE 1 END AS Diff_Comp FROM Prv_CTE
),SUM_CTE AS(
SELECT *,SUM(Diff_Comp) OVER (PARTITION BY PATIENTID ORDER BY RNK) AS Sum_Comp FROM Diff_CTE
)
SELECT PatientID,MIN(AdmissionDate) AS AdminssionDate,MAX(DischargeDate) AS DischargeDate,SUM(Cost) AS Cost FROM SUM_CTE
GROUP BY PatientID,Sum_Comp
ORDER BY PatientID
/*
PatientID AdminssionDate DischargeDate Cost
1009 2014-07-27 00:00:00.000 2014-08-23 00:00:00.000 2120.00
1009 2014-08-31 00:00:00.000 2014-09-14 00:00:00.000 3160.00
1009 2014-12-01 00:00:00.000 2014-12-31 00:00:00.000 2090.00
1024 2014-06-07 00:00:00.000 2014-08-02 00:00:00.000 6600.00
*/
LikeLike
;with cte
as
(
select a.patientid,a.admissiondate,a.dischargedate,a.cost
from #PatientProblem a
left join #PatientProblem b
on a.PatientID=b.PatientID and DATEADD(day,-1,a.AdmissionDate)=b.DischargeDate
where b.PatientID is null
union all
select a.PatientID,a.AdmissionDate,b.DischargeDate,b.Cost
from cte a join #PatientProblem b
on a.PatientID=b.PatientID
where DATEADD(day,1,a.DischargeDate)=b.AdmissionDate
)
select patientid,admissiondate,MAX(dischargedate)as dischargedate,sum(cost) as cost
from cte
group by patientid,AdmissionDate
order by PatientID
LikeLike
WITH CTE AS(
select a.patientid,a.admissiondate,a.dischargedate,a.cost,
case when b.cost is null then 1 else 0 end as start_mark
from PatientProblem a
left join PatientProblem b
on a.PatientID=b.PatientID and DATEADD(day,-1,a.AdmissionDate)=b.DischargeDate
),CTE3 as (Select patientid,admissiondate,dischargedate,cost,start_mark,
sum(start_mark) over(order by patientid,admissiondate) as run from CTE )
Select patientid,min(admissiondate) as admissiondate,max(dischargedate) as dischargedate,sum(cost) as cost
from CTE3 group by patientid,run
LikeLiked by 1 person
with cte as (
SELECT PatientID,
AdmissionDate,
DischargeDate,
Cost,
ROW_NUMBER() over (order by (select null)) as rnk
FROM PatientProblem
),
cte_days as
(
select *,
IIF(DATEDIFF(day,(LAG(DischargeDate) over (order by rnk asc)), AdmissionDate) = 1, 0, 1) as daysgroup
from cte
),
cte_grouper as (
select *, sum(daysgroup) over (order by rnk asc) grouper
from cte_days
)
select
distinct
PatientID,
min(AdmissionDate) over(partition by grouper order by (select null) asc) AdmissionDate,
max(DischargeDate) over(partition by grouper order by (select null) asc) DischargeDate,
sum(Cost) over(partition by grouper order by (select null) asc) Cost
from cte_grouper
LikeLike
;with cte as
(
select PatientId, AdmissionDate, DischargeDate, Cost
from PatientProblem
union all
select p.PatientID, c.AdmissionDate, p.DischargeDate, p.Cost
from PatientProblem p
join cte c
on p.PatientID = c.PatientID
and p.AdmissionDate = dateadd(day, 1, c.DischargeDate)
where p.AdmissionDate c.AdmissionDate
), AdmissionDedupe as
(
select *
,sum(Cost) over (partition by PatientId, AdmissionDate) as CalculatedCost
,row_number() over (partition by PatientId, AdmissionDate order by DischargeDate desc) as rn
from cte
), DischargeDedupe as
(
select PatientId, AdmissionDate, DischargeDate, CalculatedCost as Cost
,row_number() over (partition by PatientId, DischargeDate order by AdmissionDate asc) as rn
from AdmissionDedupe
where rn = 1
)
select PatientId, AdmissionDate, DischargeDate, Cost
from DischargeDedupe
where rn = 1
order by PatientID, AdmissionDate, DischargeDate;
LikeLike