Asked by:
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'
Tuesday, April 14, 2020 6:10 AM
All replies
-
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
Tuesday, April 14, 2020 7:50 AM -
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 Abc@gmail.com 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-05these 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 Pi hope this helps
kindly help.
Tuesday, April 14, 2020 8:24 AM -
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','Abc@gmail.com','8767565645','2020-03-30 17:15:11.120','priyanka.talekar'),('MT0150','EFG','Efg@gmail.com','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
Wednesday, April 15, 2020 5:13 AM -
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.
Wednesday, April 15, 2020 6:05 AM -
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
Thursday, April 16, 2020 2:28 AM -
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.
Thursday, April 16, 2020 5:56 AM -
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 presentI want the Stored Procedure which i can use in my codeCustom 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
Friday, April 17, 2020 2:56 AM