Created
December 13, 2017 14:27
-
-
Save Kavignon/7e67971e036bd92969a173be8ce057a3 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using( var dbCtx = new DataContext()) | |
{ | |
var employeeTrainingResults = dbCtx.ExecuteQuery<EmployeeTrainingResult>( | |
@"@"with SomeTemporaryEmployeeTable(EmployeeID, SkillID, IsQualified) | |
as | |
( | |
select e.ID, jSkill.ID, case when jsQualifications.QualificationID is null then 0 else 1 end | |
from Employee e | |
join Job job on job.EmployeeID = e.ID | |
and job.ID = (select max(ID) from Job | |
where EmployeeID = job.EmployeeID) | |
join JobGroup jobGroup on jobGroup.ID = job.JobGroupID | |
join JobSkill jSkill on jSkill.JobGroupID = job.JobGroupID | |
left join JobSkillQualification jsQualifications on jsQualifications.SkillID = cSkill.SkillID and jsQualifications.QualificationID = 0 | |
) | |
select distinct CONVERT(DATETIME,ISNULL(DocHistory.EffectDate, DATEADD(month, 6,activeTraining.[Date]))) , employee.EmployeeID | |
from SomeTemporaryEmployeeTable employee | |
inner join EmployeeFormation employeeFormation on employeeFormation.EmployeeID = employee.EmployeeID | |
and employeeFormation.Passed is not null | |
inner join Training activeTraining on activeTraining.QualificationID = 0 | |
and employeeFormation.FormationID = employeeFormation.FormationID | |
and activeTraining.Approved is not null and activeTraining.Approved = 1 | |
or employeeFormation.Passed is not null and employeeFormation.Passed = 1 | |
inner join JobQualification jQualifications on jQualifications.ID = activeTraining.QualificationID | |
left join JobDocuments jDocuments on jDocuments.EmployeeID = employee.EmployeeID and jDocuments.QualificationID = jQualifications.ID | |
left join JobDocumentHistories jdHistories on jdHistories.DocumentID = jDocuments.ID and EffectDate = | |
(select max(EffectDate) from JobDocumentHistories JDHistories | |
where JDHistories.DocumentID = (select max(ID) from JobDocuments)) | |
and activeTraining.Date < JDHistories.UploadTime and JDHistories.EffectDate < DATEADD(month, 6,activeTraining.Date) | |
where employee.IsQualified = 1").Where(x => x.TrainingCompletion != null) ; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment