CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Michael A
Michael A

Reputation: 9910

Should I use a partition or is there a better way?

I have a table structure, simplified like so:

period_id int
suite_id varchar(20)
suite_status varchar(50)

Each month is assigned to a period id. Each suite has a record for each month (period_id). Suite status's can be either 'Vacant' or 'Active Lease'. I'm trying to write a query that shows the top five deals that have been leased since the 1st of July 2011 that were vacant the longest before they became an active lease but I don't know how to approach this.

My original approach was to rank each lease by the number of periods that it was vacant, like so:

;WITH cte_vacancy_periods AS
(
        SELECT  lp.suite_id ,
                count(lp.period_id) AS 'Periods Vacant'
        FROM    property.lease_period lp
        WHERE   lp.suite_status = 'Vacant'
                        OR
                        (       isnull(lp.suite_status, '') = ''
                                AND     lp.lease_id = 'No Lease'        )
        GROUP BY
                lp.suite_id
        ORDER BY count(lp.period_id) desc
)

I was then planning to cross-reference this with the leases made active since period 258 (the 01-July-2011).

Unfortunately this doesn't work in cases where a lease was vacant for two months, then leased for a month then vacant for two more months (showing a total of four months vacant before being leased when it should really be two).

I'm sure this could be done with a partition but I've come up short trying to do this and don't quite understand them. What am I missing in my knowledge for approaching this problem? If I've explained the data poorly so it's hard to understand what I'm trying to do please let me know and I'll elaborate where I can.

Upvotes: 1

Views: 67

Answers (1)

MatBailie
MatBailie

Reputation: 86765

Hopefully an approach something like this will help.

I have to admit that I'm not 100% clear on what final result you're looking for, but this should get you a data-set that can answer many quesions similar to yours.

WITH
  reformatted_data
AS
(
  SELECT
    suite_id,
    period_id,
    CASE
      WHEN suite_status = 'Vacant'                                 THEN 0
      WHEN lease_id = 'No Lease' AND isnull(suite_status, '') = '' THEN 0
                                                                   ELSE 1
    END as suite_status
  FROM
    properties.lease_period
)
,
  gaps_and_islands
AS
(
  SELECT
    suite_id,
    period_id,
    suit_status,
    ROW_NUMBER() OVER (PARTITION BY suite_id               ORDER BY period_id) AS seq,
    ROW_NUMBER() OVER (PARTITION BY suite_id, suite_status ORDER BY period_id) AS seq_status
  FROM
    reformatted_data
)

SELECT
  suite_id,
  suit_status,
  MIN(period_id)       AS first_period_id,
  MAX(period_id)       AS last_period_id,
  MAX(seq) - MIN(seq)  AS consecutive_periods
FROM
  gaps_and_islands
GROUP BY
  suite_id,
  suite_status,
  seq - seq_status

Upvotes: 1

Related Questions