Click here to Skip to main content
15,308,835 members
Please Sign up or sign in to vote.
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
Updated 13-Jun-14 1:01am
v9
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

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.
   
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'.
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.
   
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900