query for employee attendance

[Danuuu]

hi
i need to show employee attendance based on device logins with employess various information like department,designation,device_person_id,person_id,logid etc.....
joining 3 tables i.e dbo.tempdevicelogs,dbo.employee_settings,dbo.persons_profile

i tried this one

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

but it's causing error Invalid column name 'Device_Person_id'

how to solve this problem is leftjoin or with inout column

dbo.tempdevicelogs data is

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 data is

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 data is

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

[ChrisHunter]

Your naming conventions aren't clear. It's considered bad practise to use use special charactor like _ to separate the words within a field name. Instead simply capitalise the first letter of each word (i.e. DevicePersonId and not Device_Person_id).

Also when you give a table an alias within an SQL statement it should be something meaningful. For example a more meaningful alias for dbo.tempdevicelogs would be tdl (the first letter of each word in table names should be capitalised too i.e. dbo.TempDeviceLogs).

If you want to join tables that have a common field you should used the JOIN key word rather than doing nested select statements if possible.

In all honesty I can't work out what you're trying to do.

If you list what fields you want, the table you want the field from and under what conditions I might be able to help you out.

What is Date1?