SQL: Filter self-loops in logistic movements

Question

I'm working on a dataset containing logistic movements of stuff. In visualising the stuff we would like to filter out the movements from e.g. A to A (this madness occurs in the dataset).

Say I have a dataset looking like this: Equipment contains the id of the stuff that is moving, FROM and TO the stockrooms, the TIME_FROM when the stuff has been moved to the FROM stockroom, and TIME_TO when the stuff has been moved to the TO stockroom.

EQUIPMENT   FROM_MAG   TO_MAG   TIME_FROM   TIME_TO
1           A          B        1           2
1           B          C        2           3
1           C          D        3           4
1           D          D        4           5
1           D          E        5           6 
1           E          F        6           7 
1           F          F        7           8 
1           F          F        8           9     
1           F          G        9           10

Than I would like an output from my query without the D-->D and two F-->F movements, but with a logical continuation of the time columns:

EQUIPMENT   FROM_MAG   TO_MAG   TIME_FROM   TIME_TO
1           A          B        1           2
1           B          C        2           3
1           C          D        3           5

1           D          E        5           6 
1           E          F        6           9 


1           F          G        9           10

I tried using queries like, but that does not give me the desired result. I'm working on SAP HANA by the way.

SELECT
EQUIPMENT,
FROM_MAG,
TO_MAG,
min(TIME_FROM),
max(TIME_TO)

FROM MOVEMENTS

GROUP BY EQUIPMENT,
FROM_MAG,
TO_MAG;

Create statement for SQL:

CREATE TABLE IF NOT EXISTS MOVEMENTS(
EQUIPMENT NVARCHAR(1) NOT NULL PRIMARY KEY
,FROM_MAG      NVARCHAR(1) NOT NULL
,TO_MAG        NVARCHAR(1) NOT NULL
,TIME_FROM NVARCHAR(1) NOT NULL
,TIME_TO   NVARCHAR(2) NOT NULL
);
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'A',N'B',N'1',N'2');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'B',N'C',N'2',N'3');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'C',N'D',N'3',N'4');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'D',N'D',N'4',N'5');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'D',N'E',N'5',N'6');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'E',N'F',N'6',N'7');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'F',N'F',N'7',N'8');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'F',N'F',N'8',N'9');
INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES 
(N'1',N'F',N'G',N'9',N'10');

score 0 · Answer 1 · answered Dec 06 '18 at 08:12

0

Checked your query, I don't think there is need for FROM_MAG in GROUP BY. I checked this in MySql, giving desired result.

SELECT
EQUIPMENT,
FROM_MAG,
TO_MAG,
min(TIME_FROM),
max(TIME_TO)
FROM MOVEMENTS
GROUP BY EQUIPMENT,
TO_MAG

answered Dec 06 '18 at 08:12

Shobhit Garg

26
2

FROM_MAG should also be in the group by according to my interpreter (SAP HANA). Doens't MySQL complain about that? – DCB Dec 06 '18 at 10:16

score 0 · Answer 2 · answered Dec 06 '18 at 13:39

I used this, and now it works:

WITH A AS(
SELECT EQUIPMENT,
     FROM_MAG,
     TO_MAG,
     TIME_FROM,
     TIME_FROM,
     TIME_TO

FROM MOVEMENTS
WHERE FROM_MAG<>TO_MAG
ORDER BY TO_NUMBER(TIME_TO))

SELECT EQUIPMENT,
     FROM_MAG,
     TO_MAG,
     TIME_FROM,
     IFNULL(LEAD(TIME_FROM) OVER(PARTITION BY EQUIPMENT ORDER BY TO_NUMBER(TIME_TO)), 
     TIME_TO) TIME_TO
FROM A;

SQL: Filter self-loops in logistic movements

2 Answers2