Obtenga una lista de rutas distintas ordenadas por fecha en SQL
Tengo datos que se parecen a esto:
| factura | departamento | fecha |
|---|---|---|
| 12345 | Transferir facturas | 2022-03-05 |
| 12345 | Gestión de proveedores | 2023-07-15 |
| 12345 | Gobierno | 2023-07-29 |
| 12345 | Gestión de proveedores | 2023-09-16 |
| 12345 | Gobierno | 2023-11-25 |
| 12345 | Gobierno | 2023-12-30 |
| 12345 | Gobierno | 2024-02-03 |
Lo que estoy tratando de recuperar es lo siguiente:
| factura | departamento | fecha |
|---|---|---|
| 12345 | Transferir facturas | 2022-03-05 |
| 12345 | Gestión de proveedores | 2023-07-15 |
| 12345 | Gobierno | 2023-07-29 |
| 12345 | Gestión de proveedores | 2023-09-16 |
| 12345 | Gobierno | 2023-11-25 |
Estaba pensando en intentar hacer un avance o un retraso y ver si el siguiente/anterior había cambiado. Estoy intentando obtener una ruta de una factura.
Puede utilizar LAG dentro de un CTE para comparar el departamento de cada fila deptcon el departamento de la fila anterior para la misma factura, ordenados por fecha. Puede ver que creé una nueva columna PrevDeptpara contener el valor del departamento de la fila anterior.
Entonces, selecciono las filas donde PrevDeptse seleccionan NULL o son diferentes de la actual dept(excluyendo las del mismo departamento y dejando solo la primera aparición en cada serie de filas consecutivas):
CREATE TABLE InvoiceData (
invoice INT,
dept VARCHAR(50),
[date] DATE
);
INSERT INTO InvoiceData (invoice, dept, date)
VALUES
(12345, 'Xfer Bills', '2022-03-05'),
(12345, 'Vendor Mgmt', '2023-07-15'),
(12345, 'Government', '2023-07-29'),
(12345, 'Vendor Mgmt', '2023-09-16'),
(12345, 'Government', '2023-11-25'),
(12345, 'Government', '2023-12-30'),
(12345, 'Government', '2024-02-03');
WITH CTE AS (
SELECT
invoice,
dept,
date,
LAG(dept, 1) OVER(PARTITION BY invoice ORDER BY [date]) AS PrevDept
FROM InvoiceData
)
SELECT
invoice,
dept,
[date]
FROM CTE
WHERE PrevDept IS NULL OR PrevDept <> dept;
| factura | departamento | fecha |
|---|---|---|
| 12345 | Transferir facturas | 2022-03-05 |
| 12345 | Gestión de proveedores | 2023-07-15 |
| 12345 | Gobierno | 2023-07-29 |
| 12345 | Gestión de proveedores | 2023-09-16 |
| 12345 | Gobierno | 2023-11-25 |
violín
En el CTE se CASE WHENcomprueba el nombre del departamento con el anterior ( LAG) y las marcas cambian. también marca la primera fila de la factura, que tiene el LAGsímbolo de NULLpara que pueda obtener todos los departamentos únicos,
CREATE TABLE invoice (
[invoice] INTEGER,
[dept] VARCHAR(11),
[date] DATE
);
INSERT INTO invoice
(invoice, dept, [date])
VALUES
('12345', 'Xfer Bills', '2022-03-05'),
('12345', 'Vendor Mgmt', '2023-07-15'),
('12345', 'Government', '2023-07-29'),
('12345', 'Vendor Mgmt', '2023-09-16'),
('12345', 'Government', '2023-11-25'),
('12345', 'Government', '2023-12-30'),
('12345', 'Government', '2024-02-03');
7 rows affected
WITH CTE AS (SELECT
invoice, dept, date
, CASE WHEN dept <> LAG(dept) OVER( PARTITION By invoice ORDER By date )
OR LAG(dept) OVER( PARTITION By invoice ORDER By "date" ) IS NULL THEN 1 ELSE 0 END is_valid
FROM invoice)
SELECT invoice, dept, date FROM CTE WHERE is_valid = 1
| factura | departamento | fecha |
|---|---|---|
| 12345 | Transferir facturas | 2022-03-05 |
| 12345 | Gestión de proveedores | 2023-07-15 |
| 12345 | Gobierno | 2023-07-29 |
| 12345 | Gestión de proveedores | 2023-09-16 |
| 12345 | Gobierno | 2023-11-25 |
violín
