Help with query

Database

Help with query

Hi,

I need some help please. I need to correctly report on the time a case was detected and worked. I have two tables containing data as follows (data has been simplified):

TABLE1 

ID    Time_Detected

123 10:00

123 10:05

123 10:10

123 10:15

123 10:20

123 10:25

123 10:30

TABLE2 

ID    Time_Worked

123 10:02

123 10:11

123 10:26

I need the report to calculate the earliest detected time for each worked i.e.

ID    Detected Worked

123 10:00    10:02

123 10:05    10:11

123 10:15    10:26

3 REPLIES
Junior Contributor

Re: Help with query

You need the earliest date after a previous worked. This should return the expected result using a single STAT-step:

SELECT ID, t AS Detected,
MIN(CASE WHEN flag = 1 THEN t END) -- get the following Time_Worked
OVER (PARTITION BY ID
ORDER BY t DESC ROWS UNBOUNDED PRECEDING) AS Worked
FROM
(
SELECT ID, Time_Detected AS t, 0 AS flag
FROM TABLE1
UNION ALL
SELECT ID, Time_Worked, 1
FROM TABLE2
) AS dt
QUALIFY
COALESCE(MIN(flag) -- only return the first row after a row from Time_Worked, i.e. lowest Time_Detected
OVER (PARTITION BY ID
ORDER BY t DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING),1) = 1
AND Worked > Detected
Teradata Employee

Re: Help with query

As well you can solve it on this way

create multiset volatile table TABLE1 
(
id integer,
Time_Detected time(0) format 'HH:MI'
)primary index( id )
on commit preserve rows;

insert into TABLE1 values(123 , cast('10:00' as TIME(0) format 'HH:MI'));
insert into TABLE1 values(123 , cast('10:05' as TIME(0) format 'HH:MI'));
insert into TABLE1 values(123 , cast('10:10' as TIME(0) format 'HH:MI'));
insert into TABLE1 values(123 , cast('10:15' as TIME(0) format 'HH:MI'));
insert into TABLE1 values(123 , cast('10:20' as TIME(0) format 'HH:MI'));
insert into TABLE1 values(123 , cast('10:25' as TIME(0) format 'HH:MI'));
insert into TABLE1 values(123 , cast('10:30' as TIME(0) format 'HH:MI'));

create multiset volatile table TABLE2
(
id integer,
Time_Worked time(0) format 'HH:MI'
)primary index( id )
on commit preserve rows;

insert into TABLE2 values(123 , cast('10:02' as TIME(0) format 'HH:MI'));
insert into TABLE2 values(123 , cast('10:11' as TIME(0) format 'HH:MI'));
insert into TABLE2 values(123 , cast('10:26' as TIME(0) format 'HH:MI'));

select
T2.id,
max(T1.Time_Detected) as Time_Detected,
T2.Time_Worked
from TABLE1 T1
inner join TABLE2 T2
on T1.id = T2.id
and T1.Time_Detected < T2.Time_Worked
group by 1, 3
order by 1, 2

Re: Help with query

Many thanks Dieter, your solution works really well, you have been a great help!