Sql-server – Help to retrieve data set

aggregatesql server

I have data set as below, for each Parent Record there can be multiple Cancel and Un-Cancels. I am trying to pull for each Parent, I am trying to find when it when it got cancelled and when it got un-cancelled (if it did); as in my second table below.

Also, another complexity is, for ParRecId A, it got un-cancelled and recancelled on same day 11-26 so it should stay Cancelled.

Same case ParRecId B, it got into Cancellation and then reactivation same day so it should stay Active.

Any help is greatly appreciated.

Given this dataset

+-------+----------+------------+------------+
| RecId | ParRecId |  Created   |   Status   |
+-------+----------+------------+------------+ 
|    17 | A        | 2015-11-26 | CANCEL     |
|    16 | A        | 2015-11-26 | INPROGRESS |
|    15 | B        | 2015-11-25 | Active     |
|    14 | B        | 2015-11-25 | CANCEL     |
|    13 | B        | 2015-11-25 | ON-HOLD    |
|    12 | E        | 2015-11-25 | CANCEL     |
|    11 | D        | 2015-11-25 | ACTIVE     |
|    10 | C        | 2015-10-19 | CANCEL     |
|     9 | C        | 2015-09-19 | ACTIVE     |
|     8 | C        | 2015-09-12 | CANCEL     |
|     7 | C        | 2015-09-05 | ACTIVE     |
|     6 | A        | 2015-09-01 | CANCEL     |
|     5 | B        | 2015-08-29 | INPROGRESS |
|     4 | C        | 2015-08-29 | INPROGRESS |
|     3 | B        | 2015-08-12 | CANCEL     |
|     2 | B        | 2015-08-03 | ACTIVE     |
|     1 | A        | 2015-06-01 | ACTIVE     |
+-------+----------+------------+------------+

The result should look like this

+----------+------------+------------+--------+
| ParRecId |   START    |    END     | STATUS |
+----------+------------+------------+--------+
| A        | 2015-09-01 |            | OPEN   |
| B        | 2015-08-12 | 2015-08-29 | CLOSED |
| C        | 2015-09-12 | 2015-09-19 | CLOSED |
| C        | 2015-10-19 |            | OPEN   |
| E        | 2015-11-25 |            | OPEN   |
+----------+------------+------------+--------+

Best Answer

Try the query below.

Query:

with ord as(
    SELECT ParRecId
        , m = ROW_NUMBER() over(partition by ParRecId, Created order by RecId)
        , r = DENSE_RANK() over(partition by ParRecId order by Created desc, RecId)
        , Created, [Status]
    FROM @data
)
SELECT o1.ParRecId
    , [START] = o1.Created
    , [END] = CASE WHEN o2.Status <> 'CANCEL' THEN o2.Created END
    , [STATUS] = CASE WHEN o2.Status IS NULL OR o2.Status = 'CANCEL' THEN 'OPEN' ELSE 'CLOSED' END      
FROM ord o1
LEFT JOIN ord o2 
    ON o2.ParRecId = o1.ParRecId 
        AND o2.r = o1.r-1 
WHERE o1.[Status] = 'CANCEL'
    AND (o1.Created <> o2.Created OR o2.Created IS NULL)
ORDER BY o1.ParRecId, o1.Created ASC

Your data:

declare @data table(RecId int, ParRecId char(1), Created date, [Status] varchar(10));
INSERT INTO @data(RecId, ParRecId, Created, [Status]) values
    (17,'A', '2015-11-26', 'CANCEL')
    , (16, 'A', '2015-11-26', 'INPROGRESS')
    , (15, 'B', '2015-11-25', 'Active')
    , (14, 'B', '2015-11-25', 'CANCEL')
    , (13, 'B', '2015-11-25', 'ON-HOLD')
    , (12, 'B', '2015-11-25', 'ON-HOLD')    
    , (12, 'E', '2015-11-25', 'CANCEL')
    , (11, 'D', '2015-11-25', 'ACTIVE')
    , (10, 'C', '2015-10-19', 'CANCEL')  
    , (9, 'C', '2015-09-19', 'ACTIVE')
    , (8, 'C', '2015-09-12', 'CANCEL')
    , (7, 'C', '2015-09-05', 'ACTIVE')
    , (6, 'A', '2015-09-01', 'CANCEL')
    , (5, 'B', '2015-08-29', 'INPROGRESS') 
    , (4, 'C', '2015-08-29', 'INPROGRESS')
    , (3, 'B', '2015-08-12', 'CANCEL')
    , (2, 'B', '2015-08-03', 'ACTIVE')
    , (1, 'A', '2015-06-01', 'ACTIVE');

Output:

ParRecId    START       END         STATUS
A           2015-09-01  NULL        OPEN
B           2015-08-12  2015-08-29  CLOSED
C           2015-09-12  2015-09-19  CLOSED
C           2015-10-19  NULL        OPEN
E           2015-11-25  NULL        OPEN
Related Question