how to update the attendance of an employee based on device logins in sql server

Question

0.00/5 (No votes)

See more:

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

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 want employee status to be present or absent based on device logins

i tried this one

SQL

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;

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

Posted 10-Jun-14 19:33pm

Danuuu

Updated 10-Jun-14 23:04pm

v4

Add a Solution

Comments

ArunRajendra 11-Jun-14 1:49am

Do you want to set the status for each day? Then you need to have a date field Emp_Setting table.

Danuuu 11-Jun-14 2:14am

in dbo.tempDeviceLogs datetime field is there "logDateTime"

if there is any entry for logdatetime then employee status should be "present" else "absent"

Danuuu 11-Jun-14 2:31am

logdatetime is datetime format only

3 solutions

Solution 3

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.

Posted 10-Jun-14 21:11pm

Magic Wonder

Comments

Danuuu 11-Jun-14 3:23am

how to minus? i dnt no
GETDATE(2014-06-11) Is it correct?

Solution 1

Hi,

Try This

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

SQL

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.

Posted 10-Jun-14 20:08pm

Magic Wonder

Comments

Magic Wonder 11-Jun-14 2:37am

At my end, default format for datetime datatype field is 2014-06-11 12:03:16.193.

Danuuu 11-Jun-14 2:43am

ya am also having same field

Magic Wonder 11-Jun-14 2:57am

It is working at my end and should work at your end also. What is the date stored in your logDateTime field?

Danuuu 11-Jun-14 2:59am

logDateTime
2014-05-11 08:09:15.000
2014-05-11 08:12:32.000
2014-05-11 08:15:01.000
2014-05-11 08:15:05.000
2014-05-11 08:18:13.000
2014-05-11 08:18:57.000
2014-05-11 08:20:06.000
2014-05-11 08:20:18.000
2014-05-11 08:20:22.000

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Magic Wonder · Accepted Answer · 2014-06-10T21:38:00

Solution 4

Check This,

SQL

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

Posted 10-Jun-14 21:38pm

Magic Wonder

Comments

Danuuu 11-Jun-14 3:59am

thank you so much for your help.But still it's showing absent

Danuuu 11-Jun-14 4:42am

sorry now it's working this answer was helpful and correct for this question.
But i have to do this without updating emp_status

Magic Wonder 11-Jun-14 5:12am

Do you want to update or just select?

Danuuu 11-Jun-14 6:40am

without updating using 3 tables i have to show employee attendance

Danuuu 11-Jun-14 6:42am

i have posted a new query there i posted 3 columns please check it