i'm generating Monthly Attendance report sheet where i am able to get records of those employees you marked there attendance on certain day. here the problem that i'm facing is i need each employee listed in the report whether his record exists in attendance table or not.

i have used right outer join but id didn't worked for me, i have look around to other solution but no one solve my problem

```
Declare @fkSerial   [numeric]=0;
Declare @attDateTime [datetime]='2019-04-01';
Declare @Company nvarchar(50)='ZCG';
Declare @Department nvarchar(50)=null;
Declare @StartOfMonth Datetime
Declare @EndOfMonth Datetime
declare @month int
declare @year int
declare @day int
set @month =DATEPART(month, @attDateTime)
set @year =DATEPART(year, @attDateTime)
set @day =DATEPART(day, @attDateTime)
set @StartOfMonth = DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
set @EndOfMonth =  dateadd(SECOND,-1,DATEADD(MONTH,datediff(MONTH,0,GETDATE())+1,0));
With DATERANGE  as(
SELECT DT =DATEADD(DD,0,@StartOfMonth)  
       WHERE DATEADD(DD, 1,@StartOfMonth) <=@EndOfMonth 
       UNION ALL  
       SELECT DATEADD(DD, 1, DT)  
       FROM DATERANGE  
       WHERE DATEADD(DD, 1, DT) <=@EndOfMonth 

)

SELECT fkSerial,company,serial, 
 name,department,attINOUT,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30]
FROM 
(SELECT Day(attDateTime) as d1,fkSerial,attDateTime, tblEmployInfo.company,  tblEmployInfo.serial, tblAttendance.attINOUT,
 tblEmployInfo.name,tblEmployInfo.department
FROM tblAttendance RIGHT outer JOIN
    tblEmployInfo ON tblAttendance.fkSerial = tblEmployInfo.serial
where(     
(tblAttendance.fkSerial = @fkSerial  OR @fkSerial  = 0) AND 
(tblEmployInfo.department = @Department OR @Department is null) And 
(tblAttendance.attDate in (select * from DATERANGE)) and 
(tblEmployInfo.company= @Company or  @Company is null)
and tblAttendance.attINOUT=0
)
group by  tblAttendance.attDateTime,tblAttendance.fkSerial,tblEmployInfo.company,tblEmployInfo.serial,tblAttendance.attINOUT,tblEmployInfo.name,tblEmployInfo.department
having tblAttendance.attDateTime= min(tblAttendance.attDateTime)

) p
PIVOT(
MAx(p.attDateTime)
FOR d1 IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30] ) ) AS pvt
ORDER BY 
pvt.fkSerial

```

i need each employee listed in the report whether his record exists in attendance table or not