1 2 3 Previous Next 32 Replies Latest reply on Jul 1, 2020 5:57 PM by Frank Kulash

    Creating virtual date range and exluding holidays

    BeefStu

      I found some code on the internet that generates virtual dates and fit it to my table layout and works fine.

       

      https://searchoracle.techtarget.com/answer/Creating-a-virtual-SQL-table-for-date-value

       

       

      After the range of dates have been created I want to exclude all dates in the holidays table if they fall into the range of dates, which haves been generated.

       

      I tried using the MINUS function,  I think that was the right idea, but was unsuccessful.

       

      Can someone please explain how this can be done based on my test case.

       

       

      I know there is much more functionality that needs to be added like employee vacation days 1 for each day or a range,scheduled days off for a week (not always sat, sun for all)… but that is way too overwhelming at this point as I want to start with smaller tasks.

       

      Thanks to all who answer.

       

      ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

       

       

      Create table employees(

      employee_id NUMBER(6),

      first_name VARCHAR2(20),

      last_name VARCHAR2(20),

      card_num VARCHAR2(10)

      );

       

      INSERT into employees(

      employee_id, first_name,

        last_name, card_num)

      VALUES

      (1, 'John', 'Doe', 'AAA1');

       

      INSERT into employees(

      employee_id, first_name,

        last_name, card_num)

      VALUES

      (2, 'Jane', 'Smith', 'BBB2');

       

      INSERT into employees(

      employee_id, first_name,

        last_name, card_num)

      VALUES

      (3, 'Ed', 'Jones', 'CCC3');

       

      CREATE TABLE  emp_attendance        (seq_num NUMBER(10),

        employee_id NUMBER(6),

        start_date DATE,

        end_date DATE,    create_date DATE DEFAULT SYSDATE       );

       

       

      INSERT into emp_attendance  (seq_num, employee_id,       start_date,       end_date)

      VALUES

      (1, 1,

       

      TO_DATE('2020/07/03 15:15:04', 'yyyy/mm/dd hh24:mi:ss'),

       

      TO_DATE('2020/07/03 19:17:34', 'yyyy/mm/dd hh24:mi:ss'));

       

      INSERT into emp_attendance   (seq_num, employee_id,       start_date,       end_date)

      VALUES

      (2,2,

       

      TO_DATE('2020/07/03 08:16:34', 'yyyy/mm/dd hh24:mi:ss'),

       

      TO_DATE('2020/07/03 11:11:45', 'yyyy/mm/dd hh24:mi:ss'));

       

       

      INSERT into emp_attendance   (seq_num, employee_id,       start_date,       end_date)

      VALUES

      (3,2,

       

      TO_DATE('2020/07/03 18:17:04', 'yyyy/mm/dd hh24:mi:ss'),

       

      TO_DATE('2020/07/03 21:18:54', 'yyyy/mm/dd hh24:mi:ss'));

       

      INSERT into emp_attendance   (seq_num, employee_id,       start_date,       end_date)

      VALUES

      (4,3,

      TO_DATE('2020/07/04 08:15:00', 'yyyy/mm/dd hh24:mi:ss'),

       

      TO_DATE('2020/07/04 16:19:04', 'yyyy/mm/dd hh24:mi:ss'));

       

       

      create table holidays(

      holiday_date DATE,

      holiday_name VARCHAR2(20)

      );

       

      INSERT into holidays

      (holiday_date,

      holiday_name)

      VALUES

      (

      TO_DATE('2020/07/04 00:00:00', 'yyyy/mm/dd hh24:mi:ss'),

      'July 4th 2020');

       

      CREATE OR REPLACE TYPE obj_date IS OBJECT (

      date_val DATE

      );

      /

       

      CREATE OR REPLACE TYPE nt_date IS TABLE OF obj_date;

      /

       

      CREATE OR REPLACE FUNCTION generate_dates(

      p_from IN DATE

      ,p_to IN DATE)

      RETURN nt_date PIPELINED

      IS

      -- normalize inputs to be as-of midnight

      v_from DATE := TRUNC(NVL(p_from, SYSDATE));

      v_to DATE := TRUNC(NVL(p_to, SYSDATE));

      BEGIN

      LOOP

         EXIT WHEN v_from > v_to;

         PIPE ROW (obj_date(v_from));

         v_from := v_from + 1; -- next calendar day

      END LOOP;

      RETURN;

      END generate_dates;

      /

       

      -- should be inserted into an absence table.

       

      SELECT e.employee_id, gd.date_val

      FROM employees e

      CROSS JOIN

      TABLE(generate_dates(DATE '2020-07-02', DATE '2020-07-05')) gd

      MINUS

      SELECT e.employee_id, trunc(ea.start_date)

      FROM employees e

      INNER JOIN emp_attendance ea

      ON ea.employee_id = e.employee_id

      WHERE ea.start_date BETWEEN DATE '2020-07-02' AND DATE '2020-07-05' + (1-1/24/60/60)

      ORDER BY 1, 2;

        • 1. Re: Creating virtual date range and exluding holidays
          Frank Kulash

          Hi,

           

          Thanks for posting the sample data. Don't forget to post the exact results you want from that sample data. Explain how you get those results from tat data.

           

          Doing the project in baby steps, testing after each one, is a great plan, but it makes it even more important for you to post the desired results and explanation of what you're trying to do in this step.

           

          BeefStu wrote:

          ...

          After the range of dates have been created I want to exclude all dates in the holidays table if they fall into the range of dates, which haves been generated.

           

          I tried using the MINUS function, I think that was the right idea, but was unsuccessful.

           

          ...

          Is that where you're getting stuck?

          Here's one way to do that using the MINUS operator (it's not a function):

          SELECT    date_val

          FROM      TABLE (generate_dates(DATE '2020-07-02', DATE '2020-07-05'))

              MINUS

          SELECT    holiday_date

          FROM      holidays

              --

          ORDER BY  date_val

          ;

          Output from your sample data:

          DATE_VAL

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

          2020/07/02 00:00:00

          2020/07/03 00:00:00

          2020/07/05 00:00:00

          1 person found this helpful
          • 2. Re: Creating virtual date range and exluding holidays
            Frank Kulash

            Hi,

             

            By the way, in Oracle you can generate a "table" of all the dates from &v_from to &v_to (inclusive) without user-defined types or PL/SQL like this:

            SELECT  &v_from + LEVEL - 1  AS date_val

            FROM    dual

            CONNECT BY  &v_from + LEVEL - 1  <= &v_to

            ;

            I'm not saying a piplelined function is necessarily wrong.  There are advantages and disadvantages to each approach.

            1 person found this helpful
            • 3. Re: Creating virtual date range and exluding holidays
              BeefStu

              Thanks, Until I found that sample on the internet I had no idea about pipeline functions nevertheless the less &v_from to &v_to format you refrenced. I know there is usually more than one way of way to skin cat. Btw, I was fumbling around with a subquery to gather the holidays and trying to use not in clause but your solution is perfect.

               

              Thanks for your patience and expertise

              • 4. Re: Creating virtual date range and exluding holidays
                BeefStu

                ORA-01789: query block has incorrect number of result columns

                 

                With the following modified code. Is this because it's being done in the in the cross JOIN, which needs an employee_id column too?

                 

                I suspect if wanted to display employees first and last name I just cant add those columns to the first SELECT and have to do something special like in the example link. That isn't a priority. It's for academic purposes  as I will eventually want to only store the employee_id and date into an absence table and since I have the employee_id I can look up the name if needed.

                 

                My modified code, with your suggestion that is causing the above error.

                 

                SELECT e.employee_id, gd.date_val

                FROM employees e

                CROSS JOIN

                TABLE(generate_dates(DATE '2020-07-02', DATE '2020-07-05')) gd

                MINUS

                 

                SELECT    holiday_date

                FROM      holidays

                 

                MINUS

                SELECT e.employee_id, trunc(ea.start_date)

                FROM employees e

                INNER JOIN emp_attendance ea

                ON ea.employee_id = e.employee_id

                WHERE ea.start_date BETWEEN DATE '2020-07-02' AND DATE '2020-07-05' + (1-1/24/60/60)

                ORDER BY 1, 2;

                • 5. Re: Creating virtual date range and exluding holidays
                  Frank Kulash

                  Hi,

                  BeefStu wrote:

                   

                  Thanks, Until I found that sample on the internet I had no idea about pipeline functions nevertheless the less &v_from to &v_to format you refrenced. I know there is usually more than one way of way to skin cat. Btw, I was fumbling around with a subquery to gather the holidays and trying to use not in clause  ...

                  NOT IN is another good way to skin this particular cat.  If it's worth mentioning that you tried it, then it's worth posting your code.  It's hard to say what you did wrong without knowing what you did.

                  • 6. Re: Creating virtual date range and exluding holidays
                    Frank Kulash

                    Hi,

                    BeefStu wrote:

                     

                    ORA-01789: query block has incorrect number of result columns

                     

                    With the following modified code. Is this because it's being done in the in the cross JOIN, which needs an employee_id column too?

                     

                    ...

                    Sorry, I'm not sure exactly what you're asking.

                    The ORA-01789 error here is caused by the first MINUS operation.  Remember what set operations (such as MINUS) do: they produce one result set based on two result sets, namely

                    1. the result set produced by the SELECT ... FROM ... before the set operator (the keyword MINUS in this case), and
                    2. the result set produced by the SELECT ... FROM ... after the set operator keyword(s).

                    Those two result sets must have the same number of columns.  In reply #4, the result set before the first MINUS is  produced by

                    SELECT e.employee_id, gd.date_val

                    FROM ...

                    That's two columns.  The result set after the first MINUS is produced by

                    SELECT    holiday_date

                    FROM ...

                    That's only only one column, so you get the error message " query block has incorrect number of result columns" .  Whether or not one (or both) of the queries involves a join, or, if so, if it's a CROSS JOIN, doesn't matter.  The error is caused by having a different number of columns in the two queries.

                     

                    Once again, always post the desired results.  If you don't say where you want to go, how can anyone give you good directions?

                    If you want to get results like this:

                    EMPLOYEE_ID DATE_VAL

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

                              1 2020/07/02 00:00:00

                              1 2020/07/03 00:00:00

                              1 2020/07/05 00:00:00

                              2 2020/07/02 00:00:00

                              2 2020/07/03 00:00:00

                              2 2020/07/05 00:00:00

                              3 2020/07/02 00:00:00

                              3 2020/07/03 00:00:00

                              3 2020/07/05 00:00:00

                    (that is, each employee in the employees table joined to each date in the result set from reply #2), then here's one way to do it:

                    WITH    dates_wanted    AS

                    (

                        SELECT  date_val

                        FROM    TABLE (generate_dates(DATE '2020-07-02', DATE '2020-07-05'))

                    MINUS

                        SELECT  holiday_date

                        FROM    holidays

                    )

                    SELECT      e.employee_id, d.date_val

                    FROM        dates_wanted  d

                    CROSS JOIN  employees     e

                    ORDER BY    e.employee_id, d.date_val  -- or whatever

                    ;

                    Notice how the code is indented, so it's easy to see the two branches of the MINUS operation.  This makes is easy to spot mistakes, like having a different number of columns in those two branches.

                    • 7. Re: Creating virtual date range and exluding holidays
                      BeefStu

                      SELECT * from emp_attendance

                       

                      SEQ_NUM    EMPLOYEE_ID    START_DATE    END_DATE    CREATE_DATE

                      1    1    07032020 15:15:04    07032020 19:17:34    06282020 18:31:38

                      2    2    07032020 08:16:34    07032020 11:11:45    06282020 18:31:38

                      3    2    07032020 18:17:04    07032020 21:18:54    06282020 18:31:38

                      4    3    07042020 08:15:00    07042020 16:19:04    06282020 18:31:38

                       

                      I am generating a list from 0702 - 0705. I was expecting see the following (in blue) with explanation for each employee_id

                       

                      EMPLOYEE_ID    DATE_VAL

                      1    07022020 00:00:00

                      1    07052020 00:00:00

                       

                      I have a row for 0703 so that should not appear. Holiday on 0704 that should not appear

                       

                      2    07022020 00:00:00

                      2    07052020 00:00:00

                       

                      I have 2 rows for 0703 that should not appear. Holiday on 0704 that should not appear

                       

                      3    07022020 00:00:00

                      3    07032020 00:00:00

                      3    07052020 00:00:00

                       

                      I have row for 0704 but since it's a holiday it shouldn't appear.

                      1 person found this helpful
                      • 8. Re: Creating virtual date range and exluding holidays
                        Frank Kulash

                        HI,

                        BeefStu wrote:

                         

                        SELECT * from emp_attendance

                         

                        SEQ_NUM EMPLOYEE_ID START_DATE END_DATE CREATE_DATE

                        1 1 07032020 15:15:04 07032020 19:17:34 06282020 18:31:38

                        2 2 07032020 08:16:34 07032020 11:11:45 06282020 18:31:38

                        3 2 07032020 18:17:04 07032020 21:18:54 06282020 18:31:38

                        4 3 07042020 08:15:00 07042020 16:19:04 06282020 18:31:38

                         

                        I am generating a list from 0702 - 0705. I was expecting see the following (in blue) with explanation for each employee_id

                         

                        EMPLOYEE_ID DATE_VAL

                        1 07022020 00:00:00

                        1 07052020 00:00:00

                         

                        I have a row for 0703 so that should not apear. Holiday on 0704 that should not appear

                        ...

                         

                        What does it mean to "have a row for 0703"?  Does that mean both start_date and end_date must be on July 3?  Does it man that at least one of those dates must be on July 3?  If so, does it matter which one?  What if start_date is 2020/07/02 23:30:00 and end_date is 2020/07/04 00:30:00; would that count as having a row on 0703?

                         

                        In reply #4, you referenced start_date, but not end_date.  That suggests to "have a row for 0703" means start_date is on July 3, but that's only a guess, and guessing isn't a very good way to solve problems.

                        If that guess happens to be correct, here's one way to get the desired results:

                        WITH    dates_wanted    AS

                        (

                            SELECT  date_val

                            FROM    TABLE (generate_dates(DATE '2020-07-02', DATE '2020-07-05'))

                        MINUS

                            SELECT  holiday_date

                            FROM    holidays

                        )

                        SELECT      ea.employee_id, da.date_val

                        FROM        dates_wanted  da      -- a is for "All"

                        CROSS JOIN  employees     ea

                            MINUS

                        SELECT      ep.employee_id, dp.date_val

                        FROM        dates_wanted    dp  -- p is for "Present"

                        INNER JOIN  emp_attendance  ep  ON   ep.start_date  >= dp.date_val

                                                        AND  ep.start_date  <  dp.date_val + 1

                            --

                        ORDER BY    employee_id, date_val

                        ;

                        For the join condition near the end,

                        TRUNC (ep.start_date) = dp.date_val

                        would get the same results, but it might be slower, since that couldn't use an index on start_date.

                        • 9. Re: Creating virtual date range and exluding holidays
                          BeefStu

                          Thanks you nailed it. What was the issue with the previous version. I'm very curious. Once again, I really appreciate your time, effort and expertise.

                          • 10. Re: Creating virtual date range and exluding holidays
                            Frank Kulash

                            Hi,

                            BeefStu wrote:

                             

                            ... What was the issue with the previous version. ...

                            Are you talking about the query in reply #4?

                            Nothing was wrong with it, except for the ORA-01789 problem.  If you fix that, it produces the same results as the query in reply #8.

                             

                            You actually don't need to join emp_attendance to any other table.  The query in reply #8 can be modified like this:

                            WITH    dates_wanted    AS

                            (

                                SELECT  date_val

                                FROM    TABLE (generate_dates(DATE '2020-07-02', DATE '2020-07-05'))

                            MINUS

                                SELECT  holiday_date

                                FROM    holidays

                            )

                            SELECT      ea.employee_id, da.date_val

                            FROM        dates_wanted  da    -- a is for "All"

                            CROSS JOIN  employees     ea

                                MINUS

                            SELECT      employee_id, TRUNC (start_date)

                            FROM        emp_attendance

                            WHERE       start_date  >= DATE '2020-07-02'

                            AND         start_date  <  DATE '2020-07-05' + 1

                                --

                            ORDER BY    employee_id, date_val

                            ;

                            • 11. Re: Creating virtual date range and exluding holidays
                              BeefStu

                              I am going through the code you wrote yesterday and trying to understand and enhance it and have a few questions.

                               

                              I understand how you removed the holidays and created the CTE dates_wanted, which leaves only 07/02, 07/03, 07/05.

                               

                              Next I see the cross JOIN with the employees table, which I suspect, creates a date for every employee

                               

                              Ie employee_id date_val

                                 1 07022020 00:00:00

                                 1 07032020 00:00:00

                                 1 07052020 00:00:00

                                

                                 2 07022020 00:00:00

                                 2 07032020 00:00:00

                                 2 07052020 00:00:00

                                

                                 3 07022020 00:00:00

                                 3 07032020 00:00:00

                                 3 07052020 00:00:00

                               

                              Now I see the SELECT of the combination of rows above.

                               

                              Question, let's say I want to create the following table and filter out rows for an employee_id and date.

                               

                              create table timeoff(

                              seq_num NUMBER,

                              employee_id NUMBER(6),

                              timeoff_date DATE,

                              timeoff_type VARCHAR2(1),

                              constraint timeoff_chk check (timeoff_date=trunc(timeoff_date, 'dd')),

                                constraint timeoff_pk primary key (employee_id, timeoff_date)

                              );

                               

                              create sequence timeoff_seq;

                               

                              -- vacation day

                              INSERT into timeoff

                              ( seq_num,

                                employee_id,

                                timeoff_date,

                                timeoff_type

                              )

                              VALUES

                              (timeoff_seq.NEXTVAL,

                                3,

                                TO_DATE('2020/07/05 00:00:00', 'yyyy/mm/dd hh24:mi:ss'), 'V');

                               

                              I want to do something like this

                               

                              MINUS

                               

                              SELECT employee_id, timeoff_date from timeoff;

                               

                              But I need a where clause with dates since I don't want to SELECT ALL the timeoff records everytime. Would those be MIN(date_val) and MAX (date_val) this is where I'm lost.

                               

                              Secondly, I Dont understand this, what's in date_val. Is it the low and high range of the generated dates? Why the +1

                               

                              ep.start_date  >= dp.date_val                                AND  ep.start_date  <  dp.date_val + 1

                               

                              Sorry for all the questions.

                              • 12. Re: Creating virtual date range and exluding holidays
                                Frank Kulash

                                Hi,

                                BeefStu wrote:

                                ...

                                Question, let's say I want to create the following table and filter out rows for an employee_id and date.

                                 

                                create table timeoff(

                                seq_num NUMBER,

                                employee_id NUMBER(6),

                                timeoff_date DATE,

                                timeoff_type VARCHAR2(1),

                                constraint timeoff_chk check (timeoff_date=trunc(timeoff_date, 'dd')),

                                constraint timeoff_pk primary key (employee_id, timeoff_date)

                                );

                                 

                                create sequence timeoff_seq;

                                 

                                -- vacation day

                                INSERT into timeoff

                                ( seq_num,

                                employee_id,

                                timeoff_date,

                                timeoff_type

                                )

                                VALUES

                                (timeoff_seq.NEXTVAL,

                                3,

                                TO_DATE('2020/07/05 00:00:00', 'yyyy/mm/dd hh24:mi:ss'), 'V');

                                 

                                I want to do something like this

                                 

                                MINUS

                                 

                                SELECT employee_id, timeoff_date from timeoff;

                                 

                                But I need a where clause with dates since I don't want to SELECT ALL the timeoff records everytime. Would those be MIN(date_val) and MAX (date_val) this is where I'm lost.

                                So, you want to avoid reading the whole timeoff table; you only need to read the part that overlaps with dates_wanted.

                                One way to do that is to join timeoff with dates_wanted, as in reply #8.

                                Another way is to use a WHERE clause, like #10.  However, reply #10 was designed to clearly show you the technique; I didn't mean it to be a example of effiicent, low-maintenance Production code.  You don't want to hard-code the parameters (2020-07-02 and 2020-07-05 in this case) two or more times in the same query.

                                One thing you can do is to use variables (bind variables, substitution variables, SYS_CONTEXT, ...) so the same values can be referenced as many times as needed, anywhere in the query.

                                Another approach to to create t date_range "table", that has only one row, and columns for the start_date and end_date of the period if interest.  Joining to a one-row table like this will probably be more efficient than joining to dates_wanted.  You can derive date_range from dates_wanted using MIN (date_val) and MAX (date_val), like you suggested, or you can derive dates_wanted from date_range, like this:

                                WITH    date_range  AS

                                (   -- This is the nly place where these values need to be hard-coded

                                    SELECT  DATE '2020-07-02'  AS start_date

                                    ,       DATE '2020-07-05'  AS end_date

                                    FROM    dual

                                )

                                ,    dates_wanted    AS

                                (

                                    SELECT  g.date_val

                                    FROM    date_range  r1

                                    CROSS APPLY (TABLE (generate_dates(r1.start_date, r1.end_date)))  g

                                MINUS

                                    SELECT  holiday_date

                                    FROM    holidays    h

                                    JOIN    date_range  r2  ON   h.holIday_date   -- if you don't want to read the whole holiday table

                                                                 BETWEEN  r2.start_date

                                                                 AND      r2.end_date

                                )

                                ...

                                Both date_range and dates_wanted are available whenever you need them in the rest of the query.

                                If you want to get the rows from timeoff that are within the range, then you can join timeoff to date_range, just like the code above joins holidyas to date_range.

                                BeefStu wrote:

                                ...
                                Secondly, I Dont understand this, what's in date_val. Is it the low and high range of the generated dates? Why the +1 ...

                                dp.date_val is the value of the date_val column on some particular row of dp.

                                In reply #8, you saw this join:

                                FROM        dates_wanted    dp  -- p is for "Present"

                                INNER JOIN  emp_attendance  ep  ON   ep.start_date  >= dp.date_val

                                                                AND  ep.start_date  <  dp.date_val + 1

                                that means there will be a row in the result set for every combination of rows from dp and ep that meet the join conditions.

                                FROM        dates_wanted    dp  -- p is for "Present"

                                INNER JOIN  emp_attendance  ep  ON   ep.start_date  >= dp.date_val

                                                                AND  ep.start_date  <  dp.date_val + 1

                                For example: what rows in the result set will there be for a row in ep that has start_date=2020/07/03 08:16:34?  To what rows in dp will it be joined?

                                Will it be joined to a row in dp that has date_val=2020/07/02?  No; it fails the sub-condition ep.start_date < dp.date_val + 1.

                                Will it be joined to a row in dp that has date_val=2020/07/03?  Yes, both sub-conditions are true

                                Will it be joined to a row in dp that has date_val=2020/07/05?  No, it fails the sub-condition ep.start_date >=dp.date_val.

                                 

                                The join above gets the same results as

                                INNER JOIN  emp_attendance  ep  ON   ep.start_date  BETWEEN dp.date_val

                                                                                    AND     dp.date_val + (1-1/24/60/60)

                                (as in your original message).  +1 is used for the same reason in both cases.

                                 

                                By the way, if you want to use BETWEEN, you can do it less cryptically:

                                INNER JOIN  emp_attendance  ep  ON   ep.start_date  BETWEEN dp.date_val

                                                                                    AND     dp.date_val + 1 - INTERVAL '1' SECOND

                                 

                                1 person found this helpful
                                • 13. Re: Creating virtual date range and exluding holidays
                                  BeefStu

                                  Thanks for the clarification it was very helpful.

                                   

                                  "If you want to get the rows from timeoff that are within the range, then you can join timeoff to date_range, just like the code above joins holidays to date_range"

                                   

                                  Since holidays are global (apply to every employee) and timeoff records are tied to a particular employee. Don't I need to expand the where clause predicate after the join to the employees table to something like this?

                                   

                                  MINUS

                                   

                                      SELECT  employee_id,

                                              timeoff_date

                                      FROM    timeoff to

                                      JOIN    date_range  r2  ON  to.timeoff_date  

                                   

                                  WHERE   to.employee_id =

                                           ea.employee_id  AND                            BETWEEN  r2.start_date                                 AND      r2.end_date

                                  • 14. Re: Creating virtual date range and exluding holidays
                                    Frank Kulash

                                    Hi,

                                    BeefStu wrote:

                                     

                                    Thanks for the clarification it was very helpful.

                                     

                                    "If you want to get the rows from timeoff that are within the range, then you can join timeoff to date_range, just like the code above joins holidays to date_range"

                                     

                                    Since holidays are global (apply to every employee) and timeoff records are tied to a particular employee. Don't I need to expand the where clause predicate after the join to the employees table to something like this?

                                     

                                    ...

                                    Once again, you haven't said where you're trying to go, so there's no way I could tell you how to get there.  Post the exact results you want from the given data, and an explanation of what you're doing.

                                     

                                    If you wanted to see what rows of timeoff were within a given date range, and you were happy to hard-code the date range, then you all you would need would be:

                                    SELECT  t.*            -- or whatever columns you want

                                    FROM    timeoff     t  -- TO is an Oracle keyword, so it's not a good table alias

                                    WHERE   t.imeoff_date  BETWEEN  DATE '2020-07-02'

                                                           AND      DATE '2020-07-05'

                                    ;

                                    There's no mention of employee_id.

                                    If, instead of hard-coding the dates, you wanted to get them from a "table", then you could say:

                                    SELECT  t.*            -- or whatever columns you want

                                    FROM    timeoff     t  -- TO is an Oracle keyword, so it's not a good table alias

                                    JOIN    date_range  r2  ON

                                            t.timeoff_date  BETWEEN  r2.start_date

                                                            AND      r2.end_date

                                    ;

                                    Again, there's no mention of employee_id.

                                     

                                     

                                     

                                    BeefStu wrote:

                                    ...

                                        SELECT  employee_id,

                                                timeoff_date

                                        FROM    timeoff to

                                        JOIN    date_range  r2  ON  to.timeoff_date 

                                     

                                    WHERE   to.employee_id =

                                             ea.employee_id  AND                            BETWEEN  r2.start_date                                 AND      r2.end_date

                                    Theres no ea table in that query.

                                    Even if there was, why would you try to compare an employee_id to DATEs, such as r2.start_date and r2.end_date?

                                    1 2 3 Previous Next