· 7 years ago · Dec 06, 2018, 08:04 AM
1EQUIPMENT FROM_MAG TO_MAG TIME_FROM TIME_TO
21 A B 1 2
31 B C 2 3
41 C D 3 4
51 D D 4 5
61 D E 5 6
71 E F 6 7
81 F F 7 8
91 F F 8 9
101 F G 9 10
11
12EQUIPMENT FROM_MAG TO_MAG TIME_FROM TIME_TO
131 A B 1 2
141 B C 2 3
151 C D 3 5
16
171 D E 5 6
181 E F 6 9
19
20
211 F G 9 10
22
23SELECT
24EQUIPMENT,
25FROM_MAG,
26TO_MAG,
27min(TIME_FROM),
28max(TIME_TO)
29
30FROM MOVEMENTS
31
32GROUP BY EQUIPMENT,
33FROM_MAG,
34TO_MAG;
35
36CREATE TABLE IF NOT EXISTS MOVEMENTS(
37EQUIPMENT NVARCHAR(1) NOT NULL PRIMARY KEY
38,FROM_MAG NVARCHAR(1) NOT NULL
39,TO_MAG NVARCHAR(1) NOT NULL
40,TIME_FROM NVARCHAR(1) NOT NULL
41,TIME_TO NVARCHAR(2) NOT NULL
42);
43INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES
44(N'1',N'A',N'B',N'1',N'2');
45INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES
46(N'1',N'B',N'C',N'2',N'3');
47INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES
48(N'1',N'C',N'D',N'3',N'4');
49INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES
50(N'1',N'D',N'D',N'4',N'5');
51INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES
52(N'1',N'D',N'E',N'5',N'6');
53INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES
54(N'1',N'E',N'F',N'6',N'7');
55INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES
56(N'1',N'F',N'F',N'7',N'8');
57INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES
58(N'1',N'F',N'F',N'8',N'9');
59INSERT INTO MOVEMENTS(EQUIPMENT,FROM_MAG,TO_MAG,TIME_FROM,TIME_TO) VALUES
60(N'1',N'F',N'G',N'9',N'10');