如何根据sql server中的设备登录更新员工的出勤率 [英] how to update the attendance of an employee based on device logins in sql server

问题描述

hi


i有两个表dbo.tempDeviceLogs和dbo.Emp_setting



dbo.tempDeviceLogs列是

hi
i am having two tables dbo.tempDeviceLogs and dbo.Emp_setting

dbo.tempDeviceLogs columns are

column name         data type        allow nulls 

LogsID                int                Unchecked
Device_Person_id      int                Unchecked
Device_id             int                Unchecked
logDateTime           datetime           Unchecked              
logVerifyMode         nchar(10)          Unchecked
workCodeID            int                Unchecked
Machin_install_id     int                unchecked 
data_loaded_dt        datetime           checked
Inout                 int                checked

dbo.Emp_setting

dbo.Emp_setting

column name     data type   allow nulls 

Empset_id	int	Unchecked
personal_id	int	Unchecked
DesignationID	int	Checked
DivisionID	int	Checked
Emp_status	char(1)	Unchecked
Emp_TypeId	int	Checked
Dept_Id		int	Checked
Group_Id	int	Checked
NDIVGRP_CODE	bigint	Checked

i希望员工身份存在或缺席基于设备登录



i尝试了这个

i want employee status to be present or absent based on device logins

i tried this one

UPDATE dbo.Emp_setting
SET Emp_status = CASE WHEN
	EXISTS(
		SELECT *
		FROM dbo.tempDeviceLogs
		WHERE
			tempDeviceLogs.logDateTime = Emp_setting.Emp_status
			AND logDateTime >= CAST(GETDATE() AS datetime)
		) THEN 'P'
	ELSE 'A' END;

但由于而导致错误tempDeviceLogs.logDateTime = Emp_setting.Emp_status



i是sql server的新手请帮我搞定结果

but this is causing error because of "tempDeviceLogs.logDateTime = Emp_setting.Emp_status"

i am new to sql server please help me to get results

推荐答案

您是正确的，它将显示/更新所有记录的ABSENT，因为您的日期时间包含上个月的数据。减去那么多。从以上查询中的GETDATE（）开始，您将得到所需的输出。

You are correct it will show / update ABSENT for all records as your date time contains last months data. Minus that much no. of days from GETDATE() in above query, you will get the desired output.

您好，



试试这个



这里我假设Device_Person_id和personal_id列具有相同的值。

Hi,

Try This

here i am assuming that Device_Person_id and personal_id columns are having same values.

UPDATE dbo.Emp_setting
SET Emp_status = CASE WHEN (SELECT COUNT(*) FROM  dbo.tempDeviceLogs WHERE CONVERT(VARCHAR,tempDeviceLogs.logDateTime,101) = CONVERT(VARCHAR,GETDATE(),101)
AND Device_Person_id = personal_id) > 0 THEN 'P' ELSE 'A' END

希望这会对你有帮助。

Hope this will help you.

检查这个，

Check This,

UPDATE dbo.Emp_setting
SET Emp_status = CASE WHEN (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

这篇关于如何根据sql server中的设备登录更新员工的出勤率的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！