-- obtener paciente con el numero de documento
SELECT pa.IdPaciente as id
    ,pa.ApellidoPaterno as apellidopaterno
    ,pa.ApellidoMaterno as apellidomaterno
    ,pa.PrimerNombre as primernombre
    ,pa.SegundoNombre as segundonombre
    ,pa.TercerNombre as tercernombre
    ,pa.FechaNacimiento as fecha
    ,pa.NroDocumento as documento
    ,pa.Telefono as celular
    ,pa.DireccionDomicilio as direcion
    ,pa.NroHistoriaClinica as historiaclinica
  FROM sigh.dbo.Pacientes as pa
  where pa.NroDocumento = '14781773201'
-- obtener paciente con el numero de historia
  SELECT pa.IdPaciente as id
    ,pa.ApellidoPaterno as apellidopaterno
    ,pa.ApellidoMaterno as apellidomaterno
    ,pa.PrimerNombre as primernombre
    ,pa.SegundoNombre as segundonombre
    ,pa.TercerNombre as tercernombre
    ,pa.FechaNacimiento as fecha
    ,pa.NroDocumento as documento
    ,pa.Telefono as celular
    ,pa.DireccionDomicilio as direcion
    ,pa.NroHistoriaClinica as historiaclinica
  FROM sigh.dbo.Pacientes as pa
  where pa.NroHistoriaClinica = '329678'
-- obtener citas por estado y paciente
SELECT ci.IdCita as id
    ,ci.Fecha as fecha
    ,ci.HoraInicio + ' - ' + ci.HoraFin as hora
    ,se.Nombre as consultorio
    ,pa.ApellidoPaterno as paciente_apellidopaterno
    ,pa.ApellidoMaterno as paciente_apellidomaterno
    ,pa.PrimerNombre as paciente_primernombre
    ,pa.SegundoNombre as paciente_segundonombre
    ,pa.TercerNombre as paciente_tercernombre
    ,pa.NroDocumento as documento
    ,pa.NroHistoriaClinica as nro_historiaclinica
    ,pa.Telefono as paciente_celular
    ,em.ApellidoPaterno as doctor_apellidopaterno
    ,em.ApellidoMaterno as doctor_apellidomaterno 
    ,em.Nombres  as doctor_nombres
    ,es.Nombre as especialidad
FROM sigh.dbo.Citas as ci
inner join sigh.dbo.Pacientes as pa ON ci.IdPaciente = pa.IdPaciente
inner join sigh.dbo.Servicios AS se ON ci.IdServicio = se.IdServicio
inner join sigh.dbo.Especialidades as es ON ci.IdEspecialidad = es.IdEspecialidad
inner join sigh.dbo.Medicos AS me ON ci.IdMedico = me.IdMedico
inner join sigh.dbo.Empleados as em ON me.IdEmpleado = em.IdEmpleado
where IdEstadoCita =  1 and ci.IdPaciente = 345346
order by fecha desc
-- obtener citas por id
SELECT ci.IdCita as id
    ,ci.Fecha as fecha
    ,ci.HoraInicio + ' - ' + ci.HoraFin as hora
    ,se.Nombre as consultorio
    ,pa.ApellidoPaterno as paciente_apellidopaterno
    ,pa.ApellidoMaterno as paciente_apellidomaterno
    ,pa.PrimerNombre as paciente_primernombre
    ,pa.SegundoNombre as paciente_segundonombre
    ,pa.TercerNombre as paciente_tercernombre
    ,pa.NroDocumento as documento
    ,pa.NroHistoriaClinica as nro_historiaclinica
    ,pa.Telefono as paciente_celular
    ,em.ApellidoPaterno as doctor_apellidopaterno
    ,em.ApellidoMaterno as doctor_apellidomaterno
    ,em.Nombres  as doctor_nombres
    ,es.Nombre as especialidad
FROM sigh.dbo.Citas as ci
inner join sigh.dbo.Pacientes as pa ON ci.IdPaciente = pa.IdPaciente
inner join sigh.dbo.Servicios AS se ON ci.IdServicio = se.IdServicio
inner join sigh.dbo.Especialidades as es ON ci.IdEspecialidad = es.IdEspecialidad
inner join sigh.dbo.Medicos AS me ON ci.IdMedico = me.IdMedico
inner join sigh.dbo.Empleados as em ON me.IdEmpleado = em.IdEmpleado
where ci.IdCita = 82149
