Forum Stats

  • 3,874,184 Users
  • 2,266,680 Discussions
  • 7,911,761 Comments

Discussions

Miss Punch in Information Query

M.bro
M.bro Member Posts: 134 Blue Ribbon
edited Nov 17, 2022 10:22AM in SQL & PL/SQL

Hi Team,

I am using oracle 12c Database.


i have one requirement to show the miss punch entries for employees wise or all employee between two dates.(one year/ One week or one days also user will run this report)

I using 4 tables like below Attendances, Calendar, Leave, Other visit. (One month sample script i added in the below)

Employee can register there leave (leave table) and other visit to client(othervisit table) and punch in details to the attendance machine and admin can configure the working days in the calendar table.

My requirement if attendance table and other three table don't get the employee log in details/Leave/Other visit details then for those missing days. i need to show like below.

employee id Missing Date Remarks

10001 24-nov-2022 MISS PUNCH IN

10001 25-nov-2022 MISS PUNCH IN

....

...

10001 30-nov-2022 MISS PUNCH IN

10002 07-nov-2022 MISS PUNCH IN

10002 16-nov-2022 MISS PUNCH IN

10003 01-nov-2022 MISS PUNCH IN

.....

10003 16-nov-2022 MISS PUNCH IN

10003 18-nov-2022 MISS PUNCH IN

.......

10003 30-nov-2022 MISS PUNCH IN

Employee - 1

Date : 7 to 16,18 to 23,24 to 30

7 TO 16 LEAVE  

18 TO 23 OTHER VISIT

24 TO 30 MISS PUNCH IN


Employee - 2

Date : 6,7,8,16,18 to 30

 6 - LEAVE

7- MISS PUNCH IN

8 TO 10 LEAVE

16 -MISS PUNCH IN

18 TO 30 OTHER VISIT


Employee - 3

1 TO 16 MISS PUNCH IN

18 TO 29 MISS PUNCH IN


--------------

--Script

Create table Attendances (att_id number,emp_id number,login_date date,

att_created_date date,created_by varchar2(100),att_type varchar2(100));


INSERT INTO Attendances VALUES(1,10001,TO_DATE('01-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(2,10001,TO_DATE('02-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(3,10001,TO_DATE('03-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(4,10001,TO_DATE('04-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(5,10001,TO_DATE('05-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(6,10001,TO_DATE('06-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(7,10001,TO_DATE('17-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(8,10002,TO_DATE('01-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(9,10002,TO_DATE('02-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(10,10002,TO_DATE('03-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(11,10002,TO_DATE('04-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(12,10002,TO_DATE('05-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(13,10002,TO_DATE('11-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(14,10002,TO_DATE('12-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(15,10002,TO_DATE('13-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(16,10002,TO_DATE('14-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(17,10002,TO_DATE('15-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(18,10002,TO_DATE('17-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(19,10003,TO_DATE('17-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');

INSERT INTO Attendances VALUES(20,10003,TO_DATE('30-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');


Create table Calendar (ac_seq_no number,

  ac_year number,

ac_date date,

ac_hol_flag varchar2(1) default 'N',

AC_CREATED_BY VARCHAR2(1000),

AC_CREATED_DATE DATE);

INSERT INTO Calendar VALUES (1,2022,TO_DATE('01-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (2,2022,TO_DATE('02-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (3,2022,TO_DATE('03-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (4,2022,TO_DATE('04-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (5,2022,TO_DATE('05-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (6,2022,TO_DATE('06-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (7,2022,TO_DATE('07-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (8,2022,TO_DATE('08-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (9,2022,TO_DATE('09-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (10,2022,TO_DATE('10-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (11,2022,TO_DATE('11-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (12,2022,TO_DATE('12-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (13,2022,TO_DATE('13-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (14,2022,TO_DATE('14-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (15,2022,TO_DATE('15-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (16,2022,TO_DATE('16-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (17,2022,TO_DATE('17-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (18,2022,TO_DATE('18-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (19,2022,TO_DATE('19-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (20,2022,TO_DATE('20-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (21,2022,TO_DATE('21-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (22,2022,TO_DATE('22-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (23,2022,TO_DATE('23-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (24,2022,TO_DATE('24-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (25,2022,TO_DATE('25-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (26,2022,TO_DATE('26-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (27,2022,TO_DATE('27-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (28,2022,TO_DATE('28-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (29,2022,TO_DATE('29-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

INSERT INTO Calendar VALUES (30,2022,TO_DATE('30-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);

Create table lEAVES (LE_id number,LE_emp_id number,le_date_from date,le_date_TO date,

le_REJOIN_date date,

LE_created_date date,LE_created_by varchar2(100),lEAVE_type varchar2(100));


INSERT INTO lEAVES VALUES (1,10001,TO_DATE('07-NOV-2022','DD-MM-RRRR'),TO_DATE('16-NOV-2022','DD-MM-RRRR'),TO_DATE('17-NOV-2022','DD-MM-RRRR'),

SYSDATE,'ADMIN','V');

INSERT INTO lEAVES VALUES (2,10002,TO_DATE('06-NOV-2022','DD-MM-RRRR'),TO_DATE('06-NOV-2022','DD-MM-RRRR'),TO_DATE('07-NOV-2022','DD-MM-RRRR'),

SYSDATE,'ADMIN','S');

INSERT INTO lEAVES VALUES (3,10002,TO_DATE('08-NOV-2022','DD-MM-RRRR'),TO_DATE('10-NOV-2022','DD-MM-RRRR'),TO_DATE('11-NOV-2022','DD-MM-RRRR'),

SYSDATE,'ADMIN','S');


Create table othervisit (OV_id number,OV_emp_id number,OV_date_from date,OV_date_TO date,

OV_created_date date,OV_created_by varchar2(100));


INSERT INTO othervisit VALUES (1,10001,TO_DATE('18-NOV-2022','DD-MM-RRRR'),TO_DATE('23-NOV-2022','DD-MM-RRRR'),

SYSDATE,'ADMIN');

INSERT INTO othervisit VALUES (2,10002,TO_DATE('18-NOV-2022','DD-MM-RRRR'),TO_DATE('30-NOV-2022','DD-MM-RRRR'),

SYSDATE,'ADMIN');

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,746 Red Diamond

    Hi, @M.bro

    In my data ov_date_from and ov_date_to not only date the field is date and time.

    So, othervisit.ov_date_from and ov_date_to can have times like 14:00:00, but calendar.ac_date is always 00:00:00. When comparing the two tables, you want to consider rows to match if the year, month and day are the same, regardless of the hours, minutes and seconds. Is that right?

    If so, change

    	JOIN	days_wanted  d3   ON  d3.a_date  BETWEEN o.ov_date_from
    			     	      		 AND	 o.ov_date_to
    

    to

    	JOIN	days_wanted  d3   ON  d3.a_date  BETWEEN TRUNC (o.ov_date_from)
    			     	      		 AND	 o.ov_date_to
    

    You don't have to change the upper bound. If d3.a_date (which always has the time 00:00:00) is less than or equal to TRUNC (o.ov_date), then it will necessarily be less than or equal to o.ov_date, too.

    The complete query is:

    WITH    params (start_date, end_date)    AS
    (
    	SELECT  TO_DATE ('01-NOV-2022', 'DD-MON-YYYY')  AS start_date
    	,	TO_DATE ('30-NOV-2022', 'DD-MON-YYYY')  AS end_date
    	FROM	dual
    )
    ,	days_wanted (a_date)    AS
    (
            SELECT  ac_date
    	FROM	params    p
    	JOIN	calendar  c   ON  c.ac_date  BETWEEN  p.start_date
    			      	  	     AND      p.end_date
    --	WHERE
    )
    ,    days_accounted (emp_id, a_date)   AS
    (
    	SELECT  a.emp_id, a.login_date
    	FROM	params        p1
    	JOIN	attendances   a   ON  a.login_date  BETWEEN  p1.start_date
    			      	      		    AND      p1.end_date
    UNION ALL
            SELECT  l.le_emp_id, d2.a_date
    	FROM	leaves       l
    	JOIN	days_wanted  d2   ON  d2.a_date  BETWEEN l.le_date_from
    			     	      		 AND	 l.le_date_to
    UNION ALL
            SELECT  o.ov_emp_id, d3.a_date
    	FROM	othervisit   o
    	JOIN	days_wanted  d3   ON  d3.a_date  BETWEEN TRUNC (o.ov_date_from)	-- This line is changed
    			     	      		 AND	 o.ov_date_to
    )
    ,    employees    AS
    (
    	SELECT DISTINCT  emp_id
    	FROM   		 days_accounted
    )
    SELECT     e.emp_id
    ,	   dw.a_date		AS missing_date
    ,	   'MISS PUNNCH IN' 	AS remarks
    FROM	   days_wanted     dw
    CROSS JOIN employees       e
    LEFT JOIN  days_accounted  da  ON   da.emp_id  = e.emp_id
         	   		       AND  da.a_date  = dw.a_date
    WHERE	   da.emp_id  IS NULL
    ORDER BY   emp_id, missing_date	-- or whatever you want
    ;
    
    
    


Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,746 Red Diamond

    Hi, @M.bro

    Thanks for posting the sample data..

     i need to show like below.

    employee id Missing Date Remarks

    10001 24-nov-2022 MISS PUNCH IN

    10001 25-nov-2022 MISS PUNCH IN

    ....

    ...

    10001 30-nov-2022 MISS PUNCH IN

    10002 07-nov-2022 MISS PUNCH IN

    10002 16-nov-2022 MISS PUNCH IN

    10003 01-nov-2022 MISS PUNCH IN

    .....

    10003 16-nov-2022 MISS PUNCH IN

    10003 18-nov-2022 MISS PUNCH IN

    .......

    10003 30-nov-2022 MISS PUNCH IN

    Employee - 1

    Date : 7 to 16,18 to 23,24 to 30

    7 TO 16 LEAVE  

    18 TO 23 OTHER VISIT

    24 TO 30 MISS PUNCH IN

    Is the section starting with "Employee - 1" part of the desired output?

    In the first section, will the remarks column always be 'MISS PUCH IN'?

    When you have "..." in the results, does that mean you want one row for every day between the date above that line and the date below?

    When you say "Employee - 1" do you mean Employee 10001?

  • M.bro
    M.bro Member Posts: 134 Blue Ribbon

    @Frank Kulash thanks for your reply.

    Is the section starting with "Employee - 1" part of the desired output? - No need

    In the first section, will the remarks column always be 'MISS PUCH IN'? - Yes

    When you have "..." in the results, does that mean you want one row for every day between the date above that line and the date below? - i will share my output structure clearly.

    When you say "Employee - 1" do you mean Employee 10001? - My employee id 10001

    My output.



  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,746 Red Diamond

    Hi, @M.bro

    Let me make sure I understand the requirements. You want to pass some parameters to a query to indicate which dates from the calendar table you want to include. You want to create a result set that has one row for each distinct combination of an employee and a day wanted, such that the combination does not occur in any of the other tables: attendances, leaves and othervisits. You can do that by cross-joining a table with one row for each day wanted with a table that has one row per for each employee, then outer joining that result set to the other tables. Do you have a table with exactly one row for every employee? The query below assumes you have only the tables you posted, so it generates a result set (called employees) with one row per employee. If you actually do have a table, then use it in place of employees below.

    WITH    params (start_date, end_date)    AS
    (
    	SELECT  TO_DATE ('01-NOV-2022', 'DD-MON-YYYY')  AS start_date
    	,	TO_DATE ('30-NOV-2022', 'DD-MON-YYYY')  AS end_date
    	FROM	dual
    )
    ,	days_wanted (a_date)    AS
    (
            SELECT  ac_date
    	FROM	params    p
    	JOIN	calendar  c   ON  c.ac_date  BETWEEN  p.start_date
    			      	  	     AND      p.end_date
    --	WHERE
    )
    ,    days_accounted (emp_id, a_date)   AS
    (
    	SELECT  a.emp_id, a.login_date
    	FROM	params        p1
    	JOIN	attendances   a   ON  a.login_date  BETWEEN  p1.start_date
    			      	      		    AND      p1.end_date
    UNION ALL
            SELECT  l.le_emp_id, d2.a_date
    	FROM	leaves       l
    	JOIN	days_wanted  d2   ON  d2.a_date  BETWEEN l.le_date_from
    			     	      		 AND	 l.le_date_to
    UNION ALL
            SELECT  o.ov_emp_id, d3.a_date
    	FROM	othervisit   o
    	JOIN	days_wanted  d3   ON  d3.a_date  BETWEEN o.ov_date_from
    			     	      		 AND	 o.ov_date_to
    )
    ,    employees    AS
    (
    	SELECT DISTINCT  emp_id
    	FROM   		 days_accounted
    )
    SELECT     e.emp_id
    ,	   dw.a_date		AS missing_date
    ,	   'MISS PUNNCH IN' 	AS remarks
    FROM	   days_wanted     dw
    CROSS JOIN employees       e
    LEFT JOIN  days_accounted  da  ON   da.emp_id  = e.emp_id
         	   		       AND  da.a_date  = dw.a_date
    WHERE	   da.emp_id  IS NULL
    ORDER BY   emp_id, missing_date	-- or whatever you want
    ;
    
    
    

    Do you really want the last row of output that you posted? Isn't there a row in the attendances table for emp_id=10003 on November 30?

  • M.bro
    M.bro Member Posts: 134 Blue Ribbon

    @Frank Kulash

    Thanks for your reply. It's working what i expected. thanks you so much.

    I find only one issue. Othervisit table like below

    SELECT  o.ov_emp_id, d3.a_date
    	FROM	othervisit   o
    	JOIN	days_wanted  d3   ON  d3.a_date  BETWEEN o.ov_date_from
    			     	      		 AND	 o.ov_date_to
    
    In my data ov_date_from and ov_date_to not only date the field is date and time.
    
    

    those record not filtering in the above query. Please find the other visit new insert script.


    INSERT INTO othervisit VALUES (1,10001,TO_DATE('18-NOV-2022 07:00:00','DD-MM-RRRR HH24:MI:SS'),TO_DATE('23-NOV-2022 14:00:00','DD-MM-RRRR HH24:MI:SS'),

    SYSDATE,'ADMIN');

    INSERT INTO othervisit VALUES (2,10002,TO_DATE('18-NOV-2022 07:00:00','DD-MM-RRRR HH24:MI:SS'),TO_DATE('30-NOV-2022 14:00:00','DD-MM-RRRR HH24:MI:SS'),

    SYSDATE,'ADMIN');


    Other than everything working fine. Once again thank you so much.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,746 Red Diamond

    Hi, @M.bro

    In my data ov_date_from and ov_date_to not only date the field is date and time.

    So, othervisit.ov_date_from and ov_date_to can have times like 14:00:00, but calendar.ac_date is always 00:00:00. When comparing the two tables, you want to consider rows to match if the year, month and day are the same, regardless of the hours, minutes and seconds. Is that right?

    If so, change

    	JOIN	days_wanted  d3   ON  d3.a_date  BETWEEN o.ov_date_from
    			     	      		 AND	 o.ov_date_to
    

    to

    	JOIN	days_wanted  d3   ON  d3.a_date  BETWEEN TRUNC (o.ov_date_from)
    			     	      		 AND	 o.ov_date_to
    

    You don't have to change the upper bound. If d3.a_date (which always has the time 00:00:00) is less than or equal to TRUNC (o.ov_date), then it will necessarily be less than or equal to o.ov_date, too.

    The complete query is:

    WITH    params (start_date, end_date)    AS
    (
    	SELECT  TO_DATE ('01-NOV-2022', 'DD-MON-YYYY')  AS start_date
    	,	TO_DATE ('30-NOV-2022', 'DD-MON-YYYY')  AS end_date
    	FROM	dual
    )
    ,	days_wanted (a_date)    AS
    (
            SELECT  ac_date
    	FROM	params    p
    	JOIN	calendar  c   ON  c.ac_date  BETWEEN  p.start_date
    			      	  	     AND      p.end_date
    --	WHERE
    )
    ,    days_accounted (emp_id, a_date)   AS
    (
    	SELECT  a.emp_id, a.login_date
    	FROM	params        p1
    	JOIN	attendances   a   ON  a.login_date  BETWEEN  p1.start_date
    			      	      		    AND      p1.end_date
    UNION ALL
            SELECT  l.le_emp_id, d2.a_date
    	FROM	leaves       l
    	JOIN	days_wanted  d2   ON  d2.a_date  BETWEEN l.le_date_from
    			     	      		 AND	 l.le_date_to
    UNION ALL
            SELECT  o.ov_emp_id, d3.a_date
    	FROM	othervisit   o
    	JOIN	days_wanted  d3   ON  d3.a_date  BETWEEN TRUNC (o.ov_date_from)	-- This line is changed
    			     	      		 AND	 o.ov_date_to
    )
    ,    employees    AS
    (
    	SELECT DISTINCT  emp_id
    	FROM   		 days_accounted
    )
    SELECT     e.emp_id
    ,	   dw.a_date		AS missing_date
    ,	   'MISS PUNNCH IN' 	AS remarks
    FROM	   days_wanted     dw
    CROSS JOIN employees       e
    LEFT JOIN  days_accounted  da  ON   da.emp_id  = e.emp_id
         	   		       AND  da.a_date  = dw.a_date
    WHERE	   da.emp_id  IS NULL
    ORDER BY   emp_id, missing_date	-- or whatever you want
    ;