Myujth

I want a query that checks if a employee has more than 14 vacation days in a year. I want to make a trigger of it. It is important that this is 14 days in total. But the employee could have 4 days in one month and 10 days in another. He don't need to take the days in one go.

I have something like this (query) I'm using SQL Server

select employeeid
from time
where datediff(day, dateStart, dateEnd) >=1
and year(dateEnd) = 2018
and timecat= 'vacationdays'
group by employeeid
having count(*) >=13

I thought I could use a datediff and have this count like 13 times. But it doesn't work.

You need to sum the date differences:

select employeeid, sum(datediff(day, dateStart, dateEnd) + 1) AS total
from time
where year(dateEnd) = 2018
and timecat= 'vacationdays'
group by employeeid
having SUM(datediff(day, dateStart, dateEnd) + 1) > 14

Consider the +1 in SUM because datediff returns 1 for two dates like '2018-11-16' and '2018-11-17', but if these are the dateStart and dateEnd you want the result to be 2.

There is still one problem remaining: what happens if dateStart and dateEnd are not dates of the same year!

Count(*) just counts the number of rows.
This means if have an employee with two vacations one of 10 days and one of 4 days, (i assume that) this are only two rows in rows in your table and count star returns 2.

You can you the function SUM()

select employeeid, sum( datediff(day, dateStart, dateEnd) ) as sum
from time
where datediff(day, dateStart, dateEnd) >=1
and year(dateEnd) = 2018
and timecat= 'vacationdays'
group by employeeid
having sum >=13

I don't know the structure of your table, therefore I'm not quite sure if the query is correct, but here a further helpful links.

https://www.w3schools.com/sql/sql_count_avg_sum.asp

Getting the sum of a datediff result