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 |
|
Unclefool
Messages: 85 Registered: August 2021
|
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 |
Solomon Yakobson
Messages: 3275 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 |
|
Unclefool
Messages: 85 Registered: August 2021
|
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 |
Solomon Yakobson
Messages: 3275 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 |
|
Unclefool
Messages: 85 Registered: August 2021
|
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;
/
|
|
|
Goto Forum:
Current Time: Sun Jun 30 01:36:31 CDT 2024
|