select a.personId,b.Identifier NumeroMatricula from Person a
inner join PersonIdentifier b on a.personId=b.personId and b.RefPersonIdentificationSystemId=55

#listado de estudiantes por colegio

select * from PersonIdentifier where refpersonidentificationsystemid=55

select a.personId,b.Identifier from Person a
inner join PersonIdentifier b on a.personId=b.personId and b.RefPersonIdentificationSystemId=51

select a.personId,b.Identifier from Person a inner join PersonIdentifier b on a.personId=b.personId and b.RefPersonIdentificationSystemId=52

SELECT FirstName FROM Person join OrganizationPersonRole using(PersonId) where OrganizationPersonRole.RoleId = 6

SELECT MiddleName FROM Person join OrganizationPersonRole using(PersonId) where OrganizationPersonRole.RoleId = 6

SELECT LastName FROM Person join OrganizationPersonRole using(PersonId) where OrganizationPersonRole.RoleId = 6

SELECT RefSex.Description FROM Person join RefSex using(RefSexId)

Como en _Personas; Person; personId se encuentra el identificador del estudiante, asumiré que personId es un dato conocido. Por lo tanto para obtener el nivel del curso de un estudiante desde Organization.Name se debe aplicar el siguiente filtro:

OrganizationPersonRole;personId == [Person.personId] AND OrganizationPersonRole;RoleID == 6 (Estudiante) AND OrganizationPersonRole;OrganizationId == Organization.OrganizationId AND Organization.RefOrganizationTypeId == 40

#listado de niveles por establecimiento#
select distinct name NIvel from organization
where reforganizationtypeid=40

#grado por nivel por establecimiento#
select distinct name Grado from organization
where reforganizationtypeid=46

#cursos por nivel y grado, por establecimiento#
SELECT DISTINCT
codEnse.Name as codigoEnseñanza
, grado.Name as grado
, curso.Name as letraCurso
FROM Organization as curso
INNER JOIN OrganizationRelationship as rsCurso on curso.OrganizationId=rsCurso.OrganizationId

INNER JOIN Organization as grado
ON grado.OrganizationId=rsCurso.Parent_OrganizationId AND grado.RefOrganizationTypeId = 46
INNER JOIN OrganizationRelationship as rsGrado on grado.OrganizationId=rsGrado.OrganizationId

INNER JOIN Organization as codEnse
ON codEnse.OrganizationId=rsGrado.Parent_OrganizationId AND codEnse.RefOrganizationTypeId = 45
INNER JOIN OrganizationRelationship as rsCodEnse on codEnse.OrganizationId=rsCodEnse.OrganizationId

INNER JOIN Organization as tipoCurso
ON tipoCurso.OrganizationId=rsCodEnse.Parent_OrganizationId AND tipoCurso.RefOrganizationTypeId = 44
INNER JOIN OrganizationRelationship as rsTipoCurso on tipoCurso.OrganizationId=rsTipoCurso.OrganizationId

INNER JOIN Organization as especialidad
ON especialidad.OrganizationId=rsTipoCurso.Parent_OrganizationId AND especialidad.RefOrganizationTypeId = 43
INNER JOIN OrganizationRelationship as rsEspecialidad on especialidad.OrganizationId=rsEspecialidad.OrganizationId

INNER JOIN Organization as sector
ON sector.OrganizationId=rsEspecialidad.Parent_OrganizationId AND sector.RefOrganizationTypeId = 42
INNER JOIN OrganizationRelationship as rsSector on sector.OrganizationId=rsSector.OrganizationId

INNER JOIN Organization as rama
ON rama.OrganizationId=rsSector.Parent_OrganizationId AND rama.RefOrganizationTypeId = 41
INNER JOIN OrganizationRelationship as rsRama on rama.OrganizationId=rsRama.OrganizationId

INNER JOIN Organization as nivel
ON nivel.OrganizationId=rsRama.Parent_OrganizationId AND nivel.RefOrganizationTypeId = 40
INNER JOIN OrganizationRelationship as rsNivel on nivel.OrganizationId=rsNivel.OrganizationId

Como en _Personas; Person; personId se encuentra el identificador del estudiante, asumiré que personId es un dato conocido. Por lo tanto para obtener el nivel del curso de un estudiante desde Organization.Name se debe aplicar el siguiente filtro:

OrganizationPersonRole;personId == [Person.personId] AND OrganizationPersonRole;RoleID == 6 (Estudiante) AND OrganizationPersonRole;OrganizationId == Organization.OrganizationId AND Organization.RefOrganizationTypeId == 21

#cursos por nivel y grado, por establecimiento#
SELECT DISTINCT
codEnse.Name as codigoEnseñanza
, grado.Name as grado
, curso.Name as letraCurso
FROM Organization as curso
INNER JOIN OrganizationRelationship as rsCurso on curso.OrganizationId=rsCurso.OrganizationId

INNER JOIN Organization as grado
ON grado.OrganizationId=rsCurso.Parent_OrganizationId AND grado.RefOrganizationTypeId = 46
INNER JOIN OrganizationRelationship as rsGrado on grado.OrganizationId=rsGrado.OrganizationId

INNER JOIN Organization as codEnse
ON codEnse.OrganizationId=rsGrado.Parent_OrganizationId AND codEnse.RefOrganizationTypeId = 45
INNER JOIN OrganizationRelationship as rsCodEnse on codEnse.OrganizationId=rsCodEnse.OrganizationId

INNER JOIN Organization as tipoCurso
ON tipoCurso.OrganizationId=rsCodEnse.Parent_OrganizationId AND tipoCurso.RefOrganizationTypeId = 44
INNER JOIN OrganizationRelationship as rsTipoCurso on tipoCurso.OrganizationId=rsTipoCurso.OrganizationId

INNER JOIN Organization as especialidad
ON especialidad.OrganizationId=rsTipoCurso.Parent_OrganizationId AND especialidad.RefOrganizationTypeId = 43
INNER JOIN OrganizationRelationship as rsEspecialidad on especialidad.OrganizationId=rsEspecialidad.OrganizationId

INNER JOIN Organization as sector



ON sector.OrganizationId=rsEspecialidad.Parent_OrganizationId AND sector.RefOrganizationTypeId = 42
INNER JOIN OrganizationRelationship as rsSector on sector.OrganizationId=rsSector.OrganizationId

INNER JOIN Organization as rama
ON rama.OrganizationId=rsSector.Parent_OrganizationId AND rama.RefOrganizationTypeId = 41
INNER JOIN OrganizationRelationship as rsRama on rama.OrganizationId=rsRama.OrganizationId

INNER JOIN Organization as nivel
ON nivel.OrganizationId=rsRama.Parent_OrganizationId AND nivel.RefOrganizationTypeId = 40
INNER JOIN OrganizationRelationship as rsNivel on nivel.OrganizationId=rsNivel.OrganizationId

#listado de datos del curso

SELECT DISTINCT
ee.RBD
, modalidad.Name as modalidad
, jornada.Name as jornada
, nivel.Name as nivel
, rama.Name as rama
, sector.Name as sector
, especialidad.Name as especialidad
, tipoCurso.Name as tipoCurso
, codEnse.Name as codigoEnseñanza
, grado.Name as grado
, curso.Name as letraCurso
, curso.OrganizationId as OrganizationIdDelCurso

FROM Organization as curso
INNER JOIN OrganizationRelationship as rsCurso on curso.OrganizationId=rsCurso.OrganizationId

INNER JOIN Organization as grado
ON grado.OrganizationId=rsCurso.Parent_OrganizationId AND grado.RefOrganizationTypeId = 46
INNER JOIN OrganizationRelationship as rsGrado on grado.OrganizationId=rsGrado.OrganizationId

INNER JOIN Organization as codEnse
ON codEnse.OrganizationId=rsGrado.Parent_OrganizationId AND codEnse.RefOrganizationTypeId = 45
INNER JOIN OrganizationRelationship as rsCodEnse on codEnse.OrganizationId=rsCodEnse.OrganizationId

INNER JOIN Organization as tipoCurso
ON tipoCurso.OrganizationId=rsCodEnse.Parent_OrganizationId AND tipoCurso.RefOrganizationTypeId = 44
INNER JOIN OrganizationRelationship as rsTipoCurso on tipoCurso.OrganizationId=rsTipoCurso.OrganizationId

INNER JOIN Organization as especialidad
ON especialidad.OrganizationId=rsTipoCurso.Parent_OrganizationId AND especialidad.RefOrganizationTypeId = 43
INNER JOIN OrganizationRelationship as rsEspecialidad on especialidad.OrganizationId=rsEspecialidad.OrganizationId

INNER JOIN Organization as sector
ON sector.OrganizationId=rsEspecialidad.Parent_OrganizationId AND sector.RefOrganizationTypeId = 42
INNER JOIN OrganizationRelationship as rsSector on sector.OrganizationId=rsSector.OrganizationId

INNER JOIN Organization as rama
ON rama.OrganizationId=rsSector.Parent_OrganizationId AND rama.RefOrganizationTypeId = 41
INNER JOIN OrganizationRelationship as rsRama on rama.OrganizationId=rsRama.OrganizationId

INNER JOIN Organization as nivel
ON nivel.OrganizationId=rsRama.Parent_OrganizationId AND nivel.RefOrganizationTypeId = 40
INNER JOIN OrganizationRelationship as rsNivel on nivel.OrganizationId=rsNivel.OrganizationId

INNER JOIN Organization as jornada
ON jornada.OrganizationId=rsNivel.Parent_OrganizationId AND jornada.RefOrganizationTypeId = 39
INNER JOIN OrganizationRelationship as rsJornada on jornada.OrganizationId=rsJornada.OrganizationId

INNER JOIN Organization as modalidad
ON modalidad.OrganizationId=rsJornada.Parent_OrganizationId AND modalidad.RefOrganizationTypeId = 38
INNER JOIN OrganizationRelationship as rsModalidad on modalidad.OrganizationId=rsModalidad.OrganizationId

INNER JOIN (
SELECT
i.Identifier as RBD
,Organization.Name as 'nombreEstablecimiento'
,i.OrganizationId as OrganizationId
FROM OrganizationIdentifier i
INNER JOIN Organization USING(OrganizationId)
INNER JOIN RefOrganizationIdentificationSystem rbd
ON i.RefOrganizationIdentificationSystemId = rbd.RefOrganizationIdentificationSystemId
AND i.RefOrganizationIdentificationSystemId = (
SELECT RefOrganizationIdentificationSystemId
FROM RefOrganizationIdentificationSystem
WHERE Code = 'RBD' )
INNER JOIN RefOrganizationIdentifierType Mineduc
ON i.RefOrganizationIdentifierTypeId = Mineduc.RefOrganizationIdentifierTypeId
AND i.RefOrganizationIdentifierTypeId = (
SELECT RefOrganizationIdentifierTypeId
FROM RefOrganizationIdentifierType
WHERE Code = 'Mineduc' )) as ee on ee.OrganizationId=rsModalidad.Parent_OrganizationId

WHERE curso.RefOrganizationTypeId = 21

select distinct StreetNumberAndName,city Ciudad,CountyName Comuna from LocationAddress

select persona.personId,
numeroMatricula.Identifier NumMatricula,
fechaMatricula.StatusStartDate FechaMatricula ,
persona.FirstName PrimerNombre,
persona.MiddleName SegundoNombre,
persona.LastName ApellidoPaterno,
persona.SecondLastName ApellidoMaterno,
fechaMatricula.recordEndDateTime FechaRetiro,
fechaMatricula.Description MotivoRetiro
from Person persona
inner join PersonIdentifier numeroMatricula on persona.personId=numeroMatricula.personId and numeroMatricula.RefPersonIdentificationSystemId=55
inner join personstatus fechaMatricula on persona.personid=fechamatricula.personid

select relacion.firstname,
relacion.MiddleName,
relacion.LastName,
relacion.SecondLastName,
direccion.StreetNumberAndName,
comuna.Description,
telefono.TelephoneNumber,
email.emailAddress,
NivelEducacionalMAE.Description EducacionNivel
from person a
inner join PersonRelationship b on a.personid=b.personid and b.refpersonrelationshipid=8
inner join refpersonrelationship c on b.refpersonrelationshipid=c.refpersonrelationshipid
inner join person relacion on b.relatedpersonid=relacion.personid
left join PersonAddress direccion on relacion.personid=direccion.personId
left join refcounty comuna on direccion.RefCountyId=comuna.RefCountyId
left join PersonTelephone telefono on relacion.personid=telefono.personid
left join PersonEmailAddress email on relacion.personid=email.personid
left join PersonDegreeOrCertificate NivelEducacional on relacion.personid=Niveleducacional.personid
left join RefDegreeOrCertificateType NivelEducacionalMAE on Niveleducacional.RefDegreeOrCertificateTypeid=NivelEducacionalMAE.RefDegreeOrCertificateTypeid

select relacion.firstname,
relacion.MiddleName,
relacion.LastName,
relacion.SecondLastName,
direccion.StreetNumberAndName,
comuna.Description,
telefono.TelephoneNumber,
email.emailAddress,
NivelEducacionalMAE.Description EducacionNivel
from person a
inner join PersonRelationship b on a.personid=b.personid and b.refpersonrelationshipid=19
inner join refpersonrelationship c on b.refpersonrelationshipid=c.refpersonrelationshipid
inner join person relacion on b.relatedpersonid=relacion.personid
left join PersonAddress direccion on relacion.personid=direccion.personId
left join refcounty comuna on direccion.RefCountyId=comuna.RefCountyId
left join PersonTelephone telefono on relacion.personid=telefono.personid
left join PersonEmailAddress email on relacion.personid=email.personid
left join PersonDegreeOrCertificate NivelEducacional on relacion.personid=Niveleducacional.personid
left join RefDegreeOrCertificateType NivelEducacionalMAE on Niveleducacional.RefDegreeOrCertificateTypeid=NivelEducacionalMAE.RefDegreeOrCertificateTypeid

select a.PersonId,c.Description from person a
inner join PersonRelationship b on a.personId=b.personId
inner join RefPersonRelationship c on b.RefPersonRelationshipId=c.RefPersonRelationshipId

select name Modalidad /*deben ser solo 3 regular, especial, adulto*/ from organization
where reforganizationtypeid=38

select CalendarYear from organizationcalendar limit 1 /*solo un año por archivo*/

select DISTINCT
curso.OrganizationId as OrganizationIdDelCurso
, profesorJefe.apellidoPaternoDocenteLiderCurso
, profesorJefe.apellidoMaternoDocenteLiderCurso
, profesorJefe.primerNombreDocenteLiderCurso
, profesorJefe.otrosNombresDocenteLiderCurso
, profesorJefe.runDocenteLiderCurso

FROM Organization as curso
INNER JOIN OrganizationRelationship as rsCurso on curso.OrganizationId=rsCurso.OrganizationId
OUTER LEFT JOIN (
SELECT
OrganizationPersonRoleId
, OrganizationId
, PersonId
, LastName as 'apellidoPaternoDocenteLiderCurso'
, SecondLastName as 'apellidoMaternoDocenteLiderCurso'
, FirstName as 'primerNombreDocenteLiderCurso'
, MiddleName as 'otrosNombresDocenteLiderCurso'
, runDocenteLiderCurso
FROM K12StaffAssignment
INNER JOIN OrganizationPersonRole USING(OrganizationPersonRoleId)
INNER JOIN (
SELECT DISTINCT
Person.PersonId
,Person.LastName
,Person.SecondLastName
,Person.FirstName
,Person.MiddleName
,rut.Identifier as RunDocenteLiderCurso
FROM Person
INNER JOIN PersonIdentifier rut ON rut.PersonId = Person.PersonId AND rut.RefPersonIdentificationSystemId = 51
) USING(PersonId)
WHERE RefTeachingAssignmentRoleId = 1
) profesorJefe ON OrganizationIdDelCurso = profesorJefe.OrganizationId

WHERE curso.RefOrganizationTypeId = 21

select relacion.firstname,
relacion.MiddleName,
relacion.LastName,
relacion.SecondLastName,
direccion.StreetNumberAndName,
comuna.Description,
telefono.TelephoneNumber,
email.emailAddress,
NivelEducacionalMAE.Description EducacionNivel
from person a
inner join PersonRelationship b on a.personid=b.personid and b.refpersonrelationshipid=31
inner join refpersonrelationship c on b.refpersonrelationshipid=c.refpersonrelationshipid
inner join person relacion on b.relatedpersonid=relacion.personid
left join PersonAddress direccion on relacion.personid=direccion.personId
left join refcounty comuna on direccion.RefCountyId=comuna.RefCountyId
left join PersonTelephone telefono on relacion.personid=telefono.personid
left join PersonEmailAddress email on relacion.personid=email.personid
left join PersonDegreeOrCertificate NivelEducacional on relacion.personid=Niveleducacional.personid
left join RefDegreeOrCertificateType NivelEducacionalMAE on Niveleducacional.RefDegreeOrCertificateTypeid=NivelEducacionalMAE.RefDegreeOrCertificateTypeid

--excedente sin pago subvencion
select b.personid,c.Description,b.docnumber numeroResolucion,b.filescanbase64 archivo,b.StatusStartDate fechadocumento from PersonStatus a
inner join person b on a.personId=b.PersonId and a.RefPersonStatusTypeId in (24)
inner join RefPersonStatusType c on a.RefPersonStatusTypeid=c.RefPersonStatusTypeId
where a.recordenddatetime is null


--excedente con pago subvencion
select b.personid,c.Description,b.docnumber numeroResolucion,b.filescanbase64 archivo,b.StatusStartDate fechadocumento from PersonStatus a
inner join person b on a.personId=b.PersonId and a.RefPersonStatusTypeId in (31)
inner join RefPersonStatusType c on a.RefPersonStatusTypeid=c.RefPersonStatusTypeId
where a.recordenddatetime is null



#Intercambio
select b.personid,c.Description,b.docnumber numeroResolucion,b.filescanbase64 archivo,b.StatusStartDate fechadocumento
from PersonStatus a
inner join person b on a.personId=b.PersonId and a.RefPersonStatusTypeId in (25)
inner join RefPersonStatusType c on a.RefPersonStatusTypeid=c.RefPersonStatusTypeId
where a.recordenddatetime is null

select b.personid,c.Description from PersonStatus a
inner join person b on a.personId=b.PersonId and a.RefPersonStatusTypeId in (26,5)
inner join RefPersonStatusType c on a.RefPersonStatusTypeid=c.RefPersonStatusTypeId

select empresa.name NombreEmpresa,
fonoempresa.TelephoneNumber telefonoempresa,
emailempresa.ElectronicMailAddress correoempresa,
direccionempresa.StreetNumberAndName dirempresa
from organization empresa
left join OrganizationTelephone fonoempresa on empresa.organizationid=fonoempresa.organizationid
left join OrganizationEmail emailempresa on empresa.organizationid=emailempresa.organizationid
left join OrganizationLocation orgloca on empresa.organizationid=orgloca.OrganizationId
left join LocationAddress direccionempresa on orgloca.locationid=direccionempresa.locationid
where empresa.reforganizationtypeid=26

select * from organization a
inner join OrganizationPersonRole rolesupervisor on a.organizationid=rolesupervisor.organizationid and rolesupervisor.roleid=17
left join RefOrganizationType ramopractica on a.reforganizationtypeid=ramopractica.reforganizationtypeid and ramopractica.RefOrganizationTypeId=47
left join person profesorsupervisor on rolesupervisor.personid=profesorsupervisor.personid
left join PersonTelephone telefonosupervisor on profesorsupervisor.personid=telefonosupervisor.personId
left join PersonEmailAddress emailsupervisor on profesorsupervisor.personid=emailsupervisor.personid

select relacion.firstname,
relacion.MiddleName,
relacion.LastName,
relacion.SecondLastName,
direccion.StreetNumberAndName,
comuna.Description,
telefono.TelephoneNumber,
email.emailAddress,
NivelEducacionalMAE.Description EducacionNivel
from person a
inner join PersonRelationship b on a.personid=b.personid and b.refpersonrelationshipid=31
inner join refpersonrelationship c on b.refpersonrelationshipid=c.refpersonrelationshipid
inner join person relacion on b.relatedpersonid=relacion.personid
left join PersonAddress direccion on relacion.personid=direccion.personId
left join refcounty comuna on direccion.RefCountyId=comuna.RefCountyId
left join PersonTelephone telefono on relacion.personid=telefono.personid
left join PersonEmailAddress email on relacion.personid=email.personid
left join PersonDegreeOrCertificate NivelEducacional on relacion.personid=Niveleducacional.personid
left join RefDegreeOrCertificateType NivelEducacionalMAE on Niveleducacional.RefDegreeOrCertificateTypeid=NivelEducacionalMAE.RefDegreeOrCertificateTypeid

select a.personid,b.Description ETNIA from person a
left join RefTribalAffiliation b on a.RefTribalAffiliationid=b.RefTribalAffiliationid