CODEWITHSUNDEEP
sqlsql-server
Heinrich
Heinrich

Reputation: 2234

SQL Select column based on MIN value of another column after left join

I have a table with the column as below, lets call the table Incident:

IncidentId, IncidentType, StartDate, EndDate

I need to find the EndDate of the previous Incident based on the nearest previous EndDate and EventType for each Incident. So for a starting point I wrote a query as below:

SELECT 
    i1.IncidentId, i2.IncidentId, i2.EndDate, DATEDIFF(DAY, i1.StartDate, i2.EndDate) AS DD 
FROM 
    Incident i1
LEFT JOIN 
    Incident i2 ON i1.IncidentId != i2.IncidentId AND i1.IncidentType != 3 i2.IncidentType = 1 AND i2.EndDate < i1.StartDate

But I don't know how to go further. I had a go at the nested query with GROUP BY and MIN, the issue with that is that I need the EndDate of i2 but if I do GROUP BY on Id and EndDate I will end up with what I started with.

What I need at the end of day is to be to do at the end of day is something along the lines of:

SELECT 
    Incident.*
FROM 
    Incident 
LEFT JOIN 
    <RESULTS> ON Incident.IncidentId = <RESULTS>.IncidentId 
WHERE 
    ISNULL(<RESULTS>.EndDate, Incident.EndDate) > 'Some Random Date'

Given the sample table below:

|IncidentId|IncidentType|StartDate | EndDate  |
|:--------:|:----------:|:--------:|:------i-:|
|1         |1           |2019-06-01|2019-07-01|
|2         |2           |2019-10-01|2018-11-11|
|3         |3           |2019-06-01|2019-06-06|
|4         |1           |2019-01-01|2019-04-02|
|5         |2           |2019-05-01|2019-05-28|
|6         |3           |2019-12-01|2019-12-11|

I am hoping to get the following results:

|IncidentId|IncidentType|StartDate | EndDate  | Previous Incident EndDate |
|:--------:|:----------:|:--------:|:--------:|:-------------------------:|
|1         |1           |2019-06-01|2019-07-01| 2019-04-02                | \* EndDate of 4*\ 
|2         |2           |2019-10-01|2018-11-11| 2019-07-01                | \* EndDate of 1*\
|3         |3           |2019-06-01|2019-06-06| NULL                      | \* Rule Does not apply to Type 3 *\
|4         |1           |2019-01-01|2019-04-02| NULL                      | \* First Incident has no EndDate*\
|5         |2           |2019-05-01|2019-05-28| 2019-04-02                | \* EndDate of 4*\
|6         |3           |2019-12-01|2019-12-11| NULL                      | \* Rule Does not apply to Type 3 *\

Explanation:
Whenever we EventType is not 3 we want to use the EndDate of nearest previous Incident of EventType 1 if such an EventType exists previously

EDIT: I add some sample data and extend query a bit more to add some more depth to the query.

Upvotes: 0

Views: 79

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

The most straight-forward method is to use lateral join (CROSS/OUTER APPLY) in SQL Server.

LAG is tempting, but you need not just the previous row, you need to skip some rows in some cases, so I don't see how LAG can be used here. If you didn't have that requirement to look only at rows with IncidentType = 1, then you could use LAG.

Sample data

DECLARE @T TABLE (IncidentId int NOT NULL PRIMARY KEY, IncidentType int NOT NULL , StartDate date NOT NULL , EndDate date NOT NULL );
INSERT INTO @T VALUES
(1, 1, '2019-06-01', '2019-07-01'),
(2, 2, '2019-10-01', '2018-11-11'),
(3, 3, '2019-06-01', '2019-06-06'),
(4, 1, '2019-01-01', '2019-04-02'),
(5, 2, '2019-05-01', '2019-05-28'),
(6, 3, '2019-12-01', '2019-12-11');

Query

SELECT
    *,
    CASE WHEN IncidentType = 3
    THEN NULL
    ELSE
        A.EndDate
    END AS PreviousIncidentEndDate
FROM
    @T AS T1
    OUTER APPLY
    (
        SELECT TOP(1)
            T2.EndDate
        FROM @T AS T2
        WHERE
            T2.IncidentType = 1
            AND T2.EndDate < T1.StartDate
        ORDER BY T2.EndDate DESC
    ) AS A
ORDER BY
    T1.IncidentId;

Result

+------------+--------------+------------+------------+------------+-------------------------+
| IncidentId | IncidentType | StartDate  |  EndDate   |  EndDate   | PreviousIncidentEndDate |
+------------+--------------+------------+------------+------------+-------------------------+
|          1 |            1 | 2019-06-01 | 2019-07-01 | 2019-04-02 | 2019-04-02              |
|          2 |            2 | 2019-10-01 | 2018-11-11 | 2019-07-01 | 2019-07-01              |
|          3 |            3 | 2019-06-01 | 2019-06-06 | 2019-04-02 | NULL                    |
|          4 |            1 | 2019-01-01 | 2019-04-02 | NULL       | NULL                    |
|          5 |            2 | 2019-05-01 | 2019-05-28 | 2019-04-02 | 2019-04-02              |
|          6 |            3 | 2019-12-01 | 2019-12-11 | 2019-07-01 | NULL                    |
+------------+--------------+------------+------------+------------+-------------------------+

You'd better create an index on (IncidentType, EndDate).

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270081

I need to find the EndDate of the previous Incident based on the nearest previous EndDate and EventType for each Incident.

I think you want lag(). Something like this:

select i.*,
       lag(i.enddate) over (partition by i.eventtype order by i.enddate) as prev_enddate
from incident i

Upvotes: 0

Related Questions