Реализация справочника с хранением записей по датам

[RuNTiME]

в общем задача в следующем, создать справочник(таблицу), который хранит историю изменения записей по датам.
1.
На входе REC_ID записи и дата актуальности(ACTUAL_DATE) записи.
На выходе запись актуальная на заданную дату
2.
На входе дата актуальности(ACTUAL_DATE).
На выходе список(актуальных) не закрытых записей на эту дату.

В качестве СУБД использую MySQL.

Для экспериментов создал вот такую таблицу:

Код:

+----+--------+-------------+------------+
| ID | REC_ID | ACTUAL_DATE | CLOSE_DATE |
+----+--------+-------------+------------+
|  1 |      1 |           1 |         10 | 
|  2 |      1 |           2 |         10 | 
|  3 |      1 |           3 |         10 | 
|  4 |      1 |           4 |         10 | 
|  5 |      1 |           5 |         10 | 
|  6 |      1 |           6 |         10 | 
|  7 |      1 |           7 |         10 | 
|  8 |      1 |           8 |         10 | 
|  9 |      1 |           9 |         10 | 
| 10 |      1 |          10 |         10 | 
| 11 |     11 |          10 |          0 | 
| 12 |     11 |          15 |          0 | 
| 13 |     11 |          16 |          0 | 
| 14 |     11 |          17 |         17 | 
+----+--------+-------------+------------+

Описание полей:
ID - первичный ключ
REC_ID - уникальный ID записи, присваиваетя при первом вводе записи методом копирования из поля ID. При изменении записи он дублируется.
ACTUAL_DATE - дата изменения записи
CLOSE_DATE - дата закрытия или окончания действия записи, если = 0, то срок не ограничен (пока не будет закрыта).

Если с первой задачей еще получилось справиться вот таким образом:

Код:

SELECT * FROM test WHERE REC_ID = <ID записи> AND ACTUAL_DATE = 
(SELECT MAX(ACTUAL_DATE) FROM test WHERE REC_ID = <ID записи> AND ACTUAL_DATE <= <дата актуальности>);

то вторую решить пока не удалось очень жду ваших советов

[Алексей++]

прочитал несколько раз, не понял оба задания

скажи словами, что требуется достичь ?

[RuNTiME]

Алексей1153++,  суть в чем, есть к примеру справочник сотрудников. Кто-то приходит, кто-то увольняется.
В таблице которую я привел находиться история двух записей с REC_ID = 1 и REC_ID = 11.
1. Если я знаю что идентификатор сотрудника REC_ID = 1, а дата ACTUAL_DATE = 7, то получаю запись:

Код:

+----+--------+-------------+------------+
| ID | REC_ID | ACTUAL_DATE | CLOSE_DATE |
+----+--------+-------------+------------+
|  7 |      1 |           7 |         10 |
+----+--------+-------------+------------+

т.е. если говорить "человеческим" языком я знаю ID сотрудника и дату, то могу узнать работал он у меня в эту дату или нет.

2.Необходимо получить список всех сотрудников, работавших на какую либо дату.
К примеру хочу узнать какие записи были актуальны на число ACTUAL_DATE = 10.
На выходе должно быть следующее, если учитывать что сотрудник с REC_ID=1 еще работал в дату 10
(был его последний день т.к. CLOSE_DATE=10):

Код:

+----+--------+-------------+------------+
| ID | REC_ID | ACTUAL_DATE | CLOSE_DATE |
+----+--------+-------------+------------+
| 10 |      1 |          10 |         10 | 
| 11 |     11 |          10 |          0 |
+----+--------+-------------+------------+

Все даты предполагаю хранить в TimeStamp формате, по этому и привожу в примерах числа.

[Алексей++]

2)

Код:

select distinct ID, REC_ID, ACTUAL_DATE, CLOSE_DATE from TEST where ACTUAL_DATE<=10 OR CLOSE_DATE=10

так?

[RuNTiME]

Алексей1153++, что - то не то
Вот ответ на твой запрос:

Код:

+----+--------+-------------+------------+
| ID | REC_ID | ACTUAL_DATE | CLOSE_DATE |
+----+--------+-------------+------------+
|  1 |      1 |           1 |         10 | 
|  2 |      1 |           2 |         10 | 
|  3 |      1 |           3 |         10 | 
|  4 |      1 |           4 |         10 | 
|  5 |      1 |           5 |         10 | 
|  6 |      1 |           6 |         10 | 
|  7 |      1 |           7 |         10 | 
|  8 |      1 |           8 |         10 | 
|  9 |      1 |           9 |         10 | 
| 10 |      1 |          10 |         10 | 
| 11 |     11 |          10 |          0 | 
+----+--------+-------------+------------+

[Алексей++]

а, ну дык тогда :
...... ACTUAL_DATE=10 .....

[RuNTiME]

Алексей1153++, выдаёт тот же самый набор, что и прошлый запрос
у тебя условие ACTUAL_DATE и CLOSE_DATE соединены через или....

[Алексей++]

Алексей1153++, выдаёт тот же самый набор, что и прошлый запрос

если  ACTUAL_DATE РАВНО 10  , то никак не получится, что в выборке будут ACTUAL_DATE со значенем меньше 10.

а с  CLOSE_DATE  наверное надо убрать ? Я по заданию не совсем понял всё таки

[RuNTiME]

Алексей1153++,
Запрос:
select distinct ID, REC_ID, ACTUAL_DATE, CLOSE_DATE from test where ACTUAL_DATE=10 OR CLOSE_DATE=10;
Ответ:

Код:

+----+--------+-------------+------------+
| ID | REC_ID | ACTUAL_DATE | CLOSE_DATE |
+----+--------+-------------+------------+
|  1 |      1 |           1 |         10 | 
|  2 |      1 |           2 |         10 | 
|  3 |      1 |           3 |         10 | 
|  4 |      1 |           4 |         10 | 
|  5 |      1 |           5 |         10 | 
|  6 |      1 |           6 |         10 | 
|  7 |      1 |           7 |         10 | 
|  8 |      1 |           8 |         10 | 
|  9 |      1 |           9 |         10 | 
| 10 |      1 |          10 |         10 | 
| 11 |     11 |          10 |          0 | 
+----+--------+-------------+------------+

[RuNTiME]

Алексей1153++, Насчет CLOSE_DATE это дата деактивации записи. т.е. дата после которой запись считается не актуальной. Возможно, что запрос удастся составить и без неё, сам пока не знаю. Но в программе это поле будет необходимо.

[Алексей++]

тогда так  ( с AND CLOSE_DATE<=10;)

select distinct ID, REC_ID, ACTUAL_DATE, CLOSE_DATE from test where ACTUAL_DATE=10 AND CLOSE_DATE<=10;

[RuNTiME]

Алексей1153++, да, это почти правильно
В дату 10 выдает правильный результат:

Код:

+----+--------+-------------+------------+
| ID | REC_ID | ACTUAL_DATE | CLOSE_DATE |
+----+--------+-------------+------------+
| 10 |      1 |          10 |         10 | 
| 11 |     11 |          10 |          0 | 
+----+--------+-------------+------------+

Но если указать к примеру дату 11, то результат должен быть таким же как и для 10 т.к. не используем CLOSE_DATE. Твой запрос в дату 11 выдаёт пустой результат.

[RuNTiME]

Алексей1153++, я почти уверен, что надо использовать MAX(ACTUAL_DATE), но не получается её по - нормальному прикрутить...

[Алексей++]

тогда мысль такая:

NNN=11

select distinct ID, REC_ID, ACTUAL_DATE, CLOSE_DATE from test where REC_ID = ... AND ACTUAL_DATE=
(select max(ACTUAL_DATE) from test where ACTUAL_DATE<=:NNN )



кстати, непонятно, почему у тебя CLOSE_DATE=0 тоже в результате, или 0 означает, что поле не заполнено ?

тогда
select distinct ID, REC_ID, ACTUAL_DATE, CLOSE_DATE from test where REC_ID = ... AND (ACTUAL_DATE<=CLOSE_DATE OR CLOSE_DATE=0) AND ACTUAL_DATE=
(select max(ACTUAL_DATE) from test where ACTUAL_DATE<=:NNN )

[RuNTiME]

Алексей1153++, насчет поля CLOSE_DATE я писал выше... если оно = 0, значит на тот момент было не известно, когда будет закрыта запись. Твои запросы ща попробую

[Алексей++]

невнимательное я ((

Кстати, надо ещё предусмотреть случай, когда подзапрос вернёт NULL

[RuNTiME]

Алексей1153++, сенкс, вроде получилось, буду еще тестить..... только из твоего запроса надо кое - что убрать:
REC_ID = ...
вот запрос который дал верный результат на любой дате:

Код:

select distinct ID, REC_ID, ACTUAL_DATE, CLOSE_DATE from test where 
(ACTUAL_DATE<=CLOSE_DATE OR CLOSE_DATE=0) AND ACTUAL_DATE= 
(select max(ACTUAL_DATE) from test where ACTUAL_DATE<=10 );

[RuNTiME]

Алексей1153++, кстати distinct в запросе тоже лишний.... select distinct ID дает тот же результат, что и просто select ID, т.к. поле ID это первичный ключ и никогда не повторяется еще раз сенкс за запрос, пол дня сегодня голову ломал над этой заразой

[Алексей++]

RuNTiME, ну я же не знаю, может ты решишь без ID вынимать данные Ну а так да, не нужно

[RuNTiME]

Алексей1153++, кстати пытаюсь оптимизировать твой запрос... условие (ACTUAL_DATE<=CLOSE_DATE OR CLOSE_DATE=0) тоже оказалось лишним... ACTUAL_DATE никогда не будет больше CLOSE_DATE т.к. запись не может изменяться после даты закрытия.... а раз отпадает условие ACTUAL_DATE<=CLOSE_DATE, то можно выкинуть и вторую часть CLOSE_DATE=0. И все продолжает работать отлично

В итоге осталось:

Код:

select * from test where ACTUAL_DATE=(select max(ACTUAL_DATE) 
from test where ACTUAL_DATE<=10);

А так можно еще и закрытые записи отфильтровать:

Код:

select * from test where (CLOSE_DATE>=<дата> or CLOSE_DATE=0) AND
ACTUAL_DATE=(select max(ACTUAL_DATE) from test where ACTUAL_DATE<=<дата> );

[Алексей++]

RuNTiME, учти, что бывают ошибки заполнения базы, даже если там будут триггеры вставки (всякое бывает), так что от греха подальше не убирай эти условия.