declare  @CualPromocion int , @CualPrograma int

set  @CualPromocion = 2
set @CualPrograma = -1



SELECT  
       ProgramaIniciales = dbo.Programa.NombreCorto,
        programa = dbo.Programa.nombre,
        promocion  = dbo.Promocion.nombre, 
        Matricula = dbo.AlumnoProgramaPromocion.Matricula,
        NombreCompleto = dbo.Contacto.ApellidoPaterno +  ' ' + dbo.Contacto.ApellidoMaterno + ' ' + dbo.Contacto.Nombres ,
        RFC = dbo.Contacto.RFC, 
        EstadoCivil = dbo.Contacto.EstadoCivil,
        FechaNacimiento =  dbo.Contacto.FechaNacimiento, 
        edad = YEAR(GETDATE()) - YEAR(dbo.Contacto.FechaNacimiento) , 
         EstadoNacimiento    = 
                     ( select top 1 EstadoProvincia.Nombre 
                         from EstadoProvincia 
       	                where EstadoProvincia.EstadoProvinciaID
                              = 
                            ( select direccion.EstadoProvinciaID
                                from direccion
                               where direccion.DireccionID 
                                        = 
                                     ( select top 1 AlumnoDireccion.DireccionID
                                         from AlumnoDireccion
                                        where AlumnoDireccion.AlumnoID = Alumno.AlumnoID 
                                              and
                                              AlumnoDireccion.DireccionTipoID = 6  --nacimiento                                       
                                     )                               
                            )
                     ) ,
         PaisNacimiento    = 
                     ( select top 1 EstadoProvincia.PaisRegionCodigo
                         from EstadoProvincia 
       	                where EstadoProvincia.EstadoProvinciaID
                              = 
                            ( select direccion.EstadoProvinciaID
                                from direccion
                               where direccion.DireccionID 
                                        = 
                                     ( select top 1 AlumnoDireccion.DireccionID
                                         from AlumnoDireccion
                                        where AlumnoDireccion.AlumnoID = Alumno.AlumnoID 
                                              and
                                              AlumnoDireccion.DireccionTipoID = 6  --nacimiento                                       
                                     )                               
                            )
                     ) ,


         EstadoOrigen    = 
                    ( select top 1 EstadoProvincia.Nombre 
                         from EstadoProvincia 
       	                where EstadoProvincia.EstadoProvinciaID
                              = 
                            ( select direccion.EstadoProvinciaID
                                from direccion
                               where direccion.DireccionID 
                                        = 
                                     ( select top 1 AlumnoDireccion.DireccionID
                                         from AlumnoDireccion
                                        where AlumnoDireccion.AlumnoID = Alumno.AlumnoID 
                                              and
                                              AlumnoDireccion.DireccionTipoID = 7  --origen                                       
                                     )                               
                            )
                     ) ,
         PaisOrigen    = 
                     ( select top 1 EstadoProvincia.PaisRegionCodigo
                         from EstadoProvincia 
       	                where EstadoProvincia.EstadoProvinciaID
                              = 
                            ( select direccion.EstadoProvinciaID
                                from direccion
                               where direccion.DireccionID 
                                        = 
                                     ( select top 1 AlumnoDireccion.DireccionID
                                         from AlumnoDireccion
                                        where AlumnoDireccion.AlumnoID = Alumno.AlumnoID 
                                              and
                                              AlumnoDireccion.DireccionTipoID = 7  --origen                                       
                                     )                               
                            )
                     ) ,

        dbo.Contacto.Sexo ,
       FormacionAcademica = substring 
                           (
                             
                              ( select top 1 diciplina
                                  from FormacionAcademica
                                 where (FormacionAcademica.AlumnoId = Alumno.AlumnoID )
                                 order by FormacionAcademica.GradoAcademico desc
                               ) ,
                             1 , 20
                            ) , 
                    
        Institucion = ( select InstitucionAcademica.nombre
                          from InstitucionAcademica
                         where InstitucionAcademica.InstitucionAcademicaID
                                 = 
                               ( select top 1 FormacionAcademica.InstitucionAcademicaID
                               from FormacionAcademica
                              where (FormacionAcademica.AlumnoId = Alumno.AlumnoID )
                           order by FormacionAcademica.GradoAcademico desc
                             ) 
                       ) ,
            promedio = ( select top 1 promedio
                           from FormacionAcademica
                          where (FormacionAcademica.AlumnoId = Alumno.AlumnoID )
                       order by FormacionAcademica.GradoAcademico desc  -- para que primero me traiga la maestria y luego la licenciatura
                        ) ,
              NoBeca = ( select top 1 BecaNo                  from Beca where Beca.AlumnoID = Alumno.AlumnoID  and  Status = 'Actual') ,
     BecaVigenciaIni = ( select top 1 FechaVigenciaIni        from Beca where Beca.AlumnoID = Alumno.AlumnoID  and  Status = 'Actual' ) ,
     BecaVigenciaFin = ( select top 1 FechaVigenciaFin        from Beca where Beca.AlumnoID = Alumno.AlumnoID  and  Status = 'Actual' ) ,
 BecaRegistroConacyt = ( select top 1 RegistroConacyt         from Beca where Beca.AlumnoID = Alumno.AlumnoID  and  Status = 'Actual' ),
     BecaInstitucion = ( select nombre from InstitucionAcademica where InstitucionAcademica.InstitucionAcademicaID  
                          in
                         ( select top 1 Beca.InstitucionAcademicaID  from Beca where Beca.AlumnoID = Alumno.AlumnoID  and  Status = 'Actual' )
                        )

         
FROM    dbo.Programa 
        INNER JOIN  dbo.ProgramaPromocion      
                ON  dbo.Programa.ProgramaID = dbo.ProgramaPromocion.ProgramaID 
        INNER JOIN  dbo.Promocion
                ON  dbo.ProgramaPromocion.PromocionID = dbo.Promocion.PromocionID 
        INNER JOIN  dbo.AlumnoProgramaPromocion 
                ON  dbo.ProgramaPromocion.ProgramaPromocionID = dbo.AlumnoProgramaPromocion.ProgramaPromocionID 
        INNER JOIN  dbo.Alumno   
                ON  dbo.AlumnoProgramaPromocion.AlumnoID = dbo.Alumno.AlumnoID 
        INNER JOIN  dbo.Contacto    
                ON  dbo.Alumno.ContactoID = dbo.Contacto.ContactoID

WHERE   
        (dbo.Promocion.PromocionID = @CualPromocion)
             and
        ( (@CualPrograma = -1) or (dbo.Programa.ProgramaID = @CualPrograma)  )




select * from FormacionAcademica