Запрос MSSQL про наработанное время в поле DATETIME

← →
Павел (2004-10-05 09:14) [0]

Уважаемые, Мастера !
Помогите составить сложнейший запрос.
Есть таблица MSSQL
logtime tagname tagvalue
2004-08-20 08:42:29.000 Y4_41 1
2004-08-20 08:43:29.000 Y5_41 1
2004-08-20 08:48:47.000 Y4_41 0
2004-08-20 09:11:12.000 Y4_41 1
2004-08-20 09:13:31.000 Y4_41 0
Как подсчитать наработанное время, в данном примере таблицы, так, чтобы по полю TAGNAME по значению Y4_41 выбрало TAGVALUE 1
т.е. грубо говоря посчитало так
tagname tagvalue
1 строка стоит у нас Y4_41 и 1
3 строка стоит у нас Y4_41 и 0
время получится 6 мин 18 сек.
и т.д.
вот как мне посчитать это наработанное время согласно такому вот (мне кажется непонятно как я обьяснил) условию...
Получается в итоге:
Y4_41 0 = 6.18+1.19 = 7 мин 37 сек

← →
Fay © (2004-10-05 09:20) [1]

Бред какой-то. Структуру данных с описанием и русифицированный текст задачи, будьте любезны.

← →
ЮЮ © (2004-10-05 09:33) [2]

Как понимаешь, только "на пальцах" иожно объяснить, ято первой строке должна соответствовать 3-я, а не 5-я, которая, в принципе, только временем отличается, поэтому для связи на уровне сервера - данных мало (связывать по времени, которое можно найти из агрегирующего подзапроса, выполняемого для каждой записи таблицы - занятие неблагодарное даже для MS SQL)

← →
Skyle © (2004-10-05 09:54) [3]

telepathy.Enabled := true;

Есть мнение, что TagValue - состояние какого-то измерения. 0 - выключено (что-то), 1 - включено, TagName - это идентификатор этого самого нечто.
Возможно хочется найти время, в течение которого работало нечто с идентификатором таким-то.

Моделирую.
CREATE TABLE #T (Value datetime, AName varchar(10), AId INT)

INSERT #T VALUES("2004-08-20 08:42:29.000", "Y4_41", 1)
INSERT #T VALUES("2004-08-20 08:43:29.000", "Y5_41", 1)
INSERT #T VALUES("2004-08-20 08:48:47.000", "Y4_41", 0)
INSERT #T VALUES("2004-08-20 09:11:12.000", "Y4_41", 1)
INSERT #T VALUES("2004-08-20 09:13:31.000", "Y4_41", 0)

SELECT Tag, CAST(Sum(Val) AS datetime)
FROM (SELECT CASE WHEN AId = 0 THEN CAST(Value AS float)
ELSE CAST(Value AS float) * -1
END AS Val, AName AS Tag
FROM #T
WHERE AName = "Y4_41") T --Такой Where потому что Y5_41 не выключен и будет минус
GROUP BY Tag

DROP TABLE #T

Всё, что выше 1900.01.01 00:00:00.0000 и будет искомое время.
Точность, конечно, не ахти какая, но тут главное понять, правильно я вопрос понял или нет....

Возможно для повышения точности подойдёт что-то типа timeStamp...

← →
Fay © (2004-10-05 10:04) [4]

>> Возможно для повышения точности подойдёт что-то типа timeStamp...

Не, это не прокатит. Как и BLOB 8)

← →
Skyle © (2004-10-05 10:06) [5]

> [4] Fay © (05.10.04 10:04)
Почему?

← →
Johnmen © (2004-10-05 10:06) [6]

Примерно так:
SELECT T1.tagname, SUM(T2.logtime-T1.logtime) FROM Table T1, Table T2 WHERE (T2.tagname=T1.tagname) AND (T2.logtime>=T1.logtime) AND (T1.tagvalue=1) AND (T2.tagvalue=0) AND (T2.logtime=(SELECT MIN(T3.logtime) FROM Table T3 WHERE (T3.tagname=T1.tagname) AND (T3.logtime>=T1.logtime) AND (T3.tagvalue=0))) GROUP BY T1.tagname

← →
АлексейК (2004-10-05 10:47) [7]

(мне кажется непонятно как я обьяснил)

Будте уверены, это вам не кажется.

Однако, если рассудить логически, то возможно будет так:

SELECT SUM(sm) FROM
(SELECT
CASE WHEN tagvalue=1 THEN DATEDIFF(..., a.logtime, (select min(logtime) from table b where b.logtime>a.logtime and tagvalue=0)) else 0 end
as sm
FROM table a WHERE tagname=Y4_41) lv
Конечно, если по логике возможны входы без регистрации или выходы нужно учитывать еще данный вариант (у нас на работе после 18,00 отключают турникет и выдти можно свободно, по какой логике они подсчитывабт время - неизветсно, видимо в программе что-то предусмотрено). Если нужно можно еще добавить подсчет времени по текущей регистрации.

← →
Fay © (2004-10-05 11:11) [8]

2 Skyle © (05.10.04 10:06) [5]
Посмотрите, что сказано в BOL по поводу TIMESTAMP. Не думаю, что вы имели ввиду то, что сказали 8)

← →
Skyle © (2004-10-05 11:20) [9]

> [8] Fay © (05.10.04 11:11)
> Не думаю, что вы имели ввиду то, что сказали
Что имел, то и сказал. Но вот подразумевал не BOL, а Delphi Help.

Use TTimeStamp to represent date and time values when a great deal of accuracy is required for the time portion.

Так и сказано, что типа TimeStamp. Правда не уточнял в каком понимании, за что и каюсь.

← →
MOA © (2004-10-05 11:36) [10]

Ещё вариантик
CREATE TABLE #t (Value datetime, tagname varchar(10), tagvalue INT) INSERT #t VALUES("2004-08-20 08:42:29.000", "Y4_41", 1) INSERT #t VALUES("2004-08-20 08:43:29.000", "Y5_41", 1) INSERT #t VALUES("2004-08-20 08:48:47.000", "Y4_41", 0) INSERT #t VALUES("2004-08-20 09:11:12.000", "Y4_41", 1) INSERT #t VALUES("2004-08-20 09:13:31.000", "Y4_41", 0) INSERT #t VALUES("2004-08-20 09:15:31.000", "Y5_41", 0) SELECT t1.tagname, SUM(DATEDIFF(mi, t1.Value, t2.Value)) FROM #t t1 INNER JOIN #t t2 ON (t2.tagname=t1.tagname) AND (t2.Value = (SELECT MIN(t3.Value) FROM #t t3 WHERE (t3.tagname=t1.tagname) AND (t3.tagvalue=0) AND (t3.Value > t1.Value))) WHERE (t1.tagvalue=1) GROUP BY t1.tagname DROP TABLE #t
Предполагается, что не может быть одинакового времени прихода-ухода для одного тэга. Желательны индексы по Value и (составной) по tagname + tagvalue
Удачи!

← →
MOA © (2004-10-05 11:59) [11]

2Johnmen
Однако, как похоже-то ;)))

← →
Johnmen © (2004-10-05 12:10) [12]

>MOA © (05.10.04 11:59) [11]

По сути - то же самое :)

← →
Павел (2004-10-05 15:13) [13]

Интернета не было всё это время...
Итак, почитал тут всё что пишется.
В принципе всё правильно поняли...
tagname - char(64) - Исполнительный механизм (ИМ)
tagvalue - int(4) - режим работы ИМ
Вот мне надо посчитатать сколько времени ИМ Y4_41 проработал в дистанционном режиме (tagvalue=1), а сколько в ручном(tagvalue=1)...
У меня вопрос к ващим составленным запросам . Почему Johnmen © и MOA © упоминаете про две таблицы т.е T1 b T2 ? У меня ж одна вроде как таблица, скажем с именем S...

← →
Johnmen © (2004-10-05 15:15) [14]

Это два разных алиаса (псевдонима) для одной и той же таблицы...

← →
Павел (2004-10-05 15:27) [15]

поправляюсь
************************
Вот мне надо посчитатать сколько времени ИМ Y4_41 проработал в дистанционном режиме (tagvalue=1), а сколько в ручном(tagvalue=О)...

← →
Павел (2004-10-05 15:41) [16]

Johnmen © что-то у меня ругается....

SELECT T1.tagname, SUM(T2.logtime-T1.logtime)
FROM s4_exec T1, s4_exec T2
WHERE (T2.tagname=T1.tagname) AND
(T2.logtime>=T1.logtime) AND
(T1.tagvalue=1) AND
(T2.tagvalue=0) AND
(T2.logtime=(SELECT MIN(T3.logtime)
FROM S4_exec T3
WHERE
(T3.tagname=T1.tagname) AND
(T3.logtime>=T1.logtime) AND
(T3.tagvalue=0)))
GROUP BY T1.tagname
****************************
Server: Msg 409, Level 16, State 2, Line 1
The sum or average aggregate operation cannot take a datetime data type as an argument.

?????

← →
Johnmen © (2004-10-05 16:01) [17]

Павел (05.10.04 15:41) [16]

Ну почитай BOL про работу с датами, наконец.
Посмотри пример MOA © (05.10.04 11:36) [10]

← →
Павел (2004-10-05 16:12) [18]

>тоже ругается...

Как и на что ?

← →
Павел (2004-10-05 16:24) [20]

SELECT t1.tagname, SUM(DATEDIFF(mi, t1.Value, t2.Value))
FROM s4_exec t1 INNER JOIN s4_exec t2 ON (t2.tagname=t1.tagname)
AND (t2.Value = (SELECT MIN(t3.Value) FROM s4_exec t3 WHERE (t3.tagname=t1.tagname) AND (t3.tagvalue=0) AND (t3.Value > t1.Value)))
WHERE (t1.tagvalue=1)
GROUP BY t1.tagname

Server: Msg 207, Level 16, State 3, Line 15
Invalid column name "Value".
Server: Msg 207, Level 16, State 1, Line 15
Invalid column name "Value".

М-дя...
С таблицей ты разобрался. А с полем ???!!!

← →
Павел (2004-10-05 16:33) [22]

С полем наверное нет...
VALUE не пойму что здесь значит...???

Значит имя поля, которое не допустимо в данном запросе.

← →
Павел (2004-10-06 09:53) [24]

Разбираюсь с этим запросом...

SELECT t1.tagname, SUM(DATEDIFF(mi, t1.logtime, t2.logtime))
FROM s4_exec t1 INNER JOIN s4_exec t2 ON (t2.tagname=t1.tagname)
AND (t2.logtime = (SELECT MIN(t3.logtime) FROM s4_exec t3 WHERE (t3.tagname=t1.tagname) AND (t3.tagvalue=0) AND (t3.logtime > t1.logtime)))
WHERE (t1.tagvalue=1)
GROUP BY t1.tagname

Выдаёт мне ответ
Y4_41 8
Y5_41 32
Вот терь пытаюсь понять, что тут насуммировалаось 8 и 32 ?
Задача у меня нассумировать время ......

Вот я сижу и думаю, ты тормоз по жизни или просто лень полностью тебя разложила ? Что ты не можешь элементарно посмотреть в хелпе про DATEDIFF ?

Запрос MSSQL про наработанное время в поле DATETIME Найти похожие ветки