query for Employee Attendance

Question

1.00/5 (2 votes)

See more:

i need to show employee attendance using tables dbo.tempDeviceLogs , dbo.Emp_setting, dbo.persons_profile

dbo.tempDeviceLogs columns are

SQL

columnname        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

SQL

columnname     datatype       allownulls
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

dbo.persons_profile

SQL

columnname             datatype      allownulls
pesonal_id	        int	      Unchecked
Emp_Code	       nchar(15)      Checked
Title	               nchar(4)	      Unchecked
First_name	       varchar(35)    Unchecked
Middle_name	       varchar(35)     Checked
last_name	        varchar(35)    Checked
Father_Husband_Name	varchar(35)    Unchecked
Dob	                datetime       Unchecked
Age	                 int	       Unchecked
gender	               nchar(1)        Unchecked
Marital_status	      nchar(1)	      Unchecked
Nationality	     nchar(10)	      Unchecked
bloodGroup	    nchar(10)	     Unchecked
perAddress	    nchar(100)	     Unchecked
PerStreet	     nchar(100)	     Checked
PerLocation	    nchar(50)	    Unchecked
PerCity	          nchar(20)     Checked
PerPincode	nchar(6)	Checked
CorAddress	nchar(50)	Checked
CorStreet	nchar(50)	Checked
CorLocation	nchar(50)	Checked
CorCity	        nchar(20)	Checked
CorPincode	nchar(6)	Checked
LandlinePhone	nchar(24)	Checked
cellNo	       nchar(24)	Checked
EmailId	       nchar(35)	Checked
NosofDependendants	int	Checked
Dependendants_details	ntext	Checked
Emergency_FirstName	nchar(35)	Checked
Emergency_Middle_name	nchar(35)	Checked
Emergency_Last_name	nchar(35)	Checked
Emergency_WithRelation	nchar(25)	Checked
Emergency_PhoneNo	nchar(22)	Checked
Emergency_CellNo	nchar(22)	Checked
Emergency_emailId	nchar(35)	Checked
Office_PF_ac_no	        nchar(20)       Checked
ESI_ac_no	       nchar(20)	Checked
JoinedDate	       datetime	       Checked
Photofile	      nchar(50)	       Checked
ReportTo	     varchar(50)       Checked
Brief_Notes	     nchar(150)	        Checked
dateofTermination	varchar(10)	Checked
termination_note	nchar(50)	Checked
Print_Priority	        int	        Checked
DeviceEmployeeID	nchar(25)	Checked
LogsPermitted	       int	       Unchecked
Machin_install_id	int	      Checked
Designation	    nchar(100)	     Checked
Dept	          nchar(100)	     Checked
Section	         nchar(100)	     Checked
Groups	         nchar(100)	     Checked
EmpWorkingTypeT	nchar(100)	     Checked

Required output is

SQL

Employeename(Fname) Device_person_id Designation emp_typeid date present/absent

if we select todays date then it should show above details for today

SQL

SELECT b.Device_Person_ID, a.Personal_id, Date1, CASE WHEN b.Device_Person_id IS NOT NULL THEN 'A' ELSE 'P' END as Emp_Status

FROM Emp_setting a LEFT OUTER JOIN (SELECT device_person_id, MAX(logDateTime) AS Date1 FROM tempDeviceLogs GROUP BY device_person_id) b

ON a.personal_id = b.device_person_id

Posted 11-Jun-14 0:28am

Danuuu

Updated 13-Jun-14 1:01am

v9

Add a Solution

Comments

OriginalGriff 11-Jun-14 6:52am

And?
What have you tried?
Where are you stuck?

Because just throwing your table definitions and asking a vague question doesn't help us much...

[no name] 11-Jun-14 7:33am

What makes you think that you have to update columns in order to show data?

Danuuu 11-Jun-14 7:52am

i got answer by updating so

2 solutions

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 · Answer 1 · 2014-06-11T02:02:00

Solution 1

Try This,

SQL

SELECT 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 FROM Emp_setting

Hope this will help you.

Posted 11-Jun-14 2:02am

Magic Wonder

Comments

Danuuu 11-Jun-14 8:18am

i need to use columns from dbo.tempdevicelogs and dbo.persons_profile it means in result

logsid deviceperson_id device_id logdatetime inout department designation etc
all columns should be there
these all columns i mentioned in above code

Magic Wonder 11-Jun-14 9:27am

you write a query as per your required output and share if in case you are not getting the desired output.

Danuuu 12-Jun-14 1:47am

Select Device_Person_id,personal_id,Date1,(
cASE WHEN eXISTS(
sELECT Device_Person_id FROM tempDeviceLogs AT WHERE T.personal_id=AT.Device_Person_id AND T.date1=AT.logDateTime
) then 'P' Else 'A' End )as Status
FROM
(
Select Device_Person_id,personal_id,Cast(logDateTime as DATE)AS DATE1 from Emp_setting a,(Select Distinct logDateTime from tempDeviceLogs) b
) T

SHOWING ERROR
Invalid column name 'Device_Person_id'.

Magic Wonder · Answer 2 · 2014-06-11T19:56:00

Solution 2

Check this one

SQL

Select Device_Person_id,personal_id,Date1,(
cASE WHEN eXISTS(
sELECT Device_Person_id 
FROM tempDeviceLogs AT WHERE T.personal_id=AT.Device_Person_id AND T.date1=AT.logDateTime
) then 'P' Else 'A' End )as Status
FROM
(
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.

SQL

Select Device_Person_id,personal_id,Date1,(
cASE WHEN eXISTS(
sELECT Device_Person_id
FROM tempDeviceLogs AT WHERE T.personal_id=AT.Device_Person_id AND T.date1=AT.logDateTime
) then 'P' Else 'A' End )as Status
FROM
(
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.

Posted 11-Jun-14 19:56pm

Magic Wonder

Updated 12-Jun-14 3:10am

v4

Comments

Danuuu 12-Jun-14 2:23am

same error

Magic Wonder 12-Jun-14 2:36am

Check updated solution

Danuuu 12-Jun-14 3:19am

Invalid object name 'tempDeviceLogs_1'.

Magic Wonder 12-Jun-14 3:25am

Oh....sorry, just check updated solution now

Danuuu 12-Jun-14 3:42am

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

Magic Wonder 12-Jun-14 3:49am

check this one.

http://support.microsoft.com/kb/909678

Danuuu 12-Jun-14 3:59am

Do you know any other method for getting solution

Magic Wonder 12-Jun-14 7:44am

@Dannu....there might be more reasons for that error. Being on remote place i can not say exactly what is happening during your query execution?

Also, you can take help of Google for sorting out this error.

Danuuu 12-Jun-14 8:01am

can this one be solved with inner join any idea??

Danuuu 12-Jun-14 8:08am

using left inner join if logdatetime is set to null then emp_attendance will show null na
i dont have clear idea about inner joins if possible help me

Magic Wonder 12-Jun-14 8:13am

why join is required? How many records are there in your tables?

Danuuu 12-Jun-14 8:19am

i didnt got your question

Magic Wonder 12-Jun-14 8:37am

I mean to ask you that why you want to use Join in your query now? Is that query not working at all?

Danuuu 12-Jun-14 8:38am

ya that query is not working

Magic Wonder 12-Jun-14 9:10am

check updated query.

Danuuu 13-Jun-14 1:23am

Invalid object name 'Emp_setting'.

Danuuu 13-Jun-14 2:21am

SELECT 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 as Emp_Status FROM Emp_setting

this code is executing showing only one column i.e emp_status
how to add device_person_id,personal_id,department,designation columns to this code only