How to return missing dates where missing dates are not in Master table

Viewed 67

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: -

enter image description here

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
0 Answers
Related