Home / Questions / 276930
Accepted
Bryan Romero
Asked: 2020-07-06 01:07:14 +0800 CST 2020-07-06 01:07:14 +0800 CST

Query to compare several fields of the same table

  • 772

I have this table: 

+----+------------+-----------+
| ID | VALID_FROM | VALID_TO  |
+----+------------+-----------+
| A  | 28-JUN-19  | 28-JUN-19 |
+----+------------+-----------+
| A  | 28-JUN-19  | 29-JUN-19 | //penultimo de A
+----+------------+-----------+
| A  | 28-JUN-19  | 30-JUN-19 | //ultimo de A
+----+------------+-----------+
| B  | 28-JUN-19  | 30-JUN-19 | // lo ignoramos
+----+------------+-----------+
| C  | 27-JUN-19  | 28-JUN-19 |
+----+------------+-----------+
| C  | 28-JUN-19  | 28-JUN-19 |
+----+------------+-----------+
| C  | 28-JUN-19  | 29-JUN-19 |
+----+------------+-----------+
| C  | 29-JUN-19  | 30-JUN-19 | //penultimo de C
+----+------------+-----------+
| C  | 29-JUN-19  | 01-JUL-19 | //ultimo de C
+----+------------+-----------+

I need a query that for each present group A,B,Creturns me the records in which the VALID_FROMlast element of a group is GREATER VALID_TOthan the penultimate element of that same group`.

If a group has only one row then the comparison is not made (AS IN THE CASE OF B).

The query I created is this:

SELECT *
FROM
    myTabla g1
INNER JOIN
    myTabla g2 ON g2.valid_from > g1.valid_to 
WHERE
    g1.ID = g2.ID;

I don't know if the logic is correct, and I don't know how to make the comparison only between the last and penultimate of each group.

mysql

1 Answers

  1. Best Answer

    To obtain the last and penultimate record by [ID], a field is required to sort them, in this case use the [N_REGISTER] column with a consecutive number, it could also be a datetime field.

    In the example of the query there are no records that meet the requested condition, so the group "D" was entered in the sample data of this response.

    -- PASO 1: CREAR TABLA TEMPORAL CON EL CAMPO [N_BY_ID] PARA IDENTIFICAR EL ÚLTIMO (N_BY_ID=1) Y PENÚLTIMO (N_BY_ID=2) REGISTRO POR [ID].
SELECT *
INTO #TB_TEMPORAL
FROM
(
    SELECT [N_REGISTRO]
          ,[ID]
          ,[VALID_FROM]
          ,[VALID_TO]
          ,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY N_REGISTRO ASC) AS N_BY_ID --IDENTIFICA EL ULTIMO (N_BY_ID=1) Y PENÚLTIMO (N_BY_ID=2) REGISTRO POR [ID].
      FROM [dbo].[myTabla]
     --ORDER BY [N_REGISTRO] DESC
 ) AS TB
 WHERE TB.N_BY_ID < 3 -- FILTRO PARA OBTENER SOLO LOS ÚLTIMOS Y PENÚLTIMOS REGISTROS POR [ID].

-- PASO 2: OBTENER LOS REGISTRO QUE CUMPLEN LA CONDICIÓN SOLICITADA.
SELECT TB1.*
FROM #TB_TEMPORAL TB1, #TB_TEMPORAL TB2 --CROSS JOIN CON SIGO MISMA.
WHERE TB1.VALID_FROM > TB2.VALID_TO -- FILTRA SOLO LOS CASOS DONDE [VALID_FROM] ES MAYOR QUE [VALID_TO].
      AND TB1.N_BY_ID = 1 -- FILTRA LOS ÚLTIMOS REGISTROS DE TB1.
      AND TB2.N_BY_ID = 2 -- FILTRA LOS PENÚLTIMOS REGISTROS DE TB2.
      AND TB1.ID = TB2.ID -- FILTRA LOS REGISTROS CON IGUAL [ID].

-- DATOS DE EJEMPLO
CREATE TABLE [dbo].[myTabla](
    [N_REGISTRO] [int] NOT NULL,
    [ID] [nchar](10) NOT NULL,
    [VALID_FROM] [date] NOT NULL,
    [VALID_TO] [date] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (8, N'A         ', CAST(0xD53F0B00 AS Date), CAST(0xD63F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (7, N'A         ', CAST(0xD53F0B00 AS Date), CAST(0xD73F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (6, N'B         ', CAST(0xD53F0B00 AS Date), CAST(0xD73F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (5, N'C         ', CAST(0xD43F0B00 AS Date), CAST(0xD53F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (4, N'C         ', CAST(0xD53F0B00 AS Date), CAST(0xD53F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (3, N'C         ', CAST(0xD53F0B00 AS Date), CAST(0xD63F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (2, N'C         ', CAST(0xD63F0B00 AS Date), CAST(0xD73F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (1, N'C         ', CAST(0xD63F0B00 AS Date), CAST(0xD83F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (9, N'D         ', CAST(0xDE3F0B00 AS Date), CAST(0xDF3F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (10, N'D         ', CAST(0xDC3F0B00 AS Date), CAST(0xDD3F0B00 AS Date))
INSERT [dbo].[myTabla] ([N_REGISTRO], [ID], [VALID_FROM], [VALID_TO]) VALUES (11, N'D         ', CAST(0xDB3F0B00 AS Date), CAST(0xDC3F0B00 AS Date))

GO
    • 1