查询员工出勤情况

本文介绍了查询员工出勤情况的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我需要使用表格显示员工出勤率dbo.tempDeviceLogs，dbo.Emp_setting，dbo.persons_profile dbo.tempDeviceLogs列i need to show employee attendance using tables dbo.tempDeviceLogs , dbo.Emp_setting, dbo.persons_profiledbo.tempDeviceLogs columns arecolumnname Data type allow nullsLogsID int UncheckedDevice_Person_id int UncheckedDevice_id int UncheckedlogDateTime datetimeUncheckedlogVerifyMode nchar(10)UncheckedworkCodeID int UncheckedMachin_install_id int Uncheckeddata_loaded_dtdatetimeCheckedInout int Checked dbo.Emp_settingdbo.Emp_settingcolumnname datatype allownullsEmpset_idint Uncheckedpersonal_idint UncheckedDesignationIDint CheckedDivisionIDint CheckedEmp_statuschar(1) UncheckedEmp_TypeIdint CheckedDept_IdintCheckedGroup_Idint CheckedNDIVGRP_CODEbigint Checked dbo.persons_profiledbo.persons_profilecolumnname datatype allownullspesonal_id int UncheckedEmp_Code nchar(15) CheckedTitle nchar(4) UncheckedFirst_name varchar(35) UncheckedMiddle_name varchar(35) Checkedlast_name varchar(35) CheckedFather_Husband_Namevarchar(35) UncheckedDob datetime UncheckedAge int Uncheckedgender nchar(1) UncheckedMarital_status nchar(1) UncheckedNationality nchar(10) UncheckedbloodGroup nchar(10) UncheckedperAddress nchar(100) UncheckedPerStreet nchar(100) CheckedPerLocation nchar(50) UncheckedPerCity nchar(20) CheckedPerPincodenchar(6)CheckedCorAddressnchar(50)CheckedCorStreetnchar(50)CheckedCorLocationnchar(50)CheckedCorCity nchar(20)CheckedCorPincodenchar(6)CheckedLandlinePhonenchar(24)CheckedcellNo nchar(24)CheckedEmailId nchar(35)CheckedNosofDependendantsintCheckedDependendants_detailsntextCheckedEmergency_FirstNamenchar(35)CheckedEmergency_Middle_namenchar(35)CheckedEmergency_Last_namenchar(35)CheckedEmergency_WithRelationnchar(25)CheckedEmergency_PhoneNonchar(22)CheckedEmergency_CellNonchar(22)CheckedEmergency_emailIdnchar(35)CheckedOffice_PF_ac_no nchar(20) CheckedESI_ac_no nchar(20)CheckedJoinedDate datetime CheckedPhotofile nchar(50) CheckedReportTo varchar(50) CheckedBrief_Notes nchar(150) CheckeddateofTerminationvarchar(10)Checkedtermination_notenchar(50)CheckedPrint_Priority int CheckedDeviceEmployeeIDnchar(25)CheckedLogsPermitted int UncheckedMachin_install_idint CheckedDesignation nchar(100) CheckedDept nchar(100) CheckedSection nchar(100) CheckedGroups nchar(100) CheckedEmpWorkingTypeTnchar(100) Checked 所需的输出是Required output isEmployeename(Fname) Device_person_id Designation emp_typeid date present/absent 如果我们选择今天的日期，那么它应该显示在上面今日详情if we select todays date then it should show above details for todaySELECT b.Device_Person_ID, a.Personal_id, Date1, CASE WHEN b.Device_Person_id IS NOT NULL THEN 'A' ELSE 'P' END as Emp_StatusFROM Emp_setting a LEFT OUTER JOIN (SELECT device_person_id, MAX(logDateTime) AS Date1 FROM tempDeviceLogs GROUP BY device_person_id) bON a.personal_id = b.device_person_id推荐答案试试这个，Try This,SELECT CASEWHEN (SELECT COUNT(*) FROM dbo.tempDeviceLogs WHERE CONVERT(VARCHAR,tempDeviceLogs.logDateTime,101) = CONVERT(VARCHAR,GETDATE() - 31 ,101)AND Device_Person_id = personal_id) > 0 THEN 'P' ELSE 'A' END FROM Emp_setting 希望这对你有帮助。Hope this will help you.查看这个Check this oneSelect Device_Person_id,personal_id,Date1,(cASE WHEN eXISTS(sELECT Device_Person_idFROM tempDeviceLogs AT WHERE T.personal_id=AT.Device_Person_id AND T.date1=AT.logDateTime) then 'P' Else 'A' End )as StatusFROM(Select b.Device_Person_id,a.personal_id,Cast(b.logDateTime as DATEtime)AS DATE1 from Emp_setting a,tempDeviceLogs b--(Select Distinct logDateTime from tempDeviceLogs_1) b) T 找到以下改进版本。 这里我假设连接中的列具有相同的值。find below improved version.Here I am assuming columns in join are having same values.Select Device_Person_id,personal_id,Date1,(cASE WHEN eXISTS(sELECT Device_Person_idFROM tempDeviceLogs AT WHERE T.personal_id=AT.Device_Person_id AND T.date1=AT.logDateTime) then 'P' Else 'A' End )as StatusFROM(Select b.Device_Person_id,a.personal_id,Cast(b.logDateTime as DATEtime)AS DATE1 from Emp_setting a left outer join tempDeviceLogs b on b.Device_Person_id = a.personal_id--(Select Distinct logDateTime from tempDeviceLogs_1) b) T 希望这会有所帮助你更多。Hope this will help you more. 这篇关于查询员工出勤情况的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！