I want to show attendance with absent present status including week offs?

Question

User420834038 posted

empid   empname Date        attendance
MT1020  Abc     2020-03-31  P
MT1020  Abc     2020-04-01  P
MT1020  Abc     2020-04-02  P
MT1020  Abc     2020-04-03  A
MT1020  Abc     2020-04-04  WO
MT1020  Abc     2020-04-05  WO
MT1020  Abc     2020-04-06  P
MT1020  Abc     2020-04-07  A
MT1020  Abc     2020-04-09  A

i want the expected output like this. Below is my query i which i am getting records only till 7th april, and after that 9th instead it should show 8th as A-absent.
Kindly please help me into this issue.

------Query---------

SELECT DISTINCT m.empid, m.empname, COALESCE(k.Punchdate, l.Punchdate) AS 
Date,
(CASE WHEN k.Punchdate IS NULL THEN 'A'
ELSE 'P'
END) AS attendance
FROM Employee_Details m
CROSS JOIN EmpShift_Details l
LEFT JOIN EmpShift_Details k ON k.empid = m.empid
AND DATEDIFF(DAY, k.Punchdate, l.Punchdate) = 0
WHERE m.empid = 'MT1020'
AND l.Punchdate BETWEEN '2020-4-01' AND '2020-04-10'

Answer 1

User-719153870 posted

Hi priyanka.talekar,

This is little confusing, the alias l and k are for the same table EmpShift_Details?

And there are two records have "WO" as their attendance but we can not find related information from the query.

Can you provide all of your tables' structure and sample data so that we won't need to do lots of unnecessary guess?

Thanks!

Best Regard,

Yang Shen

Answer 2

User420834038 posted

Hi Yang Shen,

Thanks for your response.

i have two table one is employee_details and other is empshift_details in which the employee punch in and out time will save.

so i want the record of employee with their absent and present status. if employee is punch in it should show present as p and if employee is absent it should show absent as A.

If in between saturday sunday comes it should show Week off as WO instead of A and P.

This is table structure of employee_details and empshift_details 

employee_details

EmpID      EmpName    Email                    ContactNo      CreatedDate                     createdby

MT0120    ABC            [e-mail]     8767565645   2020-03-30 17:15:11.120  priyanka.talekar

empshift_details 

EmpshiftID   PunchIn                          PunchOut                           EmpID punchdate
9                  2020-03-31 14:18:48.810 NULL                                MT1020 2020-03-31
10                2020-04-01 16:06:40.303 2020-04-01 16:08:09.790 MT1020 2020-04-01
11                2020-04-02 11:48:03.703 NULL                                 MT0150 2020-04-02
12                2020-04-02 11:51:56.573 2020-04-02 12:04:14.797 MT1020 2020-04-02
18                2020-04-03 18:51:45.267 2020-04-03 18:52:24.117 MT0150 2020-04-03
20                2020-04-04 15:06:27.120 2020-04-04 15:10:16.070 MT0150 2020-04-04
21                2020-04-04 16:32:31.600 2020-04-04 16:38:32.727 MT1020 2020-04-04
22                2020-04-04 16:41:56.123 2020-04-04 16:44:17.773 MT0180 2020-04-04
23                2020-04-05 15:09:38.797 NULL                                 MT0150 2020-04-05

these are two tables and i want the output like this means it should show the data of employees with their date and absent present remark.

empid    empname          Date           attendance
MT0150 Priyanka Talekar 2020-03-31 A
MT0150 Priyanka Talekar 2020-04-01 A
MT0150 Priyanka Talekar 2020-04-02 P
MT0150 Priyanka Talekar 2020-04-03 P
MT0150 Priyanka Talekar 2020-04-04 P
MT0150 Priyanka Talekar 2020-04-05 P
MT0150 Priyanka Talekar 2020-04-06 P
MT0150 Priyanka Talekar 2020-04-07 P
MT0150 Priyanka Talekar 2020-04-09 P
MT0150 Priyanka Talekar 2020-04-13 P

i hope this helps

kindly help.

Answer 3

User-719153870 posted

Hi priyanka,

Not sure if i understood it right, i guess your issue is that you can not get some punchdate value when specific employee took a leave those days, which means there are no records for that employee at those days.

If so, and if your are trying to achieve one specific employee's status at a time, please check below demo:

In this demo, i used a custom function to make it clean when you use it for multiple employees:

drop table employee_details
drop table empshift_details

create table employee_details(EmpID varchar(10),EmpName varchar(10),Email varchar(50),ContactNo varchar(20),CraetedDate datetime,createdby varchar(20))

create table empshift_details(EmpshiftID int identity,PunchIn datetime,PunchOut datetime,EmpID varchar(10),punchdate date)

insert into employee_details values('MT1020','ABC','[e-mail]','8767565645','2020-03-30 17:15:11.120','priyanka.talekar'),('MT0150','EFG','[e-mail]','8767565123','2020-03-31 17:15:11.120','priyanka.talekar')

insert into empshift_details values
('2020-03-31 14:18:48.810',null,'MT1020','2020-03-31'),('2020-04-01 16:06:40.303','2020-04-01 16:08:09.790','MT1020','2020-04-01'),('2020-04-02 11:48:03.703',null,'MT0150','2020-04-02'),
('2020-04-02 11:51:56.573','2020-04-02 12:04:14.797','MT1020','2020-04-02'),('2020-04-03 18:51:45.267','2020-04-03 18:52:24.117','MT0150','2020-04-03'),('2020-04-04 15:06:27.120','2020-04-04 15:10:16.070','MT0150','2020-04-04'),
('2020-04-04 16:32:31.600','2020-04-04 16:38:32.727','MT1020','2020-04-04'),('2020-04-04 16:41:56.123','2020-04-04 16:44:17.773','MT0180','2020-04-04'),('2020-04-05 15:09:38.797',null,'MT0150','2020-04-05')

select * from employee_details
select * from empshift_details

IF OBJECT_ID (N'CheckOne', N'IF') IS NOT NULL  
    DROP FUNCTION CheckOne;  
GO  
CREATE FUNCTION CheckOne (@EmpID varchar(10),@start date,@end date)  
RETURNS TABLE  
AS  
RETURN   
(  
    select @EmpID as EmpID,
     (select EmpName from employee_details where EmpID=@EmpID) as EmpName,
    s.punchdate,
    (case when ss.EmpID is null then 'A' when DATEPART(DW, s.punchdate)=1 or DATEPART(DW, s.punchdate)=7 then 'WO' else 'P' end) as [attendance]
    from (select distinct punchdate from empshift_details) s left join (select * from empshift_details where EmpID=@EmpID) ss on s.punchdate=ss.punchdate
    left join employee_details d on ss.EmpID=d.EmpID where s.punchdate BETWEEN @start AND @end
); 

select * from CheckOne('MT1020','2020-03-31','2020-04-04')

As you can see, i select the punchdate rather than EmpID, snice the ouchdate includes every day while the existence of employees at each day is uncertain.

The result of this demo:

Best Regard,

Yang Shen

Answer 4

User420834038 posted

Hi Yang Shen,

My issue is i want the attendance report with their date and attendance status as Absent and present, if on some days the employee doesn't comes or the employee is absent it should show absent-A on that particular days and it should show the record till todays date like on which date employee was present and on which date the employee was absent and on which days there was week off. Accordingly i want the procedure for this issue.

Like this i want the report.

I hope this helps.

Thanks 

Priyanka.

Answer 5

User-719153870 posted

Hi priyanka,

on which date employee was present and on which date the employee was absent and on which days there was week off.

This is what i was doing and the custom function i provided should achieve this. Have you tried the solution i suggested?

In this custom function, it accepts three parameters: employee id, start date and end date which define the date range you want to query. And it returns this employee's absent status in this date range.

Or if the solution is not what you want, please tell which part it needs to be updated.

Thanks!

Best Regard,

Yang Shen

Answer 6

User420834038 posted

Hi Yang ,

I want the employee monthly report with start date and end date in which it should come empid empname attendancedate punchin punchout and remark of absent or present, if in case any employee doesn't come today so it should show Absent as remark or else present.

And the solution u suggested its not working with expected output. I want the Stored Procedure  which i can use in my code

this is output i was expecting.

EmpID	EmpName	Attendance Date	PunchIn	PunchOut		Attendance
MT0150	Priyanka Talekar	04-02-2020	11:48:04 AM	9:00:00 PM		Present
MT0150	Priyanka Talekar	04-03-2020	6:51:45 PM	6:52:24 PM		Present
MT0150	Priyanka Talekar	04-04-2020	NULL	NULL		Absent
MT0150	Priyanka Talekar	04-05-2020	NULL	NULL		Absent
MT0150	Priyanka Talekar	04-06-2020	10:00:00 AM	9:00:00 PM		Present
MT0150	Priyanka Talekar	04-07-2020	10:38:38 AM	6:55:27 PM		Present
MT0150	Priyanka Talekar	04-08-2020	12:55:20 PM	6:08:36 PM		Present

Thanks.

Answer 7

User-719153870 posted

Hi priyanka,

employee monthly report with start date and end date in which it should come empid empname attendancedate punchin punchout and remark of absent or present

I want the Stored Procedure  which i can use in my code

Custom funtion can also be used just like the SP and even easier to use, check below SP demo if you insist:

create proc SelectAttendance @EmpID varchar(10),@start date,@end date
as
 select @EmpID as EmpID,
     (select EmpName from employee_details where EmpID=@EmpID) as EmpName,
    s.punchdate as [Attendance Date],ss.PunchIn,ss.PunchOut,
    (case when ss.EmpID is null then 'Absent' when DATEPART(DW, s.punchdate)=1 or DATEPART(DW, s.punchdate)=7 then 'WO' else 'Present' end) as [attendance]
    from (select distinct punchdate from empshift_details) s left join (select * from empshift_details where EmpID=@EmpID) ss on s.punchdate=ss.punchdate
    left join employee_details d on ss.EmpID=d.EmpID where s.punchdate BETWEEN @start AND @end
go

exec SelectAttendance @EmpID ='MT1020',@start='2020-03-31',@end='2020-04-04'

As you can see, this SP accepts 3 parameters and return all the fields you want.

Check SQL Stored Procedures for SQL Server for more information about Stored Procedure With Multiple Parameters.

Best Regard,

Yang Shen