SQL Server PIVOT on multiple columns is losing data

Question

I've spent way too much time on this issue, and I'm not getting to the finish line. Please read this through before you run to a conclusion that this is a duplicate of all the other pivot with multiple columns on SO.

We have properties and units, with a table which keeps track of when something changed in the unit. We cannot change the structure of the table, as this is a vendor application.

Objective: Pull out the begin and end date for when a unit had an unavailable code of "model".

Issue: I need to filter out the dates where it was available in the middle, though that seems to omit one row of data each time (for unit 105).

what I've tried: PIVOT, CROSS APPLY in conjunction with LEAD/LAG

Here's a link to a SQLFiddle: http://sqlfiddle.com/#!6/29592/2/0

The rest of the question has the tsql from the SQLfiddle including the results which I got. The desired result is at the end.

Create table and insert sample data

DROP TABLE IF EXISTS testModelUnit; 
CREATE TABLE testModelUnit(
    propertykey         INT             NOT NULL
    ,unitNumber         VARCHAR(10)     NOT NULL
    ,rowStartDate       DATETIME        NOT NULL
    ,rowEndDate         DATETIME        NOT NULL
    ,unavailableCode    varchar(10)     NULL
    ,CONSTRAINT pk_testModelUnit PRIMARY KEY (propertykey, unitNumber, rowStartDate )
)
GO

INSERT INTO testModelUnit VALUES 

(33,'105',  '2010-11-11 00:00:00.000','2016-11-11 00:00:00.000','MODEL')
,(33,'105', '2016-11-11 00:00:00.000','2016-12-14 07:51:03.307','MODEL')
,(33,'105', '2016-12-14 07:51:03.307','2017-01-01 00:00:00.000',NULL)
,(33,'105', '2017-01-01 00:00:00.00','2017-03-21 12:21:13.703','MODEL')
,(33,'105', '2017-03-21 12:21:13.703','2017-04-21 12:21:13.703','MODEL')
,(33,'105', '2017-04-21 12:21:13.703','9999-12-31 00:00:00.000','MODEL')
,(33,'2606','2017-04-21 12:21:23.207','9999-12-31 00:00:00.000','MODEL')
,(33,'2606','2017-04-19 10:30:09.227','2017-04-21 12:21:23.207','MODEL')
,(33,'2703','2016-12-14 07:51:03.307','2017-04-19 10:29:47.970','MODEL')
,(33,'2703','2011-11-11 00:00:00.000','2016-12-14 07:51:03.307','MODEL')

GO 

That gives you all the data which you need in order to test it, as unit 105 was available for a short period of time at the end of 2016.

Attempt 1 - use LEAD/LAG to determine if a date is the first in a series - then use multiple PIVOT statements

SELECT
    propertykey         
    ,unitNumber     
    ,firstDate
    ,lastDate   
FROM (
    SELECT 
        propertykey         
        ,unitNumber         
        ,rowStartDate       
        ,rowEndDate     
        ,CASE 
            WHEN propertykey = LAG(propertykey,1,NULL) OVER (PARTITION BY propertykey,unitNumber ORDER BY rowStartDate) 
                AND unitNumber = LAG(unitNumber,1,NULL) OVER (PARTITION BY propertykey,unitNumber ORDER BY rowStartDate) 
                AND LAG(rowEndDate,1,NULL) OVER (PARTITION BY propertykey,unitNumber ORDER BY rowStartDate) = rowStartDate THEN NULL            
            ELSE 'firstDate'
        END ISFIRST
        ,CASE 
            WHEN propertykey = LEAD(propertykey,1,NULL) OVER (PARTITION BY propertykey,unitNumber ORDER BY rowStartDate) 
                AND unitNumber = LEAD(unitNumber,1,NULL) OVER (PARTITION BY propertykey,unitNumber ORDER BY rowStartDate) 
                AND LEAD(rowStartDate,1,NULL) OVER (PARTITION BY propertykey,unitNumber ORDER BY rowStartDate) = rowEndDate THEN NULL           
            ELSE 'lastDate'
        END ISLAST
    FROM testModelUnit
    WHERE UnavailableCode = 'model'
) SRC
PIVOT (
    MAX(rowStartDate)
    FOR isfirst in ([firstDate])
) as pivotFirst
PIVOT (
    MAX(rowEndDate)
    FOR islast in ([lastDate])
) as pivotLast

Results were:

propertykey  unitNumber  firstDate                  lastDate
33           105         NULL                       9999-12-31 00:00:00.000
33           105         2010-11-11 00:00:00.000    NULL
33           105         2017-01-01 00:00:00.000    NULL
33           2606        NULL                       9999-12-31 00:00:00.000
33           2606        2017-04-19 10:30:09.227    NULL
33           2703        NULL                       2017-04-19 10:29:47.970
33           2703        2011-11-11 00:00:00.000    NULL

Issue is twofold: firstly, I have the NULLs in different rows, and secondly, I am missing an end date for unit 105 (by reversing the order of the two pivot statements, I reversed the issue, and I was then missing on start date)

Second attempt: use the LAG/LEAD as before, though this time use CROSS APPLY to get the first/last values into one column and then pivot the result

SELECT 
    propertykey
    ,unitNumber
    ,firstDate
    ,lastDate
FROM(
    SELECT
        propertykey
        ,unitNumber
        ,ca.col
        ,ca.value       
    FROM 
    (
        SELECT 
            propertykey         
            ,unitNumber         
            ,rowStartDate       
            ,rowEndDate     
            ,CASE 
                WHEN propertykey = LAG(propertykey,1,NULL) OVER (PARTITION BY propertykey,unitNumber ORDER BY rowStartDate) 
                    AND unitNumber = LAG(unitNumber,1,NULL) OVER (PARTITION BY propertykey,unitNumber ORDER BY rowStartDate) 
                    AND LAG(rowEndDate,1,NULL) OVER (PARTITION BY propertykey,unitNumber ORDER BY rowStartDate) = rowStartDate THEN NULL            
                ELSE 'firstDate'
            END ISFIRST
            ,CASE 
                WHEN propertykey = LEAD(propertykey,1,NULL) OVER (PARTITION BY propertykey,unitNumber ORDER BY rowStartDate) 
                    AND unitNumber = LEAD(unitNumber,1,NULL) OVER (PARTITION BY propertykey,unitNumber ORDER BY rowStartDate) 
                    AND LEAD(rowStartDate,1,NULL) OVER (PARTITION BY propertykey,unitNumber ORDER BY rowStartDate) = rowEndDate THEN NULL           
                ELSE 'lastDate'
            END ISLAST
        FROM testModelUnit
        WHERE UnavailableCode = 'model'
    ) sub
    OUTER APPLY (
        SELECT ISFIRST, rowStartDate
        UNION ALL
        SELECT ISLAST, rowEndDate
    ) CA (col, value)
    WHERE col IS NOT NULL
)src
PIVOT
(
    max(value)
    for col in ([firstDate],[lastDate])
) AS pivoted

Result:

propertykey  unitNumber firstDate                 lastDate
33           105        2017-01-01 00:00:00.000   9999-12-31 00:00:00.000
33           2606       2017-04-19 10:30:09.227   9999-12-31 00:00:00.000
33           2703       2011-11-11 00:00:00.000   2017-04-19 10:29:47.970

Issue: I got rid of the NULL rows, though I am still missing one record of data for 105

Desired result:

propertykey      unitNumber firstDate                 lastDate
    33           105        2010-11-11 00:00:00.000   2016-12-14 07:51:03.307
    33           105        2017-01-01 00:00:00.000   9999-12-31 00:00:00.000
    33           2606       2017-04-19 10:30:09.227   9999-12-31 00:00:00.000
    33           2703       2011-11-11 00:00:00.000   2017-04-19 10:29:47.970

Answer 1

Are you looking query like below?

Select PropertyKey, UnitNumber, Min(RowStartDate) as FirstDate, Max(rowEndDate) as LastDate from (
    Select *, Bucket = Row_number() over(partition by propertykey, unitnumber order by rowStartDate) - 
            Row_number() over(partition by propertykey, unitnumber, unavailablecode order by rowStartDate) 
    from testModelUnit
) a
Where a.unavailableCode is not null
group by propertykey, unitNumber, Bucket

Output as below:

+-------------+------------+-------------------------+-------------------------+
| PropertyKey | UnitNumber |        FirstDate        |        LastDate         |
+-------------+------------+-------------------------+-------------------------+
|          33 |        105 | 2010-11-11 00:00:00.000 | 2016-12-14 07:51:03.307 |
|          33 |        105 | 2017-01-01 00:00:00.000 | 9999-12-31 00:00:00.000 |
|          33 |       2606 | 2017-04-19 10:30:09.227 | 9999-12-31 00:00:00.000 |
|          33 |       2703 | 2011-11-11 00:00:00.000 | 2017-04-19 10:29:47.970 |
+-------------+------------+-------------------------+-------------------------+

Demo