CODEWITHSUNDEEP
sqlsql-server
CarlosIS
CarlosIS

Reputation: 83

Group By field when condition is satisfied

I am dealing with a use case I am stuck with. The problem is that I need to group by some rows by an integer field when these rows date field difference is less than certain time (Ex: 30min).

Example of a possible subset of records

I know I can use aggr. functions in order to group max and min dates, so the result set would look like this:

Grouping by idregion + max and min date

Lets say that the diff time restriction is 4 hours, then I would like to have my results set where IdRegion is "480", one of these with the the same data as the previous image 1st row, and the other one would have these values:

IdRegion: 480
FechaEntrada: 2021-10-30 15:24:44.000 
FechaSalida: 2021-11-02 13:57:48.000

I have tried cursor and CTE's so far, but none of them seems to work. Apologies if this is way too simple stuff, I am a newbie on SQL.

Thanks

Upvotes: 0

Views: 107

Answers (1)

Richard Deeming
Richard Deeming

Reputation: 31198

Assuming Microsoft SQL Server, and given the following setup:

DECLARE @T TABLE 
(
    IdRegion int NOT NULL, 
    FechaEntrada datetime2(0) NOT NULL, 
    FechaSalida datetime2(0) NOT NULL
);

INSERT INTO @T (IdRegion, FechaEntrada, FechaSalida)
VALUES
    (480, '20211030 00:04:04', '20211030 08:54:13'),
    (480, '20211030 15:24:44', '20211030 16:06:50'),
    (480, '20211030 17:34:44', '20211030 16:06:50'),
    (480, '20211030 18:44:52', '20211102 13:57:48'),
    (483, '20211030 10:08:27', '20211030 10:29:48'),
    (5081, '20211030 17:26:05', '20211030 17:27:05')
;

Then something like this seems to produce what you're after:

SELECT DISTINCT
    T.IdRegion,
    S.FechaEntrada,
    E.FechaSalida
FROM
    @T As T
    OUTER APPLY
    (
        SELECT TOP 1 FechaEntrada
        FROM @T As S
        WHERE S.IdRegion = T.IdRegion
        And S.FechaEntrada <= T.FechaEntrada
        And DateDiff(hour, S.FechaEntrada, T.FechaEntrada) < 4
    ) As S
    OUTER APPLY
    (
        SELECT TOP 1 FechaSalida
        FROM @T As E
        WHERE E.IdRegion = T.IdRegion
        And E.FechaEntrada >= T.FechaEntrada
        And DateDiff(hour, T.FechaEntrada, E.FechaEntrada) < 4
        ORDER BY FechaEntrada DESC
    ) As E
;

Output:

IdRegion FechaEntrada FechaSalida
480 2021-10-30 00:04:04 2021-10-30 08:54:13
480 2021-10-30 15:24:44 2021-11-02 13:57:48
483 2021-10-30 10:08:27 2021-10-30 10:29:48
5081 2021-10-30 17:26:05 2021-10-30 17:27:05

Upvotes: 1

Related Questions