Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Вставить дни  [new]
Lexx_SQL
Member

Откуда:
Сообщений: 566
Добрый день!
Задача по учету рабочего времени.
Есть табличка: login, Data , Time. Данные заносятся за те дни , в которые сотрудники работали. А ведь они могут прогулять, взять отгул. Как сделать , чтобы эти дни (login,data,0)тоже отображались в отчете:
1) занести "пустые" дни в табличку и потом просто их выбирать select'ом
2) как нибудь извернуться с запросом в отчете (Select...)
Посоветуйте, пожалуйста....
17 май 11, 16:03    [10665710]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
Glory
Member

Откуда:
Сообщений: 104751
Lexx_SQL
Посоветуйте, пожалуйста....

Календарь на один год занимает 365 записей, на 10 лет ~ 3650 записей, на 100 лет ~36500
А содержит все дни, которые можно как-то фильтровать
17 май 11, 16:07    [10665760]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Lexx_SQL,

имхо вопрос скорее архитектурный чем по сиквел-серверу.
мое мнение, что правильнее просто отображать недостающие пустые дни в отчете, ничего не храня при этом в бд по каждому сотруднику.
а то по такой логике надо учитывать не просто прогулы, а вдруг добавится новый сотрудник, или уволится старый, а данных за два года, что ж теперь все два года для нового сотрудника "пустыми" датами забивать. А если вдруг формат отчета изменится, зачем буду эти пляски с пустыми датами.
Лучше имхо, в селекте, получить необходимые даты по нужному промежутку (например "построить" воспользовавших таблицей целых чисел и функцией dateadd, или предварительно создать таблицу дней +-100 лет), и к ним уже цеплять реальные данные.
17 май 11, 16:15    [10665856]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
Lexx_SQL
Member

Откуда:
Сообщений: 566
SomewhereSomehow,

..да так наверно будет правильней....спасибо.
17 май 11, 16:21    [10665913]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
Гришков Максим
Member

Откуда: СПб
Сообщений: 324
Lexx_SQL
2) как нибудь извернуться с запросом в отчете (Select...)

Аналогичную задачу (только по показаниям счетчиков эл.энергии) решал вот так:
/*@p_date_begin - начальная дата, @p_date_end - конечная дата*/
with q_dates(rep_date) 
   as (select @p_date_begin as rep_date
        where @p_date_begin <= @p_date_end
        union all
        select dateadd(day, 1, rep_date) as rep_date
          from q_dates 
        where rep_date < @p_date_end)

select rep_date as date, 
         /*функции/подзапросы, позволяющие получить требуемые значения для отчета по ДАТЕ*/                                                            
  from q_dates
where rep_date < @p_date_end) q
                         option(MAXRECURSION /*макс количество дней*/)

Хочу заметить, что если данных ОООЧЕНЬ много, то производительность по понятным причинам будет не очень.
17 май 11, 16:21    [10665919]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
Lexx_SQL
1) занести "пустые" дни в табличку и потом просто их выбирать select'ом

Хранить пустые дни не правильно.

Т.е. либо таблица чисел + DATEADD, либо календарь (актуально для праздников, работ в выходные и прочего). Либо таблица чисел+календарь))). В зависимости от задач. Но это должны быть все равно отдельные сущности в единичном экземпляре на весь проект.
17 май 11, 16:24    [10665941]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
squid
Member

Откуда: LA
Сообщений: 590
Мы этот вопрос на собеседовании задаем.
Задача типа, есть таблица только с фактическими посещениями работы. Перечня дат нет.

Решается ес-но CTE, left join. Простая задача, но позволяет сразу оценить уровень.
18 май 11, 11:57    [10670201]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
squid
ес-но CTE, left join
Не согласен.
18 май 11, 11:59    [10670230]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
iap
squid
ес-но CTE, left join
Не согласен.

Ну глядя кому какой уровень надобно ;), вот вас бы не взяли. Меня походу тоже))). Или я бы такого работодателя не взял ;).
18 май 11, 12:19    [10670494]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
Snirk
Member

Откуда:
Сообщений: 6
DECLARE @Start int = 1, @Stop int = 10

;WITH rangeList (field) AS
(
  SELECT @Start
  
  UNION ALL
  
  SELECT field+1 FROM rangeList WHERE field < @Stop
)
SELECT * FROM rangeList

Как-то вот так можно через CTE организовать список всех дат (в примере для поля int, но для даты думаю переделать проблемы нет). А дальше используем rangeList в LeftJoin. Склонен согласиться, что не самая сложная задача, но CTE (в т. ч. рекурсивные) надо знать
23 сен 11, 17:52    [11328740]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
iljy
Member

Откуда:
Сообщений: 8711
Snirk,

это едва ли не самый медленный способ. Если уж делать через CTE, то вот такая конструкция
;with l0(x) as (select 0 union all select 0),
l1(x) as (select 0 from l0 l,l0),
l2(x) as (select 0 from l1 l,l1),
l3(x) as (select 0 from l2 l,l2),
l4(x) as (select 0 from l3 l,l3),
l (x) as (select ROW_NUMBER() over(order by l.x) N from l4 l,l4)
select top(@N) * from l
выигрывает примерно в 20 раз. Быстрее всего же использовать специальную таблицу чисел.
23 сен 11, 18:08    [11328917]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
iljy
Быстрее всего же использовать специальную таблицу чисел.


Да и она не нужна, по хорошему. По "разведданным" :) master..spt_values еще доооолго будет "в деле". Хоть и не документировано, да.
24 сен 11, 18:19    [11331947]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
iljy
Member

Откуда:
Сообщений: 8711
SamMan
iljy
Быстрее всего же использовать специальную таблицу чисел.


Да и она не нужна, по хорошему. По "разведданным" :) master..spt_values еще доооолго будет "в деле". Хоть и не документировано, да.

Будет, только тут 2 мааааленеьких НО. Во-первых - она небольшая, 2048 чисел всего, а во-вторых - давать простому пользователю доступ к базе master - плохая идея.
24 сен 11, 21:14    [11332284]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
При наличии достаточно большой таблицы можно просто пользоваться ROW_NUMBER().
В этом случае и в spt_values намного больше подходящих записей, чем 2048.
Но в своей таблице и индексы замутить можно,
а в таблице-календаре хранить очень нужную дополнительную информацию.
24 сен 11, 21:45    [11332327]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
iljy
Member

Откуда:
Сообщений: 8711
iap,

ненамного, в spt_values их всего 2508. Можно использовать умножение таблиц, но все равно получается медленнее, чем из отдельной таблицы. Так что я предпочитаю сделать свою, благо места много не надо, ну и можно из нее в интервалах выбирать без дополнительных заморочек. Отдельную таблицу дат надо делать по необходимости.
24 сен 11, 22:01    [11332347]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 35376
Блог
Я делаю свою, заполняю из
spt_values cross join spt_values

Имхо конечно, но практически в любой нормально-спроектированной системе календарь должен быть.
24 сен 11, 22:23    [11332396]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
Верблюд
Member

Откуда: Яженичеловек!!!
Сообщений: 65007
Критик
Я делаю свою, заполняю из
spt_values cross join spt_values

Имхо конечно, но практически в любой нормально-спроектированной системе календарь должен быть.


ога. такая таблица со всеми днями на сто лет вперед...
24 сен 11, 23:38    [11332528]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
iljy
Во-первых - она небольшая, 2048 чисел всего


Это да, не поспоришь...

iljy
давать простому пользователю доступ к базе master - плохая идея.


Ну это-то решаемо: CREATE PROCEDURE ... with EXECUTE AS...+GRANT EXECUTION. Контролируемый доступ и все такое.

iljy
ненамного, в spt_values их всего 2508.


Так если на роу_намбер переключиться тогда вообще надо sys.messages брать, взамен spt_values. Сто тыщ записей и открыта для всех входящих в роль public, что тоже отчасти решает вопрос поставленный выше.
25 сен 11, 18:03    [11333261]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
iljy
Member

Откуда:
Сообщений: 8711
SamMan
iljy
Во-первых - она небольшая, 2048 чисел всего


Это да, не поспоришь...

iljy
давать простому пользователю доступ к базе master - плохая идея.


Ну это-то решаемо: CREATE PROCEDURE ... with EXECUTE AS...+GRANT EXECUTION. Контролируемый доступ и все такое.

iljy
ненамного, в spt_values их всего 2508.


Так если на роу_намбер переключиться тогда вообще надо sys.messages брать, взамен spt_values. Сто тыщ записей и открыта для всех входящих в роль public, что тоже отчасти решает вопрос поставленный выше.

sys.messages - это вьюха, сложная и широкая, с табличными функциями, поэтому быстродействие там получается ой-ой. Проще уж sys.objects cross join sys.objects поставить. Но во-первых - все равно проиграем, а во-вторых - надо будет получить не с 1 до 10000 числа, а с N до M, и начнуться навороты, не сложные, но муторные. А для собственной таблицы банальный between. У меня на тестовом сервере всегда есть инлайн-функция на основе spt_values cross join spt_values и ROW_NUMBER(), а в боевых базах я создаю табличку на пару лимонов записей и не мучаю ни себя, ни сервер
25 сен 11, 19:10    [11333317]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
alexejs
Member

Откуда:
Сообщений: 147
http://blogs.technet.com/b/isv_team/archive/2011/08/18/3447784.aspx
26 сен 11, 11:09    [11335068]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
alexejs
http://blogs.technet.com/b/isv_team/archive/2011/08/18/3447784.aspx


Использовать в хвост и гриву LEAD и не предупредить что он только в Denali... это фэйл однозначно!
26 сен 11, 12:37    [11335942]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
alexejs
Member

Откуда:
Сообщений: 147
Злопыхателям сюда.
26 сен 11, 12:55    [11336126]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
А что на статистику и как следствие estimates взятые с потолка всем уже пофигу? Например, тот же вариант с CTE, всегда 100 строк? Какие будут планы при джойне этой псевдо-таблицы с другими таблицами? Так не далеко и до массивных sort in tempdb из-за нехватки пямяти на ровном месте.
27 сен 11, 00:21    [11338818]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Mind
А что на статистику и как следствие estimates взятые с потолка всем уже пофигу? Например, тот же вариант с CTE, всегда 100 строк? Какие будут планы при джойне этой псевдо-таблицы с другими таблицами? Так не далеко и до массивных sort in tempdb из-за нехватки пямяти на ровном месте.
Да понятно, что правильный ответ на вопрос был дан во втором посте.

СТЕ и подобное применять просто абсурдно. Уж не говоря о прочем полезном функционале, реализуемым через таблицу-календарь (типа праздников и прочих атрибутов)
27 сен 11, 09:43    [11339364]     Ответить | Цитировать Сообщить модератору
 Re: Вставить дни  [new]
alexejs
Member

Откуда:
Сообщений: 147
Как предполагается заполнять календарь?
Чем персистить в отдельно стоящий календарь лучше, чем в исходную таблицу (или таблицу рядом), если это одноразовая операция?
27 сен 11, 10:48    [11339782]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить