Skip to content

Instantly share code, notes, and snippets.

@ahrherrera
Created February 18, 2019 16:49
Show Gist options
  • Save ahrherrera/a55b8a5d72d4ec758701a385b9870497 to your computer and use it in GitHub Desktop.
Save ahrherrera/a55b8a5d72d4ec758701a385b9870497 to your computer and use it in GitHub Desktop.
Search Query
--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 */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment