Forum Stats

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

Discussions

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.

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.

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.

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

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

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

  • User_8KQYD
    User_8KQYD Member Posts: 9 Red Ribbon

    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)

  • User_8KQYD
    User_8KQYD Member Posts: 9 Red Ribbon

    our weekends are friday, saturday

    Added 1 more employee personID (1 and 2)

    Desired is for all employees.

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