-------------------------------2023/1/1 3:00:47-------------------------------
更新分析报告异常:数据库“JeezDBlh”的事务日志已满，原因为“LOG_BACKUP”。 _________________
select OrganizationID, Sum(case when HireDate>='2022-12-01' and HireDate<'2023-01-01' then 1 else 0 end) as Joins,
Sum(case when dimissiondate>='2022-12-01' and dimissiondate<'2023-01-01' then 1 else 0 end) as Resigns,count(ID)
as Staffs into #t85a5f394432049dd8d7df6c224c8dc32 from  (select  t1.ID, isnull(t2.Number,t1.Number) as Number, isnull(t2.Name,t1.Name) as Name, isnull(t2.Forbidden,t1.Forbidden) as Forbidden, isnull(t2.SexID,t1.SexID) as SexID, isnull(t2.Birthday,t1.Birthday) as Birthday, isnull(t2.DegreeID,t1.DegreeID) as DegreeID, isnull(t2.MarryStateID,t1.MarryStateID) as MarryStateID, isnull(t2.Phone,t1.Phone) as Phone, isnull(t2.Phone2,t1.Phone2) as Phone2, isnull(t2.Mobile,t1.Mobile) as Mobile, isnull(t2.PersonID,t1.PersonID) as PersonID, isnull(t2.Address,t1.Address) as Address, isnull(t2.Email,t1.Email) as Email, isnull(t2.Description,t1.Description) as Description, isnull(t2.BankAccountName,t1.BankAccountName) as BankAccountName, isnull(t2.BankAccount,t1.BankAccount) as BankAccount, isnull(t2.BankID,t1.BankID) as BankID, isnull(t2.BankAddress,t1.BankAddress) as BankAddress, isnull(t2.MSN,t1.MSN) as MSN, isnull(t2.QQ,t1.QQ) as QQ, isnull(t2.Name_Png,t1.Name_Png) as Name_Png, isnull(t2.SocietyBxNumber,t1.SocietyBxNumber) as SocietyBxNumber, isnull(t2.HuKouAddRess,t1.HuKouAddRess) as HuKouAddRess, isnull(t2.NativePlace,t1.NativePlace) as NativePlace, isnull(t2.DutyID,t1.DutyID) as DutyID, isnull(t2.EmployeeLevelID,t1.EmployeeLevelID) as EmployeeLevelID, isnull(t2.EmployeeTypeID,t1.EmployeeTypeID) as EmployeeTypeID, isnull(t2.HireDate,t1.HireDate) as HireDate, isnull(t2.FetalEmployeeDate,t1.FetalEmployeeDate) as FetalEmployeeDate, isnull(t2.DimissionDate,t1.DimissionDate) as DimissionDate, isnull(t2.WorkNumber,t1.WorkNumber) as WorkNumber, isnull(t2.CompanyEmployeeLevelID,t1.CompanyEmployeeLevelID) as CompanyEmployeeLevelID, isnull(t2.OrganizationID,t1.OrganizationID) as OrganizationID, isnull(t2.DepartMentID,t1.DepartMentID) as DepartMentID, isnull(t2.timestamp,t1.timestamp) as timestamp, isnull(t2.SysUserID,t1.SysUserID) as SysUserID, isnull(t2.EmployeeID,t1.EmployeeID) as EmployeeID, isnull(t2.EnglishName,t1.EnglishName) as EnglishName, isnull(t2.IsOtherNative,t1.IsOtherNative) as IsOtherNative, isnull(t2.JoinPartyDate,t1.JoinPartyDate) as JoinPartyDate, isnull(t2.OtherContactMode,t1.OtherContactMode) as OtherContactMode, isnull(t2.WorkDate,t1.WorkDate) as WorkDate, isnull(t2.Graduate,t1.Graduate) as Graduate, isnull(t2.College,t1.College) as College, isnull(t2.ComputerLevel,t1.ComputerLevel) as ComputerLevel, isnull(t2.StrongSuit,t1.StrongSuit) as StrongSuit, isnull(t2.SalaryStartDate,t1.SalaryStartDate) as SalaryStartDate, isnull(t2.NationID,t1.NationID) as NationID, isnull(t2.PolityID,t1.PolityID) as PolityID, isnull(t2.HealthStatusID,t1.HealthStatusID) as HealthStatusID, isnull(t2.EmpolyeeDegreeID,t1.EmpolyeeDegreeID) as EmpolyeeDegreeID, isnull(t2.SpecialtyID,t1.SpecialtyID) as SpecialtyID, isnull(t2.ForeignLanguageID1,t1.ForeignLanguageID1) as ForeignLanguageID1, isnull(t2.ForeignLevelID1,t1.ForeignLevelID1) as ForeignLevelID1, isnull(t2.ForeignLanguageID2,t1.ForeignLanguageID2) as ForeignLanguageID2, isnull(t2.ForeignLevelID2,t1.ForeignLevelID2) as ForeignLevelID2, isnull(t2.ForeignLanguageID3,t1.ForeignLanguageID3) as ForeignLanguageID3, isnull(t2.AccessoriesID2,t1.AccessoriesID2) as AccessoriesID2, isnull(t2.ForeignLevelID3,t1.ForeignLevelID3) as ForeignLevelID3, isnull(t2.AccessoriesID,t1.AccessoriesID) as AccessoriesID, isnull(t2.EmpStatusID,t1.EmpStatusID) as EmpStatusID, isnull(t2.PostTypeID,t1.PostTypeID) as PostTypeID, isnull(t2.BirthPlace,t1.BirthPlace) as BirthPlace, isnull(t2.AddressPostCode,t1.AddressPostCode) as AddressPostCode, isnull(t2.WorkPlace,t1.WorkPlace) as WorkPlace, isnull(t2.PensionPlace,t1.PensionPlace) as PensionPlace, isnull(t2.ResidenceNum,t1.ResidenceNum) as ResidenceNum, isnull(t2.ResidenceBeginDate,t1.ResidenceBeginDate) as ResidenceBeginDate, isnull(t2.ArchivesPlace,t1.ArchivesPlace) as ArchivesPlace, isnull(t2.ArchivesMO,t1.ArchivesMO) as ArchivesMO, isnull(t2.OneChildCertificate,t1.OneChildCertificate) as OneChildCertificate, isnull(t2.CertificateBirthDate,t1.CertificateBirthDate) as CertificateBirthDate, isnull(t2.InOCDate,t1.InOCDate) as InOCDate, isnull(t2.EmergencyCP,t1.EmergencyCP) as EmergencyCP, isnull(t2.EmergencyCPPhone,t1.EmergencyCPPhone) as EmergencyCPPhone, isnull(t2.GuarantorName,t1.GuarantorName) as GuarantorName, isnull(t2.GuarantorPhone,t1.GuarantorPhone) as GuarantorPhone, isnull(t2.GuarantorCompany,t1.GuarantorCompany) as GuarantorCompany, isnull(t2.GuarantorAddress,t1.GuarantorAddress) as GuarantorAddress, isnull(t2.RatingAgencies,t1.RatingAgencies) as RatingAgencies, isnull(t2.EmpLevelDate,t1.EmpLevelDate) as EmpLevelDate, isnull(t2.VocationalQualifications,t1.VocationalQualifications) as VocationalQualifications, isnull(t2.VORatingAgencies,t1.VORatingAgencies) as VORatingAgencies, isnull(t2.PostDate,t1.PostDate) as PostDate, isnull(t2.CostSubjects,t1.CostSubjects) as CostSubjects, isnull(t2.WorkDepartMent,t1.WorkDepartMent) as WorkDepartMent, isnull(t2.VORatingAgenciesDate,t1.VORatingAgenciesDate) as VORatingAgenciesDate, isnull(t2.Gangwei,t1.Gangwei) as Gangwei, isnull(t2.Specialty,t1.Specialty) as Specialty, isnull(t2.JobTitleName,t1.JobTitleName) as JobTitleName, isnull(t2.SabbaticDate,t1.SabbaticDate) as SabbaticDate, isnull(t2.PersonalIdentityID,t1.PersonalIdentityID) as PersonalIdentityID, isnull(t2.BodyHeight,t1.BodyHeight) as BodyHeight, isnull(t2.IsHasTWZ,t1.IsHasTWZ) as IsHasTWZ, isnull(t2.JinengDegreeID,t1.JinengDegreeID) as JinengDegreeID, isnull(t2.SalaryLevel,t1.SalaryLevel) as SalaryLevel, isnull(t2.WorkTime,t1.WorkTime) as WorkTime, isnull(t2.IsJoinUnion,t1.IsJoinUnion) as IsJoinUnion, isnull(t2.hujiTypeID,t1.hujiTypeID) as hujiTypeID, isnull(t2.JobTitleName1,t1.JobTitleName1) as JobTitleName1, isnull(t2.RatingAgencies1,t1.RatingAgencies1) as RatingAgencies1, isnull(t2.EmpLevelDate1,t1.EmpLevelDate1) as EmpLevelDate1, isnull(t2.JobTitleName2,t1.JobTitleName2) as JobTitleName2, isnull(t2.RatingAgencies2,t1.RatingAgencies2) as RatingAgencies2, isnull(t2.EmpLevelDate2,t1.EmpLevelDate2) as EmpLevelDate2, isnull(t2.VocationalQualifications1,t1.VocationalQualifications1) as VocationalQualifications1, isnull(t2.VORatingAgencies1,t1.VORatingAgencies1) as VORatingAgencies1, isnull(t2.VORatingAgenciesDate1,t1.VORatingAgenciesDate1) as VORatingAgenciesDate1, isnull(t2.VocationalQualifications2,t1.VocationalQualifications2) as VocationalQualifications2, isnull(t2.VORatingAgencies2,t1.VORatingAgencies2) as VORatingAgencies2, isnull(t2.VORatingAgenciesDate2,t1.VORatingAgenciesDate2) as VORatingAgenciesDate2, isnull(t2.ElseCertificate,t1.ElseCertificate) as ElseCertificate, isnull(t2.ElseCertificate1,t1.ElseCertificate1) as ElseCertificate1, isnull(t2.ElseCertificate2,t1.ElseCertificate2) as ElseCertificate2, isnull(t2.ArchivesNo,t1.ArchivesNo) as ArchivesNo, isnull(t2.ArchivesWo,t1.ArchivesWo) as ArchivesWo, isnull(t2.EmpolyeeTypeID,t1.EmpolyeeTypeID) as EmpolyeeTypeID, isnull(t2.HousingFundNumber,t1.HousingFundNumber) as HousingFundNumber, isnull(t2.StopSocialInsuranceDate,t1.StopSocialInsuranceDate) as StopSocialInsuranceDate, isnull(t2.StopGjj,t1.StopGjj) as StopGjj, isnull(t2.LeavingReasonID,t1.LeavingReasonID) as LeavingReasonID, isnull(t2.BeforeName,t1.BeforeName) as BeforeName, isnull(t2.RetireDate,t1.RetireDate) as RetireDate, isnull(t2.Weight,t1.Weight) as Weight, isnull(t2.TitleSeqID,t1.TitleSeqID) as TitleSeqID, isnull(t2.TitleSeqID1,t1.TitleSeqID1) as TitleSeqID1, isnull(t2.TitleSeqID2,t1.TitleSeqID2) as TitleSeqID2, isnull(t2.EmployeeLevelID1,t1.EmployeeLevelID1) as EmployeeLevelID1, isnull(t2.EmployeeLevelID2,t1.EmployeeLevelID2) as EmployeeLevelID2, isnull(t2.IsFullTime,t1.IsFullTime) as IsFullTime, isnull(t2.PersonnelTypeID,t1.PersonnelTypeID) as PersonnelTypeID, isnull(t2.CustomerCountryID,t1.CustomerCountryID) as CustomerCountryID, isnull(t2.DoorEKey,t1.DoorEKey) as DoorEKey, isnull(t2.DoorNumber,t1.DoorNumber) as DoorNumber, isnull(t2.DoorInnerNumber,t1.DoorInnerNumber) as DoorInnerNumber, isnull(t2.DoorOpenPwd,t1.DoorOpenPwd) as DoorOpenPwd, isnull(t2.EnterDate,t1.EnterDate) as EnterDate, isnull(t2.WorkPositionID,t1.WorkPositionID) as WorkPositionID, isnull(t2.ExtendCode,t1.ExtendCode) as ExtendCode, isnull(t2.FaithID,t1.FaithID) as FaithID, isnull(t2.RecruitWayID,t1.RecruitWayID) as RecruitWayID, isnull(t2.PartyPerson,t1.PartyPerson) as PartyPerson, isnull(t2.PartyOrganization,t1.PartyOrganization) as PartyOrganization, isnull(t2.IsConvertSoldier,t1.IsConvertSoldier) as IsConvertSoldier, isnull(t2.ConvertDate,t1.ConvertDate) as ConvertDate, isnull(t2.MilitaryRankID,t1.MilitaryRankID) as MilitaryRankID, isnull(t2.IsHandleWork,t1.IsHandleWork) as IsHandleWork, isnull(t2.IsHandleWorkLicence,t1.IsHandleWorkLicence) as IsHandleWorkLicence, isnull(t2.IsHandleWorkSign,t1.IsHandleWorkSign) as IsHandleWorkSign, isnull(t2.ResidenceID,t1.ResidenceID) as ResidenceID, isnull(t2.Director,t1.Director) as Director, isnull(t2.Recommender,t1.Recommender) as Recommender, isnull(t2.IsDestitute,t1.IsDestitute) as IsDestitute, isnull(t2.IsSupportJiang,t1.IsSupportJiang) as IsSupportJiang, isnull(t2.IsSupportZang,t1.IsSupportZang) as IsSupportZang, isnull(t2.PersonCertifiTypeID,t1.PersonCertifiTypeID) as PersonCertifiTypeID, isnull(t2.ArchivesEffectDate,t1.ArchivesEffectDate) as ArchivesEffectDate, isnull(t2.ArchivesInDate,t1.ArchivesInDate) as ArchivesInDate, isnull(t2.ArchivesOutDate,t1.ArchivesOutDate) as ArchivesOutDate, isnull(t2.EntryTypeID,t1.EntryTypeID) as EntryTypeID, isnull(t2.FromCompanyID,t1.FromCompanyID) as FromCompanyID, isnull(t2.EnterOldCompanyDate,t1.EnterOldCompanyDate) as EnterOldCompanyDate, isnull(t2.SocialBuyDate,t1.SocialBuyDate) as SocialBuyDate, isnull(t2.HouseFundBuyDate,t1.HouseFundBuyDate) as HouseFundBuyDate, isnull(t2.DutyDate,t1.DutyDate) as DutyDate from jzemployee as t1 left join (select t3.* from jzEmployee_History as t3
inner join (select t1.EmployeeID2,Min(t1.BDDate) as BDDate from jzEmployee_History as t1 inner join  jzEmployee as t2 on t1.EmployeeID2=t2.ID where t1.BDDate>='2023-01-01' group by t1.EmployeeID2) as t4 on t3.EmployeeID2=t4.EmployeeID2 and t3.BDDate=t4.BDDate) 
as t2 on t1.ID=t2.EmployeeID2  where  (isnull(t2.IsInsert,0)=0 and t1.ID in (select EmployeeID2 from jzEmployee_History where IsInsert=1 and BDDate<='2023-01-01')) or (t1.HireDate>'1900-01-01' and t1.HireDate<'2023-01-01')) as t where forbidden=0 group by OrganizationID
update t1 set Joins=t2.Joins,Resigns=t1.Resigns,Staffs=t1.Staffs from jzHREffectAnalysis as t1 inner join #t85a5f394432049dd8d7df6c224c8dc32
as t2  on t1.OrganizationID=t2.OrganizationID and t1.Year=2022 and t1.Month=12
insert into jzHREffectAnalysis(OrganizationID,Year,Month,Joins,Resigns,Staffs,Income,ParkIncome,MultiIncome,PayCost,OutScourcing,PurchaseCost,Expense,PerValue,PerCost) 
                       select t1.OrganizationID,2022 as Year,12 as Month,t1.Joins,t1.Resigns,t1.Staffs,0 as Income,0 as ParkIncome,0 as MultiIncome,0 as PayCost,0 as OutScourcing
,0 as PurchaseCost,0 as Expense,0 as PerValue,0 as PerCost 
from #t85a5f394432049dd8d7df6c224c8dc32
as t1 left join jzHREffectAnalysis as t2 on t1.OrganizationID=t2.OrganizationID and t2.Year=2022 and t2.Month=12 where isnull(t2.ID,0)=0
drop table #t85a5f394432049dd8d7df6c224c8dc32