Search Query

QUERY.sql

--Stored Procedure: [dbo].[sp_Search]	  
    
    Select 
		pro.id as 'ProfileID',
		pro.first_name as 'FirstName',
		pro.last_name as 'LastName',
		pro.email as 'Email',
		pro.phone as 'Phone',
		sp.name as 'Sport',
		pro.score as 'Score',
		pos.name as 'Position',
		pro.user_id as 'UserID',
		pro.birthday as 'Birthday',
		pro.gender as 'Gender',
		pro.bio as 'Bio',
		pro.skill_id as 'SkillID',
		pro.picUrl as 'picUrl',
		pro.State as 'State',
		pro.City as 'City',
		pro.lat as 'Latitude',
		pro.lng as 'Longitude'
	from dbo.profiles pro 
	inner join dbo.profile_positions pp
	on pp.profile_id = pro.id
	inner join dbo.positions pos
	on pos.id = pp.position_id
	inner join dbo.sports sp
	on sp.id = pos.sport_id
	left join dbo.invitations iv
	on pro.id = iv.profile_id
	left join dbo.confirmations conf
	on iv.id = conf.invitation_id
	left join dbo.searches search
	on search.id = iv.search_id
	where pro.sport_id = @SportID and pos.id = @PositionID
	and pro.gender = @gender
	and @startDate not between search.start_date and search.end_date
	and @endDate not between search.start_date and search.end_date
	and CAST(@startTime as time(7)) not between search.start_time and search.end_time 
	and CAST(@endTime as time(7)) not between search.start_time and search.end_time 
	)
  
  /* Este query deberia regresar los players 
     que estan disponibles para las fechas y 
     horas definidas siempre y cuando no haya confirmado para esas fechas.

    Resultado actual: No encuentra ningun player  */