Witam!
Proszę o pomoc:
mam tabelkę z kolumnami: nazwisko, znacznik wej\wyj, data i czas (powiedzmy, że są to przerwy w pracy),
np.
Kowalski | wyjscie | 2017-03-23 1001 |
Kowalski | wejscie | 2017-03-23 1022 |
Kowalski | wyjscie | 2017-03-23 1051 |
Kowalski | wejscie | 2017-03-23 1005 |
W ciągu dnia może pojawić się wiele wpisów, tj. wyjść i wejść.
Jak skonstruować zapytanie aby uzyskać czasy, np. w sekundach, pomiędzy kolejnymi parami wyjście-wejście?
0
0
Zakładając, że tabela wygląda mniej więcej tak:
CREATE TABLE `table`
(
`name` VARCHAR(255),
`type` VARCHAR(15),
`date` DATETIME
);
INSERT INTO `table`
(`name`, `type`, `date`)
VALUES
('Kowalski', 'wejscie', '2017-03-23 10:00:01'),
('Kowalski', 'wyjscie', '2017-03-23 10:00:22'),
('Kowalski', 'wejscie', '2017-03-23 10:03:51'),
('Kowalski', 'wyjscie', '2017-03-23 10:05:05')
;
Zapytanie tego typu powinno znajdować to, o co Ci chodzi.
Gdybyś miał pytania, to chętnie odpowiem :)
SELECT
TWE.*, TWY.*
FROM
`table` TWE
JOIN `table` TWY ON TWY.name = TWE.name AND TWY.date = (
# Następny wpis dla tej samej osoby typu wyjście
SELECT
MIN(`date`)
FROM
`table`
WHERE
`name` = TWE.name
AND `date` > TWE.date
AND `type` = 'wyjscie'
)
WHERE
TWE.type = 'wejscie'
;
[Adam]
0
- Jaki silnik bazy danych?
- Czy masz kolumnę id lub coś w ten deseń?
0
W MS SQL można np. tak:
Tworzenie przykładowej tabeli:
create table #tmp
(
emp int,
operation varchar(3),
data datetime
)
insert into #tmp values (1,'wej','2014-07-31 11:25:11.000')
insert into #tmp values (1,'wyj','2014-07-31 12:25:11.000')
insert into #tmp values (1,'wej','2014-07-31 13:14:11.000')
insert into #tmp values (1,'wyj','2014-07-31 13:26:11.000')
insert into #tmp values (2,'wej','2015-06-01 15:21:11.000')
insert into #tmp values (2,'wyj','2015-06-01 15:25:11.000')
insert into #tmp values (2,'wej','2015-06-08 16:33:11.000')
insert into #tmp values (2,'wyj','2015-06-08 16:43:11.000')
Zapytanie
SELECT
t1.emp
,t1.operation
,t1.data
,(SELECT MAX(data) FROM #tmp WHERE emp = t1.emp and data < t1.data ) as previous_data
,DATEDIFF(MINUTE, (select max(data) from #tmp where emp = t1.emp and data < t1.data ), t1.data) as result_minutes
FROM #tmp AS t1
--WHERE t1.operation = 'wej'
--AND (SELECT MAX(data) FROM #tmp WHERE emp = t1.emp and data < t1.data ) IS NOT NULL
DROP TABLE #tmp
Zostawiłem 'where' zakomentowany, żebyś mógł skontrolować różnice dla całej tabeli. Po odkomentowaniu zostaną tylko wyniki pomiędzy parami wej-wyj
0
Dziękuję za odzew. To jest to czego szukałem (małe sprostowanie - najpierw wyjście, następnie wejście).
Tabela utworzona:
CREATE TABLE mm_tmp
(
emp VARCHAR(255),
operation VARCHAR(15),
DATA_ DATETIME
)
GO
INSERT INTO mm_tmp VALUES ('Kowalski','wyj','2017-07-31 11:25:11.000')
INSERT INTO mm_tmp VALUES ('Kowalski','wej','2017-07-31 12:25:11.000')
INSERT INTO mm_tmp VALUES ('Kowalski','wyj','2017-07-31 13:14:11.000')
INSERT INTO mm_tmp VALUES ('Kowalski','wej','2017-07-31 13:26:11.000')
INSERT INTO mm_tmp VALUES ('Nowak','wyj','2017-06-01 15:21:11.000')
INSERT INTO mm_tmp VALUES ('Nowak','wej','2017-06-01 15:25:11.000')
INSERT INTO mm_tmp VALUES ('Nowak','wyj','2017-06-08 16:33:11.000')
INSERT INTO mm_tmp VALUES ('Nowak','wej','2017-06-08 16:43:11.000')
GO
Zapytanie kolegi kchteam
SELECT
TWY.*, TWE.*, datediff(MINUTE, TWY.DATA_, TWE.DATA_) as [czas]
FROM
mm_tmp as TWY
JOIN mm_tmp as TWE ON TWE.emp = TWE.emp AND TWE.DATA_ = (
-- Następny wpis dla tej samej osoby typu wejście
SELECT
MIN(DATA_)
FROM
mm_tmp
WHERE
emp = TWY.emp
AND DATA_ > TWY.DATA_
AND operation = 'wej'
)
WHERE
TWY.operation = 'wyj'
zwraca:
Zapytanie kolegi pitcairn1987:
SELECT
t1.emp
,(SELECT MAX(DATA_) FROM mm_tmp WHERE emp = t1.emp AND DATA_ < t1.DATA_ ) AS [Data wyjscia]
,t1.operation
,t1.DATA_ as [Data wejscia]
,DATEDIFF(MINUTE, (SELECT MAX(DATA_) FROM mm_tmp WHERE emp = t1.emp AND DATA_ < t1.DATA_ ), t1.DATA_) AS result_minutes
FROM mm_tmp AS t1
WHERE t1.operation = 'wej' AND (SELECT MAX(DATA_) FROM mm_tmp WHERE emp = t1.emp and DATA_ < t1.DATA_ ) IS NOT NULL
zwraca:
Czyli generalnie to samo :-)
Teraz troszkę skomplikujmy :-) : załóżmy, że jest wyjście, a z jakiś powodów nie ma wejścia lub odwrotnie.
Zapytanie kolegi pitcairn1987 pomija rekord, w którym nie ma wejścia i to jest dobrze, tylko jak zrobić warunek dla braku wyjścia?
Najlepiej by było jak by pokazywało:
0
Czyli że może być np. tak:
1,wej,'2017-04-11 13:11'
1,wyj,'2017-04-11 13:11'
1,wej,'2017-05-11 15:11'
1,wej,'2017-06-11 09:11'
1,wyj,'2017-06-11 13:22'
I wtedy również obliczać różnice między:
1,wej,'2017-05-11 15:11'
a
1,wej,'2017-06-11 09:11'
0
Jeśli to MS SQL server powyżej 2012, to wynikowy zbiór (posortowany po czasie) ponumeruj sobie używając ROW_NUMBER, a potem łącz w pary parzysta-nieparzysta, gdzie jedna z nich (pewnie nieparzysta) musi być wejscie, a druga wyjscie. Proste
0
Marcin.Miga napisał(a):
Jeśli to MS SQL server powyżej 2012, to wynikowy zbiór (posortowany po czasie) ponumeruj sobie używając ROW_NUMBER, a potem łącz w pary parzysta-nieparzysta, gdzie jedna z nich (pewnie nieparzysta) musi być wejscie, a druga wyjscie. Proste
Nie zawsze są pary wyj-wej.
Faktycznie może się zdarzyć, że mamy:
1,wej,'2017-04-11 13:11'
1,wyj,'2017-04-11 13:12'
1,wej,'2017-04-11 15:13'
1,wej,'2017-04-11 15:14'
1,wyj,'2017-04-11 16:22'
Głównie zależy mi na tym aby znaleźć wyjście i pierwsze po nim wejście oraz obliczyć czas pomiędzy nimi. Jeżeli w tabeli będzie kolejno wejście, wejście, to je pominąć i jak trafimy na wyjście to znowu znaleźć pierwsze po nim wejście, itd.
I Twoje zapytanie robi to dobrze, w wyniku otrzymujemy:
Kowalski | wyj | 2017-04-11 13:12 | wej | 2017-04-11 15:13 | 121.
I za to dziękuję, bardzo mi to pomogło.
Tylko tak jak pisałem wyżej idealnie by było otrzymać taki (przykładowy) wynik:
0
Na postgeSQL:
with tabela as
(
select uid, op, czas::timestamp(0) from (
values(1,'wej','2017-04-11 13:11'),(1,'wyj','2017-04-11 13:12'),(1,'wej','2017-04-11 15:13'),(1,'wej','2017-04-11 15:14'),(1,'wyj','2017-04-11 16:22')
) as x(uid, op, czas)
),
z_lp as
(
select *, row_number()over(order by czas) lp
from tabela
)
select * from z_lp t_wej left join z_lp t_wyj on t_wej.lp=t_wyj.lp-1 and t_wyj.op='wyj'
where t_wej.op='wej'
0
Poniżej zapytanie dla przypadku, gdy może nie być wyjścia lub wejścia. Order w tej formie dla większej liczby danych będzie powolny - jeśli go nie będzie, to przypadki gdy nie ma "wyjścia" będą na samym końcu wyników.
Ogólnie pierwsza część zapytania (przed UNION ALL) odpowiada za wyświetlenie wyników w wersji, gdy jest para i gdy nie ma "wejścia", a druga część tylko, gdy nie ma "wyjścia".
Zapytanie napisane dla MySQL.
SELECT
TWY.emp, TWY.operation, TWY.DATA_ AS date_out, TWE.operation, TWE.DATA_ AS date_in, DATEDIFF(TWY.DATA_, TWE.DATA_) AS '[czas]'
FROM
mm_tmp AS TWY
LEFT JOIN mm_tmp AS TWE ON TWE.emp = TWE.emp AND TWE.DATA_ = (
-- Następny wpis dla tej samej osoby typu wejście
SELECT
MIN(DATA_)
FROM
mm_tmp
WHERE
emp = TWY.emp
AND DATA_ > TWY.DATA_
) AND TWE.operation = 'wej'
WHERE
TWY.operation = 'wyj'
UNION ALL
SELECT
TWY.emp, TWY.operation, TWY.DATA_ AS date_out, TWE.operation, TWE.DATA_ AS date_in, DATEDIFF(TWY.DATA_, TWE.DATA_) AS '[czas]'
FROM
mm_tmp AS TWE
LEFT JOIN mm_tmp AS TWY ON TWY.emp = TWE.emp AND TWY.DATA_ = (
SELECT
MAX(DATA_)
FROM
mm_tmp
WHERE
emp = TWE.emp
AND DATA_ < TWE.DATA_
) AND TWY.operation = 'wyj'
WHERE
TWE.operation = 'wej'
AND TWY.emp IS NULL
ORDER BY
IFNULL(date_out,date_in)
;
[Adam]
0
Jesteś wielki :-) Bardzo dziękuję. To jest to czego szukałem - teraz tylko muszę ten zapis dokładnie zrozumieć.
Wywala mi tylko błąd przy: IFNULL(date_out,date_in). Bez tego działa.
Jeszcze raz dziękuję.