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

  • 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-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.

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

    Friday, April 17, 2020 2:56 AM