SQL - Liczenie czasu pracy z przerwami

Cześć, tworze mój pierwszy mały program i mam problem aby poprawnie zliczać faktyczny czas pracy maszyny, a mianowicie
posiadam następująca dane z bazy SQL w której pomiary są wykonywane co 1 min:

gdzie :
WorkDateTime - czas pomiarów
BeltMoveTime =1 maszyna włączona
BeltMoveTime = 0 maszyna wyłączona

chcąc użyć polecenie *DATEDIFF(MINUTE,'2021-09-10 09:44:57.000' ,'2021-09-10 09:57:57.000' ) *
otrzymujemy wynik 13 min co nie uwzględnia czasu przerwy,
a rzeczywisty czas pracy wynosi 7 minut

a przecież od 2021-09-10 09:46:57.000 do 2021-09-10 09:52:57.000 maszyna nie działała
i tutaj rodzi się pytanie jak poprawnie liczyć rzeczywisty czas pracy maszyny uwzględniając, iż te przerwy
występują nieregularnie i z różnym czasem postoju, w czasie całego dnia ?

Jak już musisz robić legendę do zmiennych to idzie w zła stronę. Czemu nie nazwiesz kolumny is_on? Druga rzeczy to czemu nie zanotujesz startu i wyłączenia?

To są dane pobrane z bazy danych więc pozostawiłem oryginale nazewnictwo,
Start maszyny czy tez wyłącznie końcowe maszyny łatwo można okreslić zapytaniami MAX(), MIN() lecz daje pozornie tylko poprawny wynik gdyż,
nie uwzględnia nieprzewidzianych przerw w działaniu.

Dlatego pytam czy ktoś zna dobre rozwiązanie aby obliczyć rzeczywisty czas pracy wraz z niespodziewanymi przerwami.
Których nie da się wpisać gdyż nigdy nie wiemy kiedy wystąpią i jak długo będą trwać.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/27ec5367-cd23-4ffe-adb9-888c22b85dde/sum-the-time-between-dates-on-different-records?forum=sqlgetstarted

No to wez policz DATEDIFF dla maszyn ze statusem = 1 i zsumuj to samo ze stausem = 0 i odejmij i bedziesz mial czas pracy minus czas przew i juz 5 sekund roboty

Można by tak zrobić jeśli by była tylko jedna przerwa w ciągu dnia.
a co jeśli będzie 2 lub 3 przerwy ?
a co jesli będziemy mieli 2 lub więcej urządzeń?

Szukam roziązania tak aby nie sprawdzać danych z każdego dnia, tylko aby automatycznie to robić.

Przykład z kilku minut a pomiary są wykonywane przez ok 15 godz dziennie, przez cały miesiąc, więc 'masterc' Twoje rozwiąznie nie ma sensu .

Najpierw musisz dociągnąć wartość z następnego (poprzedniego zależy, w którą stronę będziesz liczył) : https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-lag-function/ i do tego casem sprawdzasz, czy pracowała, czy nie. Jak nie bierzesz 0 jak tak to bierzesz różnicę dat z dwóch poprzedzających się rekordów. Dodatkowo musisz sprawdzić, czy twój zakres żadnych warunków nie łapie się w środku bloku - wóczas początek lub koniec bloku musisz podmienić na datę z warunku.

Najpierw musisz ogarnąć logikę w swoich założeniach

a przecież od 2021-09-10 09:46:57.000 do 2021-09-10 09:52:57.000 maszyna nie działała

czyli mimo ze dla 2021-09-10 09:46:57.000 status jest 1 to i tak nie pracowala ? to w koncu kiedy wiadomo jak maszyna pracuje? podczas zmiany stanu czy jak ?
bo jak dla mnie to maszyna nie rpacowala 1 minute a 12 minut pracowala

Czy jak mamy przejścei z 1-0 to liczymy jako czas pracy czy przestoju i analogicznie jak liczyć z 0-1?

Nie rozumiem dlaczego czepiasz się mojej logiki ? a jak już się czepiasz to dobrze przeanalizuj przykład

dane normalnie są podawane co 1 min lecz czasem podczas przerwy nie jest to zachowane

Dla uproszczenia przyjmijmy że jeśli BeltMoveTime = 0 to maszyna nie działa a BeltMoveTime = 1

No to jak dane są co minutę, to liczysz ilość wierszy z
BeltMoveTime = 1 poprzez zwykły count i masz ilość minut pracy. Z ilości minut możesz sobie obliczyć godziny czy dni czy co tam potrzebujesz.

Mlody_pb napisał(a):

Dla uproszczenia przyjmijmy że jeśli BeltMoveTime = 0 to maszyna nie działa a BeltMoveTime = 1

Ale to nie my mamy sobie przyjmowac tylko jaki jest logiczny tan faktyczny ? Jeżeli mi wyszło ze maszyna pracuje 12 minut a ma minute przerwy a tobie wyszlo ze maszyna pracowala 7 minut no panie kochany ? to skad ci wyszlo 7 minut skoro przy czasie
id=18373 stan maszyny jest = 1 a ty liczysz tutaj ze ona juz nie dziala. Bo albo bedziesz bral znaczniki zmiany stanu z 0 na 1 i odwrotnie albo zliczasz same 1 i 0

Co minutę mamy odczyt jeśli maszyna pracuje, sprawa się komplikuje gdyż jeśli maszyna jest wyłączona nie mamy odczytu co minutę tylko odczyt zaczyna się jak maszyna znów zostanie włączona.

Jakieś propozycje kodu ? teoretycznie wiem jak rozwiązać ten problem gożej z realizacjią gdyż dopiero zaczynam swoją pzrygodę z SQL

Po screenie widzę że to mssql, po tagach nie mam pojęcia w czym to piszesz, jakiego kodu się spodziewasz?

wszytsko jest w MS SQL

Najprościej to jest SELECT COUNT(id) WHERE id_maszyny = costam AND status = 1 i juz. z obrazka wynika ze jak maszyna pracuje to kazda minuta to status 1 wiec mozesz zignorowac te statusy z 0 i juz. nie komplikuj

@Mlody_pb: Czy możemy założyć, że maszyna pracująca ZAWSZE melduje się co minutę? innymi słowy, czy dla maszyny pracującej bez przerwy przez całą dobę na pewno będziemy mieć 1440 wpisów? Bo od tego zależy, czy możemy oprzeć się na zliczaniu meldunków i ich serii, czy też musimy wyliczać różnice w czasach meldunków, a to pierwsze podejście zapewne będzie o wiele wydajniejsze

Chcę zrobić aby liczyć różnice w czasach jeśli wystąpi przerwa, myślałem nad tym aby zliczać 1 lecz, nie jestem pewny czy zawsze maszyna będzie zliczała co min, w razie jakiegoś błędu czy tez złego odczytu z bazy wyjda nam złe wyniki, dletego próbuje to zrobic bezpiecznie z czasem.
I tutaj zaczyna się zagwozdka jak to poprawnie zrobić.

To może przygotuj dane w postaci sql, albo wklej jak tekst...
I odpowiedz na pytanie jak zliczać czas jeżeli następuje zmiana stanu

w Tym problem iż nie mamy100% pewności że zawsze jak maszyna działa jest pomiar co 1 min, niestety jak nie działa to pomiary sa różne.

Z tego co do tej pory zrozumiałem to jeśli mamy pzrejście stanu z 1 na 0 to maszyna nie działa a jeśli z 0 na 1 to maszyna działa.

Kolejny problem jest taki ze mamy różne pomiary na poczatku dnia i na końcu np:
Maszyna włacza się i działa z pierwszym pomiarem

lecz niestety czasem jest stytuacja że mamy kilka pomiarów z wartością 0 czyli maszyna nie działa i dopiero po jakimś czasie zaczyna działać

i z zakończeniem pracy jest podobnie.

do tego mamy przerwy w pomiarach jak maszyna nie działa

Jeśli nie było by takich komplikacji to wystarczyło by uzyć

DATEDIFF (MINUTE, MIN (WorkDateTime), MAX (WorkDateTime))

lub

SUM (BeltMoveTime)
where BeltMoveTime = 1

Pisane z palca mogą być jakieś literówki.

select sum( t.c)
from  ( select 
CASE 
    WHEN BeltMoveTime  =  0 THEN 0 
    WHEN BeltMoveTime = 1 THEN DATEDIFF (MINUTE, MIN (WorkDateTime), MAX (LEAD(WorkDateTime,1) OVER (ORDER BY WorkDateTime))) 
 
    ELSE 0
END c
from tabela) as t

Przepraszam skopiowałem przez pomyłkę złą linijke

Trochę na kolanie, bardzo nieelegancko, trochę partyzancko, trochę na piechotę:

SELECT VehicleId
	,SUM(datediff(second, workdatetime, leadWDT)) / 60.0
FROM (
	SELECT *
		,LEAD(workdatetime) OVER (
			PARTITION BY vehicleid ORDER BY workdatetime
			) leadWDT
	FROM (
		SELECT *
		FROM (
			SELECT *
				,COALESCE(LAG(beltmovetime) OVER (
						PARTITION BY vehicleId ORDER BY workdatetime
						), 0) lagBMT
				,COALESCE(LEAD(beltmovetime) OVER (
						PARTITION BY vehicleId ORDER BY workdatetime
						), 0) leadBMT
			FROM aa4p
			) x
		WHERE (beltmovetime = 1 AND (lagBMT = 0 OR leadBMT = 0)) OR (BeltMoveTime = 0 AND leadBMT = 1)
		) y
	) z
WHERE beltmovetime = 1 AND leadBMT = 1
GROUP BY vehicleId