There is a table Stag ( PersonNumber, DataStart, DataFinish, Dolgnost, MestoRaboty ).

For example: there are such lines in this table.

 1, 2001-01-01, 2010-01-01, Менеджер, Банк 1, 2010-02-10, null, Менеджер, Отель

I do the procedure so that it displays a list of people whose experience, let's say more than 10 years ...

null-i mean that a person works here and is not fired.

Based on these two lines, the experience in the amount of> 10! How to make so that all lines with the same number of the employee would be looked through and summed up?

I use DATEDIFF and CURRENT_TIMESTAMP , but I don’t understand how ...

    2 answers 2

    For MySQL, the query looks like this:

     SELECT PersonNumber, SUM( -- Сумматор DATEDIFF( -- Разница между конечной и начальной датой IFNULL(DataFinish, CURDATE()), -- если дата = NULL, то берем текущую DataStart ) ) / 365 AS count_years FROM stag GROUP BY PersonNumber -- Группируем по работникам HAVING count_years > 10 -- Ограничиваем выборку теми чей стаж > 10 лет
    • Thanks!) everything seems to be clear here, but it still does not work ((('CURDATE' is not a recognized built-in function name. - wicS
    • CURDATE() is a function in MySQL that returns the current date. If you use another DBMS, look for a similar one there. - KiTE
    • thanks, earned! - wicS

    based on this code

     SELECT Manager FROM Stag HAVING SUM(DATEDIFF(year, ISNULL(DataFinish, GETDATE(), DataStart)>10 GROUP BY Manager
    • I did not understand (((you can explain ... everything is underlined in my line after SUM ... year is the difference in years, isnull is if the cell is empty? and then I don’t understand ... getdate is something like the current date ? - wicS
    • one
      GROUP BY Manager probably after FROM should be ...? - wicS
    • Yes of course. if the tag is specified sql, then I think it is about MS SQL and write in T-SQL dialect - renegator
    • column names and a complete list of fields by which to group up yourself. you don't have primary keys specified - renegator
    • thanks, did everything! - wicS