title: SQL Server PIVOT on multiple columns is losing data
body: <p>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.  </p>
  
  <p>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. </p>
  
  <p><strong>Objective</strong>: Pull out the begin and end date for when a unit had an unavailable code of "model". </p>
  
  <p><strong>Issue</strong>: 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).</p>
  
  <p><strong>what I've tried</strong>: <code>PIVOT, CROSS APPLY</code> in conjunction with <code>LEAD/LAG</code></p>
  
  <p>Here's a link to a SQLFiddle: <a href="http://sqlfiddle.com/#!6/29592/2/0" rel="nofollow noreferrer">http://sqlfiddle.com/#!6/29592/2/0</a></p>
  
  <p>The rest of the question has the tsql from the SQLfiddle including the results which I got. The desired result is at the end. </p>
  
  <p>Create table and insert sample data</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>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.</p>
  
  <p>Attempt 1 - use <code>LEAD/LAG</code> to determine if a date is the first in a series - then use multiple <code>PIVOT</code> statements</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Results were:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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)</p>
  
  <p>Second attempt: use the <code>LAG/LEAD</code> as before, though this time use <code>CROSS APPLY</code> to get the first/last values into one column and then pivot the result</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Result: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Issue: I got rid of the NULL rows, though I am still missing one record of data for 105</p>
  
  <p>Desired result: </p>
  
  <pre><code>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
  </code></pre>