Forum Stats

  • 3,827,745 Users
  • 2,260,818 Discussions
  • 7,897,364 Comments

Discussions

Categories

Request for sql query to exclude weekends, leave for attendances

User_8KQYD
User_8KQYD Member Posts: 9 Red Ribbon
edited Nov 14, 2021 1:14PM in SQL & PL/SQL 
CREATE TABLE APPS.XX_ATTENDANCE_MAIN
(
 PERSONID   NUMBER,
 TRANS_DATE  DATE,
 IN_TIME    VARCHAR2(240),
 OUT_TIME   VARCHAR2(240)
)

INSERT INTO XX_ATTENDANCE_MAIN
VALUES( 1,TO_DATE('01-11-2021','DD-MM-YYYY'),'07:30','14:30');

INSERT INTO XX_ATTENDANCE_MAIN
VALUES( 1,TO_DATE('02-11-2021','DD-MM-YYYY'),'07:30','14:30');

INSERT INTO XX_ATTENDANCE_MAIN
VALUES( 1,TO_DATE('03-11-2021','DD-MM-YYYY'),'07:30','14:30');

INSERT INTO XX_ATTENDANCE_MAIN
VALUES( 1,TO_DATE('04-11-2021','DD-MM-YYYY'),'07:30','14:30');

INSERT INTO XX_ATTENDANCE_MAIN
VALUES( 2,TO_DATE('01-11-2021','DD-MM-YYYY'),'07:30','14:30');


INSERT INTO XX_ATTENDANCE_MAIN
VALUES( 2,TO_DATE('02-11-2021','DD-MM-YYYY'),'07:30','14:30');


INSERT INTO XX_ATTENDANCE_MAIN
VALUES( 2,TO_DATE('03-11-2021','DD-MM-YYYY'),'07:30','14:30');

CREATE TABLE XX_ABSENCE
(PERSONID NUMBER,
DATE_START DATE,
DATE_END DATE);

INSERT INTO XX_ABSENCE
VALUES(1, TO_DATE('07-11-2021','DD-MM-YYYY'),TO_DATE('09-11-2021','DD-MM-YYYY'));
INSERT INTO XX_ABSENCE
VALUES(2, TO_DATE('07-11-2021','DD-MM-YYYY'),TO_DATE('09-11-2021','DD-MM-YYYY'));


CREATE TABLE XX_HOLIDAYS
(TYPE VARCHAR2(240),
DATE_START DATE,
DATE_END DATE);

INSERT INTO XX_HOLIDAYS
VALUES ('PUBLIC HOLIDAYS',TO_DATE('14-11-2021','DD-MM-YYYY'),TO_DATE('16-11-2021','DD-MM-YYYY'));

COMMIT;


ATTENDANCE AVAILABE:

FOR personID 1 01-NOV-2021 TO 04-NOV-2021

for personID 2 01-NOV-2021 TO 03-NOV-2021

Leave Taken both : 07-NOV-2021 TO 09-NOV-2021

PUBLIC HOLIDAYS for all : 14-NOV-2021 TO 16-NOV-2021

Weekends : firday, saturday


Need to find employee didnt attend to office wihtout approval (ex: 10 nov 2021, 11 nov 2021 total 2 days he is absent)

1. excluding weekends friday, saturday, 

2. excluding leaves(Absences)

3. excluding public holidays


Query output required as below:


select * from XX_ATTENDANCE_MAIN

where trans_date between TO_DATE('01-11-2021','DD-MM-YYYY') and TO_DATE('16-11-2021','DD-MM-YYYY');


PERSONID  TRANS_DATE IN_TIME OUT_TIME  ABSENT

1     11/1/2021 07:30   14:30

1     11/2/2021 07:30   14:30

1     11/3/2021 07:30   14:30

1     11/4/2021 07:30   14:30

1      11/10/2021  NULL     NULL   Y

1      11/11/2021  NULL     NULL   Y

2     11/1/2021 07:30   14:30

2     11/2/2021 07:30   14:30

2     11/3/2021 07:30   14:30

2     11/4/2021 NULL NULL Y

2      11/10/2021  NULL     NULL   Y

2     11/11/2021  NULL     NULL   Y



I tried using unionAll and minus dates, it becomes very long query .

Kindly suggest.


Thanks,

Afzal.


Update:

Our Database is 11g 11.2.0.4.0

Friday, Saturday are our weekends.

There is another main table of employees which holds all employee information ex: per_all_people_f.

Tagged:
· Share on TwitterShare on Facebook

Best Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,488 Red Diamond
    Answer ✓

    @User_8KQYD our database is 11g 11.2.0.4.0

    Always post your version especially when it is outdated. Anyway:

    SQL> SELECT  VERSION
  2    FROM  V$INSTANCE
  3  /

VERSION
-----------------
11.2.0.4.0


DEFINE REPORT_FROM_DATE = "2021-11-01"
DEFINE REPORT_TO_DATE = "2021-11-16"
WITH DRIVER AS (
                 SELECT  DATE '&REPORT_FROM_DATE' + LEVEL - 1 DT
                   FROM  DUAL
                   WHERE DATE '&REPORT_FROM_DATE' + LEVEL - 1 - TRUNC(DATE '&REPORT_FROM_DATE' + LEVEL - 1,'IW') NOT IN (4,5)
                   CONNECT BY LEVEL <= DATE '&REPORT_TO_DATE' - DATE '&REPORT_FROM_DATE' + 1
                MINUS
                 SELECT  DATE_START + LEVEL - 1 DT
                   FROM  XX_HOLIDAYS
                   CONNECT BY PRIOR SYS_GUID() IS NOT NULL
                          AND LEVEL <= DATE_END - DATE_START + 1
               )
 SELECT  AT.PERSONID,
         D.DT TRANS_DATE,
         AT.IN_TIME,
         AT.OUT_TIME,
         NVL2(AT.TRANS_DATE,NULL,'Y') ABSENT
   FROM      DRIVER D
         LEFT JOIN
             XX_ATTENDANCE_MAIN AT
           PARTITION BY(AT.PERSONID)
           ON AT.TRANS_DATE = D.DT
   WHERE NOT EXISTS (
                     SELECT  1
                       FROM  XX_ABSENCE AB
                       WHERE AB.PERSONID = AT.PERSONID
                         AND D.DT BETWEEN AB.DATE_START AND AB.DATE_END
                     )
  ORDER BY PERSONID,
           TRANS_DATE
/
  PERSONID TRANS_DAT IN_TIME OUT_TIME ABSENT
---------- --------- ------- -------- ------
         1 01-NOV-21 07:30   14:30
         1 02-NOV-21 07:30   14:30
         1 03-NOV-21 07:30   14:30
         1 04-NOV-21 07:30   14:30
         1 10-NOV-21                  Y
         1 11-NOV-21                  Y
         2 01-NOV-21 07:30   14:30
         2 02-NOV-21 07:30   14:30
         2 03-NOV-21 07:30   14:30
         2 04-NOV-21                  Y
         2 10-NOV-21                  Y
         2 11-NOV-21                  Y


12 rows selected.


SQL>

    SY.

    · Share on TwitterShare on Facebook
  • User_8KQYD
    User_8KQYD Member Posts: 9 Red Ribbon
    Answer ✓
    https://community.oracle.com/tech/developers/discussion/comment/16815612#Comment_16815612
    https://community.oracle.com/tech/developers/discussion/comment/16815612#Comment_16815612

    Thanks Solomon a lot.

    · Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,072 Red Diamond
    edited Nov 14, 2021 11:58AM

    Hi, @User_8KQYD

    Thanks for posting the CREATE TABLE and INSERT statements.

    Will you always want output for only one person? If not, then you should include at least sample data an d desired results for more than one person.

    PERSONID  TRANS_DATE IN_TIME OUT_TIME  ABSENT

    1    11/1/2021 07:30  14:30

    1    11/2/2021 07:30  14:30

    1    11/3/2021 07:30  14:30

    1    11/4/2021 07:30  14:30

    1      11/10/2021  NULL     NULL   Y

    1      11/11/2021  NULL     NULL   Y

    Why don't you want any output for 11/5/2021? It's a Friday, and not a holiday.

    [EDIT:] Same question for Friday, 11/12/2021.

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,072 Red Diamond

    Hi,

    CREATE TABLE APPS.XX_ATTENDANCE_MAIN
(
 PERSONID   NUMBER,
 TRANS_DATE  DATE,
 IN_TIME    VARCHAR2(240),
 OUT_TIME   VARCHAR2(240)
)

INSERT INTO XX_ATTENDANCE_MAIN
VALUES( 1,TO_DATE('01-11-2021','DD-MM-YYYY'),'07:30','14:30');
...

    Why are in_time and out_time defined as VARCHAR2 (240)? Why not VARCHAR2 (5)?

    Do you want the output to cover a given range of dates (e.g. Nov. 1-16, 2021) to be supplied at run time?

    What output would you want if a person didn't work at all in that range? How do you even know what persons exist, if they don't have any data in xx_attendance_main or xx_absence? Do you have another table (with, say, one row per person) that you haven't shown?

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,488 Red Diamond
    edited Nov 14, 2021 12:08PM 
    DEFINE REPORT_FROM_DATE = "2021-11-01"
DEFINE REPORT_TO_DATE = "2021-11-16"
WITH DRIVER AS (
                 SELECT  DATE '&REPORT_FROM_DATE' + LEVEL - 1 DT
                   FROM  DUAL
                   WHERE DATE '&REPORT_FROM_DATE' + LEVEL - 1 - TRUNC(DATE '&REPORT_FROM_DATE' + LEVEL - 1,'IW') NOT IN (4,5) -- exclude Fridays and Saturdays
                   CONNECT BY LEVEL <= DATE '&REPORT_TO_DATE' - DATE '&REPORT_FROM_DATE' + 1
                MINUS
                 SELECT  DT
                   FROM  XX_HOLIDAYS,
                         LATERAL(
                                 SELECT  DATE_START + LEVEL - 1 DT
                                   FROM  DUAL
                                   CONNECT BY LEVEL <= DATE_END - DATE_START + 1
                                )
               )
 SELECT  A.PERSONID,
         D.DT MISSED_DAY
   FROM      DRIVER D
         LEFT JOIN
             XX_ATTENDANCE_MAIN A
           PARTITION BY(PERSONID)
           ON A.TRANS_DATE = D.DT
   WHERE A.TRANS_DATE IS NULL
MINUS
 SELECT  PERSONID,
         DT
   FROM  XX_ABSENCE,
         LATERAL(
                 SELECT  DATE_START + LEVEL - 1 DT
                   FROM  DUAL
                   CONNECT BY LEVEL <= DATE_END - DATE_START + 1
                )
/

  PERSONID MISSED_DAY
---------- ----------
         1 10-NOV-21
         1 11-NOV-21

SQL>

    SY.

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,488 Red Diamond

    Ah, I missed you want both attended and absent days:

    WITH DRIVER AS (
                 SELECT  DATE '&REPORT_FROM_DATE' + LEVEL - 1 DT
                   FROM  DUAL
                   WHERE DATE '&REPORT_FROM_DATE' + LEVEL - 1 - TRUNC(DATE '&REPORT_FROM_DATE' + LEVEL - 1,'IW') NOT IN (4,5)
                   CONNECT BY LEVEL <= DATE '&REPORT_TO_DATE' - DATE '&REPORT_FROM_DATE' + 1
                MINUS
                 SELECT  DT
                   FROM  XX_HOLIDAYS,
                         LATERAL(
                                 SELECT  DATE_START + LEVEL - 1 DT
                                   FROM  DUAL
                                   CONNECT BY LEVEL <= DATE_END - DATE_START + 1
                                )
               )
 SELECT  AT.PERSONID,
         D.DT TRANS_DATE,
         AT.IN_TIME,
         AT.OUT_TIME,
         NVL2(AT.TRANS_DATE,NULL,'Y') ABSENT
   FROM      DRIVER D
         LEFT JOIN
             XX_ATTENDANCE_MAIN AT
           PARTITION BY(AT.PERSONID)
           ON AT.TRANS_DATE = D.DT
   WHERE NOT EXISTS (
                     SELECT  1
                       FROM  XX_ABSENCE AB
                       WHERE AB.PERSONID = AT.PERSONID
                         AND D.DT BETWEEN AB.DATE_START AND AB.DATE_END
                     )
/
  PERSONID TRANS_DAT IN_TIME OUT_TIME ABSENT
---------- --------- ------- -------- ------
         1 01-NOV-21 07:30   14:30
         1 02-NOV-21 07:30   14:30
         1 03-NOV-21 07:30   14:30
         1 04-NOV-21 07:30   14:30
         1 10-NOV-21                  Y
         1 11-NOV-21                  Y

6 rows selected.

SQL>

    SY.

    · Share on TwitterShare on Facebook
  • User_8KQYD
    User_8KQYD Member Posts: 9 Red Ribbon
    https://community.oracle.com/tech/developers/discussion/comment/16815607#Comment_16815607

    Thanks for the reply.

    Error at line 11

    ORA-00907: missing right parenthesis

    our database is 11g 11.2.0.4.0 (edited the question)

    · Share on TwitterShare on Facebook
  • User_8KQYD
    User_8KQYD Member Posts: 9 Red Ribbon
    https://community.oracle.com/tech/developers/discussion/comment/16815602#Comment_16815602

    our weekends are friday, saturday

    Added 1 more employee personID (1 and 2)

    Desired is for all employees.

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,488 Red Diamond
    Answer ✓

    @User_8KQYD our database is 11g 11.2.0.4.0

    Always post your version especially when it is outdated. Anyway:

    SQL> SELECT  VERSION
  2    FROM  V$INSTANCE
  3  /

VERSION
-----------------
11.2.0.4.0


DEFINE REPORT_FROM_DATE = "2021-11-01"
DEFINE REPORT_TO_DATE = "2021-11-16"
WITH DRIVER AS (
                 SELECT  DATE '&REPORT_FROM_DATE' + LEVEL - 1 DT
                   FROM  DUAL
                   WHERE DATE '&REPORT_FROM_DATE' + LEVEL - 1 - TRUNC(DATE '&REPORT_FROM_DATE' + LEVEL - 1,'IW') NOT IN (4,5)
                   CONNECT BY LEVEL <= DATE '&REPORT_TO_DATE' - DATE '&REPORT_FROM_DATE' + 1
                MINUS
                 SELECT  DATE_START + LEVEL - 1 DT
                   FROM  XX_HOLIDAYS
                   CONNECT BY PRIOR SYS_GUID() IS NOT NULL
                          AND LEVEL <= DATE_END - DATE_START + 1
               )
 SELECT  AT.PERSONID,
         D.DT TRANS_DATE,
         AT.IN_TIME,
         AT.OUT_TIME,
         NVL2(AT.TRANS_DATE,NULL,'Y') ABSENT
   FROM      DRIVER D
         LEFT JOIN
             XX_ATTENDANCE_MAIN AT
           PARTITION BY(AT.PERSONID)
           ON AT.TRANS_DATE = D.DT
   WHERE NOT EXISTS (
                     SELECT  1
                       FROM  XX_ABSENCE AB
                       WHERE AB.PERSONID = AT.PERSONID
                         AND D.DT BETWEEN AB.DATE_START AND AB.DATE_END
                     )
  ORDER BY PERSONID,
           TRANS_DATE
/
  PERSONID TRANS_DAT IN_TIME OUT_TIME ABSENT
---------- --------- ------- -------- ------
         1 01-NOV-21 07:30   14:30
         1 02-NOV-21 07:30   14:30
         1 03-NOV-21 07:30   14:30
         1 04-NOV-21 07:30   14:30
         1 10-NOV-21                  Y
         1 11-NOV-21                  Y
         2 01-NOV-21 07:30   14:30
         2 02-NOV-21 07:30   14:30
         2 03-NOV-21 07:30   14:30
         2 04-NOV-21                  Y
         2 10-NOV-21                  Y
         2 11-NOV-21                  Y


12 rows selected.


SQL>

    SY.

    · Share on TwitterShare on Facebook
  • User_8KQYD
    User_8KQYD Member Posts: 9 Red Ribbon
    Answer ✓
    https://community.oracle.com/tech/developers/discussion/comment/16815612#Comment_16815612
    https://community.oracle.com/tech/developers/discussion/comment/16815612#Comment_16815612

    Thanks Solomon a lot.

    · Share on TwitterShare on Facebook