CODEWITHSUNDEEP
sqlsql-servergaps-and-islandsdense-rankpartition-by
mckeyes
mckeyes

Reputation: 23

SQL Server DENSE_RANK()

I have a table where each line item contains a unit#, date stamp, and bed count. A record is created each day for each unit with the number of beds.

Unit   DateTime    Beds
----------------------
ICU    2011-03-23  12
ICU    2011-03-24  24
ICU    2011-03-25  24
ICU    2011-03-26  35
ICU    2011-03-27  24
ICU    2011-03-28  24

I am attempting to take the data and create a table like the one below.

Unit Beds  StartDate  EndDate
------------------------------
ICU  12    2011-03-23 2011-03-23
ICU  24    2011-03-24 2011-03-25
ICU  35    2011-03-26 2011-03-26
ICU  24    2011-03-27 2011-03-28

The issue is that the rows with 24 beds are being combined to get these results.

Unit Beds  StartDate  EndDate
------------------------------
ICU  12    2011-03-23 2011-03-23
ICU  24    2011-03-24 2011-03-28
ICU  35    2011-03-26 2011-03-26

I tried using DENSE_RANK to assign a ranking to use as a grouping number to separate the instances of the 24 beds. I want to the grouper values to be 1,2,2,3,4,4. Instead the grouper values are 1,2,2,3,2,2. 

SELECT DENSE_RANK() OVER(PARTITION BY Unit ORDER BY Beds) AS Grouper, 
Unit, DateTime, Beds
FROM StatsLocation 

Grouper Unit DateTime    Beds
-------------------------------
1       ICU  2011-03-23  12
2       ICU  2011-03-24  24
2       ICU  2011-03-25  24
3       ICU  2011-03-26  35
2       ICU  2011-03-27  24
2       ICU  2011-03-28  24

Upvotes: 2

Views: 726

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use lag to check if the previous row has the same value for beds and get the running sum as Grouper column.

SELECT SUM(COL) OVER(PARTITION BY Unit ORDER BY DateTime) as Grouper,Unit,DateTime,Beds
FROM (
SELECT CASE WHEN lag(beds) OVER(PARTITION BY Unit ORDER BY DateTime)=beds then 0 ELSE 1 END AS col, 
Unit, DateTime, Beds
FROM StatsLocation
) X

Thereafter, to get the start and end dates is easy with min and max for each group.

WITH CTE AS( 
SELECT SUM(COL) OVER(PARTITION BY Unit ORDER BY DateTime) as Grouper,Unit,DateTime,Beds
FROM (SELECT CASE WHEN lag(beds) OVER(PARTITION BY Unit ORDER BY DateTime)=beds then 0 ELSE 1 END AS col, 
      Unit, DateTime, Beds
      FROM StatsLocation) t
)
SELECT UNIT,BEDS,MIN(DATETIME) AS STARTDATE,MAX(DATETIME) AS ENDDATE
FROM CTE
GROUP BY UNIT,BEDS,GROUPER

If you don't need a grouper column,but just the start and end dates, this can be done with difference of row numbers.

SELECT UNIT,BEDS,MIN(DATETIME) AS STARTDATE,MAX(DATETIME) AS ENDDATE
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY Unit ORDER BY Dt)
   - ROW_NUMBER() OVER(PARTITION BY Unit,Beds ORDER BY Dt) AS    Grouper, 
Unit, Dt, Beds
FROM StatsLocation) T
GROUP BY UNIT,BEDS,GROUPER

Upvotes: 2

SqlZim
SqlZim

Reputation: 38043

This is a gaps and islands problem, you can solve it using two row_number()s like so:

select 
    Unit
  , Beds
  , StartDate = min(DateTime)
  , EndDate   = max(DateTime)
from (
  select *
    , rn_x = row_number() over (partition by unit order by [datetime]) 
    , rn_y = row_number() over (partition by unit, beds order by [datetime]) 
  from t 
    ) as s
group by Unit, Beds, rn_x-rn_y
order by Unit, StartDate

rextester demo: http://rextester.com/IJXC7931

returns:

+------+------+------------+------------+
| Unit | Beds | StartDate  |  EndDate   |
+------+------+------------+------------+
| ICU  |   12 | 2011-03-23 | 2011-03-23 |
| ICU  |   24 | 2011-03-24 | 2011-03-25 |
| ICU  |   35 | 2011-03-26 | 2011-03-26 |
| ICU  |   24 | 2011-03-27 | 2011-03-28 |
+------+------+------------+------------+

Upvotes: 0

Related Questions