title: Crosstab SQL rows on Date Islands with SUM of decimal column
body: <p>Similar Question: <a href="https://stackoverflow.com/questions/8592764/crosstabbing-rows-based-on-sequence-islands-in-a-list-of-times">Crosstabbing rows based on sequence islands in a list of times</a></p>
  
  <p>Take the following example data:</p>
  
  <pre><code>Employ     Date          Duration
  0000001    2012-11-12    9.00
  0000001    2012-11-13    9.00
  0000001    2012-11-14    9.00
  0000001    2012-11-16    9.00
  0000001    2012-11-17    9.00
  0000002    2012-11-04    9.00
  0000002    2012-11-05    9.00
  0000002    2012-11-06    9.00
  0000003    2012-11-01    9.00
  0000004    2012-11-02    6.50
  </code></pre>
  
  <p>I'm trying to transform this as follows:</p>
  
  <pre><code>Employ     Start Date    End Date      Days    TotalDuration
  0000001    2012-11-12    2012-11-14    3       27
  0000001    2012-11-16    2012-11-17    2       18
  0000002    2012-11-04    2012-11-06    3       27
  0000003    2012-11-01    2012-11-01    1       9.00
  0000004    2012-11-02    2012-11-02    1       6.50
  </code></pre>
  
  <p>Using the linked question above i've tried doing this as follows:</p>
  
  <pre><code>WITH    ExampleData ( [DET_NUMBERA], [Date], [Duration] )
            AS ( 
                  SELECT '0000001' AS [DET_NUMBERA], CONVERT(DATE, '12/11/2012', 103) AS [Date], 9.00 AS [Duration]
                  UNION ALL
                  SELECT '0000001', CONVERT(DATE, '13/11/2012', 103), 9.00
                  UNION ALL
                  SELECT '0000001', CONVERT(DATE, '14/11/2012', 103), 9.00
                  UNION ALL
                  SELECT '0000001', CONVERT(DATE, '16/11/2012', 103), 9.00
                  UNION ALL
                  SELECT '0000001', CONVERT(DATE, '17/11/2012', 103), 9.00
                  UNION ALL
                  SELECT '0000002', CONVERT(DATE, '04/11/2012', 103), 9.00
                  UNION ALL
                  SELECT '0000002', CONVERT(DATE, '05/11/2012', 103), 9.00
                  UNION ALL
                  SELECT '0000002', CONVERT(DATE, '06/11/2012', 103), 9.00
                  UNION ALL
                  SELECT '0000003', CONVERT(DATE, '01/11/2012', 103), 9.00
                  UNION ALL
                  SELECT '0000004', CONVERT(DATE, '02/11/2012', 103), 6.5
               ),
          CrossTabPrep
            AS ( SELECT   [DET_NUMBERA] ,
                          [Date] ,
                          [Duration] ,
                          CONVERT(INT, CONVERT(VARCHAR(10), GETDATE(), 112)) - ROW_NUMBER() OVER ( PARTITION BY [DET_NUMBERA] ORDER BY [Date] ) AS [Grp]
                 FROM     ExampleData
               ),
          FinalRender ( [DET_NUMBERA], [Start Date], [End Date], [Days], [Duration] )
            AS ( SELECT   [DET_NUMBERA] ,
                          MIN([Date]) AS [Start Date] ,
                          MAX([Date]) AS [End Date] ,
                          ISNULL(DATEDIFF(DAY, MIN([Date]), MAX([Date])), 0) AS [Days] ,
                          SUM([Duration]) AS [Duration]
                 FROM     CrossTabPrep
                 GROUP BY [DET_NUMBERA] ,
                          [Grp]
               )
      SELECT  *
      FROM    FinalRender
      ORDER BY [DET_NUMBERA]
  </code></pre>
  
  <p>But its not quite working - ive gone wrong somewhere - how do i tweak this SQL to transform as described above.</p>