Make two continued date records in the same row via joining two tables.

I tried to use LAG functions to score each row 1 for a new start row and 0 for others. So column has values of 1 and 0 eg 1,0,0,1,1,0,1 then uses SUM aggregate function to add all values together so becomes 1,1,1,2,3,3,4. Then use these values to group row

Using LAG and LEAD functions to get start admission date and upper bound admission date respectively, then joining on original table to get amounts.

Using the SQL server 2012 new window function features to solve the island issue. Particularly, using the new running total calculation to group all consecutive admissions into same group is different with last solution I submitted and I found that this so

(SQLServer 2012) -- What this solution does is it first uses the LAG() function to bring previous discharge dates alongside each admission date. We then take the date difference between the admission date and the previous discharge date and subtract

This solution uses the new SQL server window function lead to identify the row where a consecutive admisssion ends and give it a value 1, all other consecutive rows will have a value 0. Once the end consecutive rows are indentified, again uses the new L