First and last values before a partition change

• leeholden

  SSC Veteran

  Points: 233

  More actions

  September 20, 2017 at 9:11 am

  #402504

  give the following data set:

  
create table #t (CustomerID INT, Stage int, GroupStage int, FromDt date, ToDt date);

  insert #t
values
    (1001, 1, 1, '2017-01-02', '2017-02-01'),
    (1001, 2, 1, '2017-02-02', '2017-03-01'),
    (1001, 3, 2, '2017-03-02', '2017-04-01'),
    (1001, 4, 2, '2017-04-02', '2017-05-01'),
    (1001, 5, 2, '2017-05-02', '2017-06-01'),
    (1001, 6, 1, '2017-06-02', '2017-07-01');


  I need to find the From Date and To Date of each distinct period of their GroupStage. so the desired result is:

  1001, 1, '2017-01-02', '2017-03-01'
  1001, 2, '2017-03-02', '2017-06-01'
  1001, 1, '2017-06-02', '2017-07-01'

  everything i try includes the last row in the first grouping. this is where i got to:
  
select CustomerID,
    GroupStage,
    FromDt,
    ToDt,
    FIRST_VALUE([FromDt]) over (Partition by CustomerId, GroupStage order by FromDt) as FirstDate,
    LAST_VALUE([ToDt]) over (Partition by CustomerId, GroupStage order by FromDt ) as LastDate1,
    LAST_VALUE([ToDt]) over (Partition by CustomerId, GroupStage order by FromDt rows between unbounded preceding and unbounded following) as LastDate2,
    LAST_VALUE([ToDt]) over (Partition by CustomerId, GroupStage order by FromDt rows between current row and unbounded following) as LastDate3,
    LAST_VALUE([ToDt]) over (Partition by CustomerId, GroupStage order by FromDt rows between unbounded preceding and current row) as LastDate4
from #t
ORDER BY FromDt
 

  It's clear from my results that the ORDER doesn't affect the PARTITION.
  I've been pulling my hair out for a week over this, and i'm on the verge of using a cursor - that's how desperate it has gotten!
  Any help much appreciated

• Thom A

  SSC Guru

  Points: 99009

  More actions

  September 20, 2017 at 9:29 am

  #1960188

  Looks like a Groups and Islands problem.

  Using an Example the "lovely" Luis (I think it was Luis, if not, I apologise!) gave me awhile ago, this should give you what your looking for:

  WITH Islands AS (
  SELECT *,
        ROW_NUMBER() OVER (ORDER BY CustomerID, Stage, GroupStage) - ROW_NUMBER() OVER (PARTITION BY CustomerID, GroupStage ORDER BY Stage) AS Grp
  FROM #t)
SELECT CustomerID,
       GroupStage,
       MIN(FromDt) AS FromDate,
       MAX(ToDt) AS ToDate
FROM Islands
GROUP BY CustomerID,
         GroupStage,
         Grp
ORDER BY FromDate ASC;

  Found the old post, it was Drew!

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk