Существует ли основанное на наборе решение для этой проблемы?
У нас есть настольный приемник следующим образом:
|ID|EmployeeID|Date |Category |Hours|
|1 |1 |1/1/2010 |Vacation Earned|2.0 |
|2 |2 |2/12/2010|Vacation Earned|3.0 |
|3 |1 |2/4/2010 |Vacation Used |1.0 |
|4 |2 |5/18/2010|Vacation Earned|2.0 |
|5 |2 |7/23/2010|Vacation Used |4.0 |
Бизнес-правила:
- Баланс отпуска вычисляется отпуском, заработанным минус используемый отпуск.
- Vacation использовал, всегда применяется против самого старого отпуска заработанная сумма сначала.
Мы должны возвратить строки для Отпуска, Заработанного, которые не были смещены используемым отпуском. Если используемый отпуск только сместил часть отпуска заработанная запись, мы должны возвратить ту запись, показывающую различие. Например, с помощью приведенной выше таблицы, набор результатов был бы похож:
|ID|EmployeeID|Date |Category |Hours|
|1 |1 |1/1/2010 |Vacation Earned|1.0 |
|4 |2 |5/18/2010|Vacation Earned|1.0 |
Обратите внимание, что рекордные 2 были устранены, потому что это было полностью смещено используемым временем, но записывает 1, и 4 только частично использовались, таким образом, они были вычислены и возвратились как таковой.
Единственным путем мы думали, чтобы сделать, это должно получить весь отпуск заработанные записи во временной таблице. Затем используйте общий отпуск и цикл через временную таблицу, удаляя самую старую запись и вычитая то значение из общего отпуска, используемого, пока общий используемый отпуск не будет нулем. Мы могли очистить его для того, когда остающийся используемый отпуск является только частью самого старого отпуска заработанная запись. Это оставило бы нас только с выдающимся отпуском заработанными записями.
Это работает, но это очень неэффективно и работает плохо. Кроме того, производительность будет просто ухудшаться со временем, поскольку добавляется все больше записей.
Есть ли какие-либо предложения для лучшего решения, предпочтительный базирующийся набор? В противном случае мы должны будем просто пойти с этим.
Править: Это - база данных поставщика. Мы не можем изменить структуру таблицы всегда.
5 ответов
Следующее должно сделать это...
(но, как отмечают другие, лучшим решением будет корректировка оставшихся отпусков по мере их использования...)
select
id, employeeid, date, category,
case
when earned_so_far + hours - total_spent > hours then
hours
else
earned_so_far + hours - total_spent
end as hours
from
(
select
id, employeeid, date, category, hours,
(
select
isnull(sum(hours),0)
from
vacations
WHERE
category = 'Vacation Earned'
and
date < v.date
and
employeeid = v.employeeid
) as earned_so_far,
(
select
isnull(sum(hours),0)
from
vacations
where
category = 'Vacation Used'
and
employeeid = v.employeeid
) as total_spent
from
vacations V
where category = 'Vacation Earned'
) earned
where
earned_so_far + hours > total_spent
Логика такова
- вычислите для каждой строки
заработанныхчасов, заработанных на данный момент - вычислите общее количество часов, использованных для этого пользователя
- выберите запись, если общее количество_часов_на данный момент + часы этой записи - общее количество_израсходованных_часов > 0
Я считаю, что весь ваш набор результатов сбивает с толку и неточен, и я вижу, как сотрудники говорят: «Нет, 25 января я заработал 2 часа, а не 1». Неверно, что в этот день они заработали 1 час, который был частично компенсирован, и у вас не будет конца проблем, если вы выберете такой способ отображения.Я бы посмотрел на другой способ представления информации. Обычно вы либо представляете список всех действий по отпуску (заработанных, истекших и использованных) с общим итогом внизу, либо представляете сводку доступных для использования и использованных.
За более чем 30 лет работы в штате и за то, что я работал с множеством различных систем хронометража (а также изучил еще больше, когда был аналитиком по менеджменту), я никогда не видел, чтобы кто-то хотел отображать хронометраж таким образом. Думаю, на то есть причина. Если это требование, я бы посоветовал отказаться от него и объяснить, как будет сложно читать эти данные, а также сложно получить хорошо работающее решение. Я бы не принял это как требование, не пытаясь убедить клиента, что это плохая идея.
По мере того, как время идет и записи добавляются, производительность будет становиться все хуже и хуже, если вы не сделаете что-то с этим, например:
- Удалите старые строки, когда они «аннулированы» (например, для заработанных отпусков использовался эквивалентный отпуск. добавлены и учтены строки; использованный отпуск был использован установить «истекший» отпуск, заработанный как «израсходованный»)
- Добавьте столбец, который отмечает, что строка была «отменена», и включите этот столбец в свои индексы
Отслеживание того, как данные изменяются таким образом, кажется аргументом для изменения структуры ваших таблиц (их несколько, а не только одна), но это выходит за рамки вашей текущей проблемы.
Что касается самого запроса, я бы построил два агрегата, сделал бы некоторое вычитание, сделал бы это подзапросом, а затем присоединил бы к нему, умело используя одну из функций ранжирования. Там тоже пахнет коррелированным подзапросом. Я могу попытаться решить это позже (у меня мало времени), но держу пари, что кто-то меня опередит.
Размышляя над проблемой, я подумал, что единственная причина, по которой вам нужно заботиться о , когда отпуск заработан, - это его истечение. И в этом случае самое простое решение - добавить в таблицу записи «Срок отпуска истек», чтобы количество оставшегося отпуска для сотрудника всегда равнялось сумме (заработанный отпуск) - (сумма (срок отпуска истек) + сумма (использованный отпуск)) . Вы даже можете показать точные записи, которые хотите, используя последнюю запись с истекшим сроком отпуска в качестве отправной точки для запроса.
Но я предполагаю, что это не вариант. Чтобы решить проблему, как указано, имейте в виду, что всякий раз, когда вы обнаруживаете, что используете временную таблицу, попробуйте вместо этого поместить эти данные в CTE (общее табличное выражение). К сожалению, у меня сейчас встреча, и поэтому у меня нет времени писать запрос (возможно, позже, это звучит весело), но это должно помочь вам начать.
Я бы посоветовал изменить таблицу, чтобы отслеживать Баланс в отдельном столбце. Таким образом, вам нужно будет получить только самую последнюю запись, чтобы узнать, где находится сотрудник.
Таким образом, вы можете удовлетворить простой случай («Сколько у меня отпуска?»), Но при этом иметь возможность сделать неудобную сводку, которую вы ищете в своей статье «Какие отрезки времени отпуска не совпадают. вверх с другими битами », который, я надеюсь, вам не понадобится очень часто.