Не могу правильно написать запрос!

← →
unicode © (2004-03-27 12:02) [0]

Есть таблица с полями id,cardnumber,event,dateandtime поле event показывает человек зашел или вышел а что есть значение допустим для зашел 0 а для вышел 1 и дата вхождения или выхода...

допустим
id cardnumber event dateandtime
1 100 0 2004-01-01 17:00:00
2 100 1 2004-01-01 20:15:00
3 101 0 2004-01-01 08:00:00
4 101 1 2004-01-01 12:10:00
5 100 0 2004-01-02 08:00:00
6 100 1 2004-01-02 15:00:00
7 101 0 2004-01-02 07:00:00
8 101 1 2004-01-02 16:30:00
задача вывести с 01 по 02 число номера карт которые вошли и вышли вот в таком порядке............
card event dateandtime event dateandtime
100 0 2004-01-01 17:00:00 1 2004-01-01 20:15:00
101 0 2004-01-01 08:00:00 1 2004-01-01 12:10:00
100 0 2004-01-02 08:00:00 1 2004-01-02 15:00:00
101 0 2004-01-02 07:00:00 1 2004-01-02 16:30:00
причем есть вероятность что человек вошедший мог не выйти .. или наоборот..
тогда какая-то из записей должна выйти как NULL. Если кто поможет буду благодарен

← →
Nikolay M. © (2004-03-27 12:19) [1]

SELECT
t1.cardnumber,
(SELECT dateandtime FROM table t2 WHERE t1.cardnumber = t2.cardnumber AND event = 0) AS in_dateandtime,
(SELECT dateandtime FROM table t2 WHERE t1.cardnumber = t2.cardnumber AND event = 1) AS out_dateandtime
FROM table t1
WHERE dateandtime BETWEEN xxx AND yyy

← →
Nikolay M. © (2004-03-27 12:24) [2]

Нет, глупость написал. Доработать напильником надо...

← →
unicode © (2004-03-27 12:35) [3]

вроде должно было помочь но вот возвращает таку ошибку

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

← →
Nikolay M. © (2004-03-27 13:15) [4]

> Subquery returned more than 1 value

Правильно пишет. Добавь в подзапросы MIN (dateandtime) и в условия подзапросов AND t1.dateandtime <= t2.dateandtime. Хотя это тоже прокатит не во всех случаях :(

← →
unicode © (2004-03-27 13:29) [5]

да но так он возвращает только одну запись а если номеров карточек многго.. как тогда? неужели нет этому решения?

← →
unicode © (2004-03-27 13:31) [6]

то есть не одну а только на один день... то есть не имеет разницы какая дата.. он выбирает из диапазона или самую первую или самую высокую то есть в зависимости от max and min

← →
Nikolay M. © (2004-03-27 14:36) [7]

Имхо, лучше, наверное процедурку с курсором сообразить. И проще будет, и быстрее, чем двухэтажные запросы городить.

← →
MOA © (2004-03-27 14:37) [8]

SELECT t.cardnumber, t.event, t.dateandtime,
t.cardnumber, t.event, t.dateandtime
FROM tab t INNER JOIN tab t1 ON (t1.cardnumber = t.cardnumber)
AND t1.dateandtime=(SELECT MIN(dateandtime) FROM tab t3 WHERE (t3.dateandtime > t.dateandtime) AND (t3.event=1))
WHERE (t.event = 0)
ORDER BY t.dateandtime
Не подойтёт?

← →
MOA © (2004-03-27 14:39) [9]

Пардон, забыл t1:

SELECT t.cardnumber, t.event, t.dateandtime,
t1.event, t1.dateandtime
FROM tab t INNER JOIN tab t1 ON (t1.cardnumber = t.cardnumber)
AND t1.dateandtime=(SELECT MIN(dateandtime) FROM tab t3 WHERE (t3.dateandtime > t.dateandtime) AND (t3.event=1))
WHERE (t.event = 0)
ORDER BY t.dateandtime

← →
MOA © (2004-03-27 14:40) [10]

Да что такое, блин! Вот окончательно:

SELECT t.cardnumber, t.event, t.dateandtime,
t1.event, t1.dateandtime
FROM tab t INNER JOIN tab t1 ON (t1.cardnumber = t.cardnumber)
AND t1.dateandtime=(SELECT MIN(dateandtime) FROM tab t3 WHERE (t3.cardnumber = t.cardnumber) AND (t3.dateandtime > t.dateandtime) AND (t3.event=1))
WHERE (t.event = 0)
ORDER BY t.dateandtime

← →
unicode © (2004-03-27 14:54) [11]

MOA----->>>>>>> результат выборки 0 :(

← →
MOA © (2004-03-27 14:56) [12]

И, наконец, если
>есть вероятность что человек вошедший мог не выйти .. или наоборот
то так (@SD и @ED - соотв. даты начала и конца периода):

SELECT t.cardnumber, t.event, t.dateandtime,
t1.event, t1.dateandtime
FROM tab t LEFT OUTER JOIN tab t1 ON (t1.cardnumber = t.cardnumber)
AND t1.dateandtime=(SELECT MIN(dateandtime) FROM tab t3 WHERE (t3.cardnumber = t.cardnumber) AND (t3.dateandtime > t.dateandtime) AND (t3.event=1) AND (t3.dateandtime BETWEEN @SD AND @ED))
WHERE (t.event = 0) AND (t.dateandtime BERTWEEN @SD AND @ED)
UNION
SELECT t4.cardnumber, NULL, NULL,
t4.event, t4.dateandtime
FROM tab t4
WHERE (t4.event = 1) AND
(t4.dateandtime BETWEEN @SD AND @ED) AND NOT EXISTS (SELECT * FROM tab t5 WHERE (t5.event = 0) AND (t5.cardnumber = t4.cardnumber) AND (t5.dateandtime < t4.dateandtime ) AND (t5.dateandtime >= @SD))
ORDER BY t.dateandtime

Вроде, теперь всё. К сожалению, проверить сейчас негде, но суть понятна...
Удачи!

> MOA © (27.03.04 14:56) [12]

Вот я тоже еще немного подумал и понял, что без запроса такой этажности не обойтись. Так что лучше все-таки процедуркой оформить.

ок так получаеться .. но вот теперь еще проблема я только что узнал что выходов и входов может быть два.. т.е 0 и 2 это вхож а 1 и 3 это выход.. как тогда сделать? это последняя прозьба..

2Nikolay M.
Ну да, подобный запрос стоит оформить в функцию (если SQL 2000), однако, IMHO, курсор - это слишиком крайнее средство в запросах подобной сложности. IMHO, курсор стоит использовать, когда либо без него запрос получается уж через чур мострообразным (напр., "не помещается в экране"), либо когда мы очень хотим выделить некую общую логику в функциях - а в MSSQL в параметрах функции-участнике запроса нельзя использовать поля, только переменные и константы - тут уж без курсора никак.
2unicode
Самый простой путь - объедините при помощи UNION ещё один такой же запрос, но с другими кодами - вот и всё.
Удачи!

>0 и 2 это вхож а 1 и 3 это выход
хм, ну тогда
t3.event=1
менять
t3.event in (1,3)
дальше так же,..

2Opuhshii
Удачная идея, но,к сожалению, так пойдёт только тогда, если человек может зайти на 1-м а выйти на 2-м и наоборот. Вдобавок, MSSQL не очень-то нравится "ИЛИ" - он, зараза, норовит сделать полное соединение.

>допустим

Недопустимо смешивать разные сущности в одной таблице, особенно, когда приходится так извращаться для "восстановления" связи

Так будет проще:

Enters
id cardnumber dateandtime
1 100 2004-01-01 17:00:00
3 101 2004-01-01 08:00:00
5 100 2004-01-02 08:00:00
7 101 2004-01-02 07:00:00

Exits:
id cardnumber enter dateandtime
2 100 1 2004-01-01 20:15:00
4 101 3 2004-01-01 12:10:00
6 100 5 2004-01-02 15:00:00
8 101 7 2004-01-02 16:30:00

Не могу правильно написать запрос! Найти похожие ветки