v2

This solution uses the Tally table to first expand the dates for each PatientID. After the dates are expanded, use a technique taught by Itzik Ben Gan for the "Island" technique. Final CTE groups the data and sums up the cost.

The challenge idea is taken from a problem discussed in the MSDN TSQL forum. The challenge is to find the Islands(gaps) in sequential dates. You need to write a query to identify continuous intervals from the start date and end date. For example, 01/

The challenge idea is taken from a problem discussed in the MSDN TSQL forum. The challenge is to find the Islands(gaps) in sequential dates. You need to write a query to identify continuous intervals from the start date and end date. For example, 01/

I used CTE's isolate the admission dates that had no discharge on the previous day and discharge dates that had no admission on the following day. By using the Row_number function on bothe, I was able to join the result tables into another giving the 'isla

The challenge idea is taken from a problem discussed in the MSDN TSQL forum. The challenge is to find the Islands(gaps) in sequential dates. You need to write a query to identify continuous intervals from the start date and end date. For example, 01/

The challenge idea is taken from a problem discussed in the MSDN TSQL forum. The challenge is to find the Islands(gaps) in sequential dates. You need to write a query to identify continuous intervals from the start date and end date. For example, 01/

Two CTE tables are used to isolate all the islands.

The challenge idea is taken from a problem discussed in the MSDN TSQL forum. The challenge is to find the Islands(gaps) in sequential dates. You need to write a query to identify continuous intervals from the start date and end date. For example, 01/

Uses a recursive CTE to collapse the continuous intervals and then identifies the final interval for uninterrupted stay.