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

Откуда:
Сообщений: 2
Всем привет

Прочитал несколько тем на форуме, посвященные моей проблеме

вот например https://www.sql.ru/forum/553845/kak-vychislit-raznicu-v-datah

но ответа так пока и не нашел.

Если кто подскажет буду очень благодарен.

Есть кадровая программа, уже давно написанная. Возникла необходимость её немного дописать.

Возникла необходимость посчитать стаж работы сотрудника в компании.

Вроде все легко, я быстро написал дополнение и получил вроде нужный результат, но оказалось не все так просто.

Дело в том что некоторые сотрудники увольнялись и возвращались в компанию, и по ним у меня неправильный подсчет стажа.

Все события "принят на работу" "фактически уволен" и т.п. пишутся в таблицу tbl_PersonnelHistory:

PH_ID PERSONNEL_ID DATE ACTION
1654 381 2015-08-19 00:00:00.000 0
1761 381 2016-04-28 00:00:00.000 6
1928 381 2017-06-06 00:00:00.000 0

выше показал результат выборки с такого запроса:

SELECT *
FROM tbl_PersonnelHistory
WHERE PERSONNEL_ID=381 AND [ACTION] IN (0,6)

PERSONNEL_ID=381 - Сотрудник

[ACTION] 0 - принят на работу
6 - фактически уволен


нужен такой запрос чтобы посчитать стаж от события ACTION=0 до ACTION=6 от второго события ACTION=0 по текущую дату и при этом исключить время от события ACTION=6 до второго ACTION=0

все что я придумал это написать процедуру и вызывать её в запросе, а самой процедуре уже выполнять "сложные" вычисления, определять очередность событий по номеру PH_ID и определять какой из ACTION выше или ниже другого и потом это все считать

а вот как это все написать одним запросом никак не могу понять, если кто меня понял и готов подсказать большое вам заранее спасибо!
24 янв 18, 17:38    [21135405]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать стаж сотрудника за исключением диапазона  [new]
ГуЗы
Guest
Ackep,
Если совсем тупо:
row_number() over(partiton by id_personsl,action order by date)
и будет тебе от 1-0 до 1-6 ... n - 0 до n - 6
24 янв 18, 17:49    [21135459]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать стаж сотрудника за исключением диапазона  [new]
Добрый Э - Эх
Guest
Ackep,

вчера же только обсуждали???? Сколько можно-то?
причем, твоя задача даже ещё проще - у тебя уже даны точки смены состояний, а не диапазоны их действия. Стало быть, разворота диапазонов в точки делать не надо. только собрать правильные диапазоны по точкам, и всё...
24 янв 18, 18:27    [21135641]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать стаж сотрудника за исключением диапазона  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
А что если посчитать по зарплатным ведомостям ? :)
24 янв 18, 18:49    [21135705]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать стаж сотрудника за исключением диапазона  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Прям эпидемия пошла...
+ Безопасно для homo sapiens и felis silvestris catus
IF OBJECT_ID( 'tempdb..#temp' ) IS NOT NULL
  DROP TABLE #temp
;
CREATE TABLE
  #temp (
    [ID] INT,
    [TabID] INT,
    [Date] DATE,
    [Action] TINYINT )
;
INSERT INTO
  #temp
VALUES 
   ( 1654, 381,	'20150819',	0 ),	
   ( 1761, 381,	'20160428',	6 ), 	 
   ( 1928, 381,	'20170606',	0 )
;
WITH
t0 AS (
  SELECT
    ts.[TabID],
    [From] = ts.[Date],
    [Till] = ISNULL( MIN( te.[Date] ), CONVERT( DATE, GETDATE() ) )
  FROM
    #temp ts
    LEFT JOIN #temp te ON (
          te.[TabID] = ts.[TabId]
      AND te.[Date] > ts.[Date]
      AND te.[Action] = 6 )
  WHERE
    ts.[Action] = 0
  GROUP BY
    ts.[TabID],
    ts.[Date]
)
SELECT
  [TabID],
  [Seniority] = SUM( DATEDIFF( DAY, [From], [Till] ) )
FROM
  t0
GROUP BY
  [TabID]
;
25 янв 18, 11:28    [21137351]     Ответить | Цитировать Сообщить модератору
 Re: Посчитать стаж сотрудника за исключением диапазона  [new]
Ackep
Member

Откуда:
Сообщений: 2
Всем кто откликнулся большое спасибо.
Очень выручили!

Руслан, немного изменил ваш запрос т.к. вложенный WITH не проходит.

SELECT(SELECT CAST(SUM(DATEDIFF( MONTH, [From], [Till]))/12 AS varchar(3)) + ',' + CAST(SUM(DATEDIFF( MONTH, [From], [Till])) %12 AS varchar(3)) AS FLOAT
FROM 
(
SELECT
    ts.[TabID],
    [From] = ts.[Date],
    [Till] = ISNULL( MIN( te.[Date] ), CONVERT( DATE, GETDATE() ) )
  FROM
    tbl_PersonnelHistory ts
    LEFT JOIN tbl_PersonnelHistory te ON (
          te.[TabID] = ts.[TabID]
      AND te.[Date] > ts.[Date]
      AND te.[Action] IN (0,6))   --Добавил _0_ т.к. у некоторых сотрудников было два выхода на работу без фактического увольнения после первого выхода
  WHERE
    ts.[Action] IN (0) AND ts.[TabID] = p.[TabID]
  GROUP BY
    ts.[TabID],
    ts.[Date]
) t0
GROUP BY p.[TabID]) AS [Стаж работы] 
 
7 фев 18, 13:22    [21172955]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить