Hello,
Kindly explain how to get report and data from the following sql table.
1. AttData
SELECT TOP 1000 [ID]
,[EmpID]
,[EmpName]
,[Date]
,[Time]
,[DeviceNo]
,[State]
FROM [AttendanceSystem].[dbo].[AttData]
2. Daily Schedule
SELECT TOP 1000 [ID]
,[EmpID]
,[Date]
,[Time]
,[Remarks]
FROM [AttendanceSystem].[dbo].[Dailyschedule]
I want to take the report from both table in single query
Select a.EmpID,b.Time, a.Timein,a.Timeout,b.Remarks
where AttData a, dailyschedule b
AttData Values
20160107 PrasathKumar 2019-09-04 11:06:22 1 0
20160107 PrasathKumar 2019-09-04 22:18:01 1 1
Daily Schedule Values
EmpID Date Time Remarks
20190802 2019-09-04 1900-01-01 11:00:00.000
I want the result
Emp Id, EmpName, time from dailyschedule, Time In from Attdata, TimeOut from AttData, remarks from Daiyschedule
Time IN and Time based on the State in attData Table
I want When State is 0 mean to display TimeIn, state is 1 mean display TimeOut.
I want the report Same like the below for Each Employee separate.
NO |
DATE |
START TIME |
TIME IN |
TIME OUT |
TIME DIFFRENCE |
REMARKS |
1 |
01-Feb-19 |
13:00:00 |
12:58:26 |
22:54:04 |
#VALUE! |
ONTIME |
2 |
02-Feb-19 |
10:30:00 |
10:34:23 |
14:42:23 |
00:04:23 |
LATE |
3 |
03-Feb-19 |
10:30:00 |
10:23:43 |
17:08:59 |
#VALUE! |
ONTIME |