Click here to Skip to main content
15,905,508 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.
 
Share this answer
 
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.
 
Share this answer
 
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.

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