0

I have a table:

account   | onln_status |   browse status | beg_date |  end_date
----------+-------------+-----------------+----------+-------------
123456789 | On          |   Y             | 1/1/2018 |  2/1/2018
123456789 | On          |   N             | 2/2/2018 |  4/1/2018
123456789 | On          |   Y             | 4/2/2018 |  5/1/2018
123456789 | Off         |   N             | 5/2/2018 |  7/1/2018
123456789 | Off         |   Y             | 7/2/2018 |  8/1/2018
123456789 | On          |   Y             | 8/2/2018 |  10/1/2018
123456789 | On          |   N             | 10/2/2018|  11/1/2018

and need the result to show :

account   | onln_status |   beg_date |  end_date
----------+-------------+------------+------------
123456789 | On          |   1/1/2018 |  5/1/2018
123456789 | Off         |   5/2/2018 |  8/1/2018
123456789 | On          |   8/2/2018 |  11/1/2018

At first, I used using min(beg date) and max(end date) but it doesn't work in this situation:

select 
    omsid, onln_status, min(beg_date), max(end_date)
from 
    table
group by 
    omsid, onln_status

I also tried getting a unique number whenever the previous online_status changes, but could not get a way to just add on to the number:

 select 
     *, 
     case 
        when onln_status <> lag(onln_status) over (partition by account order by beg_date)
           then 1 
          else 0
     end as status_change
 from 
     table
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Manav Kotian
  • 35
  • 4
  • Can you count on the `beg_date` of each entry to be just one day after the `end_date` of the previous entry with no gaps or overlap? – SunKnight0 Apr 20 '18 at 14:49
  • Google `SQL gaps and islands` – Tab Alleman Apr 20 '18 at 14:52
  • 1
    Post the SQL that you've tried, don't just say "I've tried this"; it might be that we simply need to make a small change to your SQL, rather that writing an entire new one. – Thom A Apr 20 '18 at 14:53
  • Possible duplicate of [Group rows by contiguous date ranges for groups of values](https://stackoverflow.com/questions/27368543/group-rows-by-contiguous-date-ranges-for-groups-of-values) – Tab Alleman Apr 20 '18 at 15:05

1 Answers1

1

This should get you going:

CREATE TABLE Account (AccountID bigint,
                      onln_status varchar(3),
                      BrowseStatus char(1),
                      Beg_date date,
                      End_Date date);
GO

INSERT INTO Account
SELECT A, O, B, CONVERT(date,S,101), CONVERT(date,E,101)
FROM (
    VALUES (123456789,'On','Y','1/1/2018','2/1/2018'),
           (123456789,'On','N','2/2/2018','4/1/2018'),
           (123456789,'On','Y','4/2/2018','5/1/2018'),
           (123456789,'Off','N','5/2/2018','7/1/2018'),
           (123456789,'Off','Y','7/2/2018','8/1/2018'),
           (123456789,'On','Y','8/2/2018','10/1/2018'),
           (123456789,'On','N','10/2/2018','11/1/2018')) V(A, O, B, S, E);
GO

WITH Grps AS(
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY Beg_date) - --You may need to add a PARTITION here (I.e. on AccountID)
           ROW_NUMBER() OVER (PARTITION BY onln_status ORDER BY Beg_date) AS Grp --You may need to add a PARTITION here (I.e. on AccountID)
    FROM Account)
SELECT AccountID,
       onln_status,
       MIN(beg_date) AS beg_date,
       MAX(End_date) AS End_Date
FROM Grps
GROUP BY AccountID,
          onln_status,
          Grp;

GO
DROP TABLE Account;

Note my comments on the use of ROW_NUMBER() though. You may need to add further partitions.

Thom A
  • 88,727
  • 11
  • 45
  • 75