Home » SQL & PL/SQL » SQL & PL/SQL » Creating time rows that doesn't cross midnight (19c)
Creating time rows that doesn't cross midnight [message #684763] Tue, 17 August 2021 02:29 Go to next message
Unclefool
Messages: 17
Registered: August 2021
Junior Member

have some code below that generates 20 rows (hard coded). Can this be converted into a SQL so it creates rows uptown midnight but DOESN'T cross over to the next day.

For every time row I want to include each employee_id and location_id.

Perhaps using a CTE to modularize the times so it's not intertwined in the rest of the code?

My goal is to populate the access_history table below.

Thanks in advance for your expertise and all who answer.





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


create table access_history(
      seq_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
       employee_id NUMBER(6), 
       card_num varchar2(10),
       location_id number(4),
       access_date date,
       processed NUMBER(1) default 0
    );

Create table employees(
          employee_id NUMBER(6), 
          first_name VARCHAR2(20),
          last_name VARCHAR2(20),
         card_num VARCHAR2(10),
          work_days VARCHAR2(7)
       );

        INSERT INTO employees (
         employee_id,
         first_name, 
         last_name,
         card_num,
         work_days
        )
        WITH names AS   ( 
          SELECT 1, 'John',     'Doe',      'D564311','YYYYYNN' FROM dual UNION ALL
          SELECT 2, 'Justin',     'Case',      'C224311','YYYYYNN' FROM dual UNION ALL
        SELECT 3, 'Mike',     'Jones',      'J288811','YYYYYNN' FROM dual UNION ALL
         SELECT 4, 'Jane',     'Smith',      'S564661','YYYYYNN' FROM dual 
       ) SELECT * FROM names; 

CREATE TABLE locations AS
    SELECT level AS location_id,
       'Door ' || level AS location_name,

    CASE round(dbms_random.value(1,3)) 
            WHEN 1 THEN 'A' 
            WHEN 2 THEN 'T' 
            WHEN 3 THEN 'T' 
         END AS location_type

    FROM   dual
    CONNECT BY level <= 5;

select timestamp '2016-01-10 14:00:00' + numtodsinterval(rownum*10,'MINUTE')
    from dual
    connect by level <= 20;


[Updated on: Tue, 17 August 2021 03:22]

Report message to a moderator

Re: Creating time rows that doesn't cross midnight [message #684764 is a reply to message #684763] Tue, 17 August 2021 04:53 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3091
Registered: January 2010
Location: Connecticut, USA
Senior Member
Requirements aren't clear. Are you looking to create variable number of rows starting with sysdate + 10 minutes and increasing by 10 minutes till midnight? If so:

select sysdate + numtodsinterval( level * 10,'MINUTE')
    from dual
    connect by sysdate + numtodsinterval( level * 10,'MINUTE') < trunc(sysdate) + 1
/

SYSDATE+NUMTODSIN
-----------------
08172021 06:01:41
08172021 06:11:41
08172021 06:21:41
08172021 06:31:41
08172021 06:41:41
08172021 06:51:41
08172021 07:01:41
08172021 07:11:41
08172021 07:21:41
08172021 07:31:41
08172021 07:41:41
08172021 07:51:41
08172021 08:01:41
08172021 08:11:41
08172021 08:21:41
08172021 08:31:41
08172021 08:41:41
08172021 08:51:41
08172021 09:01:41
08172021 09:11:41
08172021 09:21:41
08172021 09:31:41
08172021 09:41:41
08172021 09:51:41
08172021 10:01:41
08172021 10:11:41
08172021 10:21:41
08172021 10:31:41
08172021 10:41:41
08172021 10:51:41
08172021 11:01:41
08172021 11:11:41
08172021 11:21:41
08172021 11:31:41
08172021 11:41:41
08172021 11:51:41
08172021 12:01:41
08172021 12:11:41
08172021 12:21:41
08172021 12:31:41
08172021 12:41:41
08172021 12:51:41
08172021 13:01:41
08172021 13:11:41
08172021 13:21:41
08172021 13:31:41
08172021 13:41:41
08172021 13:51:41
08172021 14:01:41
08172021 14:11:41
08172021 14:21:41
08172021 14:31:41
08172021 14:41:41
08172021 14:51:41
08172021 15:01:41
08172021 15:11:41
08172021 15:21:41
08172021 15:31:41
08172021 15:41:41
08172021 15:51:41
08172021 16:01:41
08172021 16:11:41
08172021 16:21:41
08172021 16:31:41
08172021 16:41:41
08172021 16:51:41
08172021 17:01:41
08172021 17:11:41
08172021 17:21:41
08172021 17:31:41
08172021 17:41:41
08172021 17:51:41
08172021 18:01:41
08172021 18:11:41
08172021 18:21:41
08172021 18:31:41
08172021 18:41:41
08172021 18:51:41
08172021 19:01:41
08172021 19:11:41
08172021 19:21:41
08172021 19:31:41
08172021 19:41:41
08172021 19:51:41
08172021 20:01:41
08172021 20:11:41
08172021 20:21:41
08172021 20:31:41
08172021 20:41:41
08172021 20:51:41
08172021 21:01:41
08172021 21:11:41
08172021 21:21:41
08172021 21:31:41
08172021 21:41:41
08172021 21:51:41
08172021 22:01:41
08172021 22:11:41
08172021 22:21:41
08172021 22:31:41
08172021 22:41:41
08172021 22:51:41
08172021 23:01:41
08172021 23:11:41
08172021 23:21:41
08172021 23:31:41
08172021 23:41:41
08172021 23:51:41

108 rows selected.

SQL>
SY.
Creating time rows that doesn't cross midnight [message #684768 is a reply to message #684764] Tue, 17 August 2021 09:13 Go to previous messageGo to next message
Unclefool
Messages: 17
Registered: August 2021
Junior Member
@SY what I have so far.

WITH inputs ( value ) AS (
SELECT TO_DATE('2021/08/20 18:30:25', 'YYYY/MM/DD HH24:MI:SS')
FROM DUAL
),
date_rows ( start_date, end_date ) AS (
SELECT value,
TRUNC(value) + INTERVAL '1' DAY
FROM inputs
UNION ALL
SELECT start_date + INTERVAL '10' MINUTE,
end_date
FROM date_rows
WHERE start_date + INTERVAL '10' MINUTE < end_date
)
SELECT start_date
FROM date_rows;

For each date I want to cross apply the employees and locations table so I can populate the access_history table. I believe that is what I need to do as there is nothing to join on.


[Updated on: Tue, 17 August 2021 14:19]

Report message to a moderator

Re: Creating time rows that doesn't cross midnight [message #684771 is a reply to message #684768] Tue, 17 August 2021 17:11 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3091
Registered: January 2010
Location: Connecticut, USA
Senior Member
If INPUTS will have no more than 1 row:

WITH INPUTS(DT)
  AS (
      SELECT  TO_DATE('2021/08/20 18:30:25','YYYY/MM/DD HH24:MI:SS')
        FROM  DUAL
     )
SELECT  DT + (LEVEL - 1) / 144
  FROM  INPUTS
  CONNECT BY DT + (LEVEL - 1) / 144 < TRUNC(DT) + 1
/

DT+(LEVEL-1)/144
-----------------
08202021 18:30:25
08202021 18:40:25
08202021 18:50:25
08202021 19:00:25
08202021 19:10:25
08202021 19:20:25
08202021 19:30:25
08202021 19:40:25
08202021 19:50:25
08202021 20:00:25
08202021 20:10:25
08202021 20:20:25
08202021 20:30:25
08202021 20:40:25
08202021 20:50:25
08202021 21:00:25
08202021 21:10:25
08202021 21:20:25
08202021 21:30:25
08202021 21:40:25
08202021 21:50:25
08202021 22:00:25
08202021 22:10:25
08202021 22:20:25
08202021 22:30:25
08202021 22:40:25
08202021 22:50:25
08202021 23:00:25
08202021 23:10:25
08202021 23:20:25
08202021 23:30:25
08202021 23:40:25
08202021 23:50:25

33 rows selected.

SQL>
If INPUTS can have multiple rows you will need a second column to uniquely identify each row:

WITH INPUTS(ID,DT)
  AS (
       SELECT  1,
               TO_DATE('2021/08/20 18:30:25','YYYY/MM/DD HH24:MI:SS')
         FROM  DUAL
      UNION ALL
       SELECT  2,
               TO_DATE('2021/08/20 23:19:01','YYYY/MM/DD HH24:MI:SS')
         FROM  DUAL
     )
SELECT  ID,
        DT + (LEVEL - 1) / 144
  FROM  INPUTS
  CONNECT BY ID = PRIOR ID
         AND PRIOR SYS_GUID() IS NOT NULL
         AND DT + (LEVEL - 1) / 144 < TRUNC(DT) + 1
/

        ID DT+(LEVEL-1)/144
---------- -----------------
         1 08202021 18:30:25
         1 08202021 18:40:25
         1 08202021 18:50:25
         1 08202021 19:00:25
         1 08202021 19:10:25
         1 08202021 19:20:25
         1 08202021 19:30:25
         1 08202021 19:40:25
         1 08202021 19:50:25
         1 08202021 20:00:25
         1 08202021 20:10:25
         1 08202021 20:20:25
         1 08202021 20:30:25
         1 08202021 20:40:25
         1 08202021 20:50:25
         1 08202021 21:00:25
         1 08202021 21:10:25
         1 08202021 21:20:25
         1 08202021 21:30:25
         1 08202021 21:40:25
         1 08202021 21:50:25
         1 08202021 22:00:25
         1 08202021 22:10:25
         1 08202021 22:20:25
         1 08202021 22:30:25
         1 08202021 22:40:25
         1 08202021 22:50:25
         1 08202021 23:00:25
         1 08202021 23:10:25
         1 08202021 23:20:25
         1 08202021 23:30:25
         1 08202021 23:40:25
         1 08202021 23:50:25
         2 08202021 23:19:01
         2 08202021 23:29:01
         2 08202021 23:39:01
         2 08202021 23:49:01
         2 08202021 23:59:01

38 rows selected.

SQL>
SY.
Re: Creating time rows that doesn't cross midnight [message #684775 is a reply to message #684771] Wed, 18 August 2021 08:36 Go to previous message
Unclefool
Messages: 17
Registered: August 2021
Junior Member
@SY thanks for your expertise and responding my final solution if you're interested


create or replace procedure xxx (
  i_start_date in date,
  i_interval         IN PLS_INTEGER DEFAULT 10
) as
BEGIN

INSERT INTO access_history (employee_id, card_num, location_id, access_date)
WITH date_rows ( start_date, end_date ) AS (
 SELECT i_start_date,
        TRUNC(i_start_date) + 1
 FROM   DUAL
UNION ALL
 SELECT start_date + 
NUMTODSINTERVAL(i_interval, 'MINUTE'),
        end_date
 FROM   date_rows
 WHERE  start_date +
NUMTODSINTERVAL(i_interval, 'MINUTE') < end_date
)
SELECT     e.employee_id
,        e.card_num
,       l.location_id
,       d.start_date
FROM       employees e
CROSS JOIN locations l
CROSS JOIN date_rows d;
END;
/

Previous Topic: How to execute dynamically the permission grant using AWS RDS custom procedure (merged)
Next Topic: Oracle procedure passing NULL in DATE parameters
Goto Forum:
  


Current Time: Mon Oct 25 04:38:32 CDT 2021