I am importing data into a database table from text(.txt) files. Because of this text file have only Attendance data like if someone present that day or swap his ID card to Attendance device only that day's data will be saved. Now I am trying to export/represent data in a manner where query must return all the values if someone does not presents his data should be shown like, "His ID and Attendance day will show but In/Out time with zero or null value"
I already tried with UNION/UNION ALL and JOIN but My result did not fit with my expectation.
Result I am getting: -
Result I expected: -
AttendDate | Emplid | EmplName | OutTime | InTime
2019-06-27 | SAO-21-001| Suman Mondol| 14:53:47 |13:28:34
2019-06-27 | SAO-21-001| Suman Mondol| 00:00:00 |00:00:00
2019-06-29 | SAO-21-001| Suman Mondol| 13:17:12 |9:08:48
I have tried followeing query for this:
Select distinct c.Date as Attendate
,t.EmplId
,t.Name
,t.AttendTimeMax
,t.AttendTimeMin
from AccessAttenHO t Right Outer JOin dbo.HRCalendar c on t.AttendDate=c.Date
where c.Date between '2019-06-01' and '2019-06-30'
order by t.EmplId;
and Master table "AccessAttenHO" Populate data as follows:
Insert into AccessAttenHO(EmplId,Name,AttendDate,AttendTimeMax,AttendTimeMin, IsLate,UserId,TranDate,BranId,AttendType,DeptId)
select HREmployee.emplid,
HREmployee.EmplFirstName,
TempAttendImport.AttendDate,
MAX(CAST(TempAttendImport.AttendTime as time))maxtime,
MIN(CAST(TempAttendImport.AttendTime as time))mintime,
(case
when HREmployee.AttendType='General' and (MIN(cast(AttendTime as time))) >=CAST('09:16:00' AS time)
THEN 'T'
ELSE 'F'
END) ISLATE , TempAttendImport.UserId,TempAttendImport.TrnsDate,HREmployee.BranId,HREmployee.AttendType,HREmployee.DeptId
from TempAttendImport, HREmployee
where TempAttendImport.EmplId =HREmployee.IDCARDNO
group by HREmployee.emplid,TempAttendImport.AttendDate,HREmployee.EmplFirstName, HREmployee.AttendType,TempAttendImport.UserId,TempAttendImport.TrnsDate,HREmployee.BranId,HREmployee.AttendType,HREmployee.DeptId
ORDER BY HREmployee.emplid
