GoldenLine
Zaloguj się
Raphael Brick

Raphael Brick Vince in bono
malum...

Temat: zapytanie sql

Witam

Pytanie do specjalistów zapytań sql.

Mam tablicę w której przechowywane są zdarzenia otwierania drzwi in i out w takiej postaci :

id, date, user, event, time
1, 01/09/2014, user1, in, 09:00
2.01/09/2014, user1, out, 12:00
3, 01/09/2014, user1, in, 13:00
4, 01/09/2014, user1, out 17:00

O wybierając min i max z time mogę określić kiedy dana osoba weszła i kiedy wyszła to mam problem z wyliczeniem jak długo była na lunchu.
Jak wybrać te środkowe wartoci i wyliczyć czas poza budynkiem ?
Takich środkowych rekordów może być znacznie więcej jak zapis dotyczy palacza i lata tam i z powrotem żeby zafajczyć.

Będę wdzięczny za pomoc.

Pozdrawiam
Link do wypowiedziLink
Marcin Miga

Marcin Miga Programista. Po
prostu programista.

Temat: zapytanie sql

To bedzie coś w ten deseń:
http://sqlfiddle.com/#!1/540a0/6
W razie pytań pytaj.
Link do wypowiedziLink
Raphael Brick

Raphael Brick Vince in bono
malum...

Temat: zapytanie sql

a mógłbym prosić o wersję dla MSSql bo mam jakieś problemy z typami

dzięki serdeczne
Link do wypowiedziLink

konto usunięte

Temat: zapytanie sql

Dane testowe:


create table #events (

  eventId int not null,

  eventDate date not null,

  eventUser varchar(10) not null,

  eventType varchar(10) not null,

  eventTime time);



insert into #events 

values

  (1, '20140901','user1', 'in', '9:00:00'),

  (2, '20140901','user1', 'out', '12:00:00'),

  (3, '20140901','user1', 'in', '13:00:00'),

  (4, '20140901','user1', 'out', '17:00:00'),

  (5, '20140901','user2', 'in', '9:00:00'),

  (6, '20140901','user2', 'out', '17:00:00');


Zapytanie:


select *,  DateDiff(

    hh, 

    eventTime, 

    lead(eventTime, 1, eventTime) 

      over(partition by eventDate, eventUser order by eventTime)

  ) as [time]

from #events


Wynik:

eventId eventDate eventUser eventType eventTime time
1 2014-09-01 user1 in 09:00 3
2 2014-09-01 user1 out 12:00 1
3 2014-09-01 user1 in 13:00 4
4 2014-09-01 user1 out 17:00 0
5 2014-09-01 user2 in 09:00 8
6 2014-09-01 user2 out 17:00 0

W ostatniej kolumnie znajduje się ilość godzin jaka minęła od danego zdarzenia do czasu kolejnego (dla ostatniego zdarzenia w danym dniu jest to 0). W zapytaniu użyłem funkcji okienkowej lead dostępnej od wersji 2012. Teraz wystarczy zsumować wartości tej kolumny dla zdarzeń in (czas spędzony w budynku) i zdarzeń out (czas poza budynkiem).

Pozdrawiam
Marcin
Link do wypowiedziLink
Marcin Miga

Marcin Miga Programista. Po
prostu programista.

Temat: zapytanie sql

Rafał C.:
a mógłbym prosić o wersję dla MSSql bo mam jakieś problemy z typami

dzięki serdeczne
Problem był tego typu, że wczoraj sqlfidle nie działał dla MS SQL-a, więc zrobilem dla post. Zapominając, że ma on typ INTERVAL, którego nie ma MS SQL. Dlatego to zapytanie by nie zadziałało. Trzeba by bylo najpierw czas na sekundy zamienić, potem sumę różnic i na końcu znowu na czas...
Wprawdzie masz powyżej pewnie ziałające zapytanie, ae gdybyś się upierał, to napiszę "po mojemu" :)
Link do wypowiedziLink
Raphael Brick

Raphael Brick Vince in bono
malum...

Temat: zapytanie sql

Dokładnie tak zrobiłem, zamieniłem na sekundy. Dzięki serdeczne obu panom.

Marcin M.:
Rafał C.:
a mógłbym prosić o wersję dla MSSql bo mam jakieś problemy z typami

dzięki serdeczne
Problem był tego typu, że wczoraj sqlfidle nie działał dla MS SQL-a, więc zrobilem dla post. Zapominając, że ma on typ INTERVAL, którego nie ma MS SQL. Dlatego to zapytanie by nie zadziałało. Trzeba by bylo najpierw czas na sekundy zamienić, potem sumę różnic i na końcu znowu na czas...
Wprawdzie masz powyżej pewnie ziałające zapytanie, ae gdybyś się upierał, to napiszę "po mojemu" :)
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

zapytanie sql




Wyślij zaproszenie do
Anuluj