I need a query to k...
Clear all

I need a query to know that a date column is not between the period that has two columns of dates (one is the start of assignment to a project and another the end of assignment of the project)

Posts: 1
Topic starter
Joined: 3 months ago

Hi there!,


Here is my problem, I have two tables:

1. a table in which I show the employee id, the project id and each day the employee assigns his 8 hours to one project, which when finished and assigned to another enters his timesheets to another project.


2. the other table is based on the start of assignments and the end of assignments from each project to each employee


The question is: I need to know the timesheets (which come from table 1) that are badly involved, that is, that the employee has not done well, and do not match, that is, they are outside the range from the start date of assignment to project and the end date of assignment to project


I tried this query, but it does not work:

select t.Id_Empleado, t.NombreEmpleado, t.IdProyecto, t.DescProyecto, MIN(t.FechaImp), MAX(t.FechaImp),
a.f_InicioAsigna, a.f_FinAsigna
from o_inf_tiempo_Vw t
join o_lst_asignaciones_Vw a
on t.IdProyecto=a.s_IdProyecto and t.Id_Empleado=a.s_IdPersonal
join o_inf_proyectos_Vw p
on t.IdProyecto=p.s_IdProyecto
where (t.FechaImp not between a.f_InicioAsigna and a.f_FinAsigna) and t.sCurrBase = 'EUR'
and p.s_Estado='Open'
group by t.Id_Empleado ,t.IdProyecto
order by t.Id_Empleado asc , t.FechaImp desc



where, fechaimp is the timesheets

and f_inicioasigna is the start of assignments and f_finasigna is the end of assignments