Calculate number of occurances between two time stamps

Database
Enthusiast

Calculate number of occurances between two time stamps

Hi All , 

 

SyntaxEditor Code Snippet

Hello , 

I'm trying to get the number of times a client called between two dates with a window funtion but i think i'm doing something wrong , i'm just a beginner in teradata , any help would be very appereciated , thank you .
please see the code in red for the portion of code i'm havaing issue with .



SEL
DISTINCT ORD.ORDER_NO/*,TRANSACTION_NO,ORDER_STATUS_ID,ORDER_TYPE_ID*/ ,CDM.SBM_MASTER_CUST_ID,MIN (ORD.ISSUE_DTM) over (partition by ORD.ORDER_NO order by ORD.ISSUE_DTM) AS MIN_ORDERDATE ,MIN (IVR.CALL_START_DTM) over (partition by IVR.SBM_MASTER_CUST_ID order by IVR.CALL_START_DTM) as MIN_IVRDATE ,( MIN_IVRDATE - MIN_ORDERDATE) DAY(4) /*TO HOUR*/ as DAYS_INTERVAL ,COUNT (IVR.CALL_START_DTM) over (partition by IVR.SBM_MASTER_CUST_ID order by IVR.CALL_START_DTM) ROWS BETWEEN MIN_IVRDATE AND MIN_IVRDATE + interval '10' DAY ) AS DAYS_INTERVALs FROM BI_SEMANTIC_BASE.VC_SBM_RDM_ORDER_ITEM_HIST ORD LEFT JOIN BI_SEMANTIC_BASE.VC_SBM_RDM_LINK_CDM_HIST CDM ON ORD.ORDER_NO = CDM.ORDER_NO LEFT JOIN BI_SEMANTIC_BASE.VC_CALLS_IVR IVR ON CDM.SBM_MASTER_CUST_ID = IVR.SBM_MASTER_CUST_ID and IVR.CALL_START_DTM >= ORD.ISSUE_DTM

 


Accepted Solutions
Senior Supporter

Re: Calculate number of occurances between two time stamps

It is always so much easier to support questions like this if you would provide some CODE to set up the test data which would allow to easily run some queries as @dnoeth also stated.

If I translate your lists into some DDLs and DML - which I also don't like as it is waisting my time -  would come up with

create multiset table VC_SBM_RDM_ORDER_ITEM_HIST
( ORDER_NO int,
  ISSUE_DTM date
  ) primary index (order_no)
  ;
  
insert into VC_SBM_RDM_ORDER_ITEM_HIST values ( 1, '2017-09-01');
insert into VC_SBM_RDM_ORDER_ITEM_HIST values ( 1, '2017-09-01');
insert into VC_SBM_RDM_ORDER_ITEM_HIST values ( 2, '2017-09-02');
insert into VC_SBM_RDM_ORDER_ITEM_HIST values ( 2, '2017-09-02');

create table VC_SBM_RDM_LINK_CDM_HIST
(
 ORDER_NO int,
 smb_key char(3)
) primary index (order_no);

insert into VC_SBM_RDM_LINK_CDM_HIST values (1, 'zzz');
insert into VC_SBM_RDM_LINK_CDM_HIST values (2, 'yyy');


create multiset table VC_CALLS_IVR
(
smb_key char(3),
call_date date
) primary index (smb_key);


insert into VC_CALLS_IVR
select smb_key,
       c1.calendar_date 
from  VC_SBM_RDM_LINK_CDM_HIST 
cross join 
sys_calendar.calendar c1
where c1.calendar_date between '2017-09-01' and '2017-09-20'
and random(0,1) = 1
union all
select smb_key,
       c1.calendar_date 
from  VC_SBM_RDM_LINK_CDM_HIST 
cross join 
sys_calendar.calendar c1
where c1.calendar_date between '2017-09-01' and '2017-09-20'
and random(0,1) = 1
union all
select smb_key,
       c1.calendar_date 
from  VC_SBM_RDM_LINK_CDM_HIST 
cross join 
sys_calendar.calendar c1
where c1.calendar_date between '2017-09-01' and '2017-09-20'
and random(0,1) = 1
union all
select smb_key,
       c1.calendar_date 
from  VC_SBM_RDM_LINK_CDM_HIST 
cross join 
sys_calendar.calendar c1
where c1.calendar_date between '2017-09-01' and '2017-09-20'
and random(0,1) = 1
;

you might run the last bit some time as the result set differes each run.

I got

yyy   	2017-09-01
yyy   	2017-09-02
yyy   	2017-09-03
yyy   	2017-09-06
yyy   	2017-09-08
yyy   	2017-09-09
yyy   	2017-09-10
yyy   	2017-09-11
yyy   	2017-09-15
yyy   	2017-09-17
yyy   	2017-09-18
yyy   	2017-09-19
zzz   	2017-09-02
zzz   	2017-09-03
zzz   	2017-09-06
zzz   	2017-09-07
zzz   	2017-09-09
zzz   	2017-09-09
zzz   	2017-09-10
zzz   	2017-09-10
zzz   	2017-09-11
zzz   	2017-09-14
zzz   	2017-09-15
zzz   	2017-09-16
zzz   	2017-09-17
zzz   	2017-09-17
zzz   	2017-09-19
zzz   	2017-09-20

with this data you could calculate the desired result without olap functions as

select ord.order_no, 
       ord.min_issue_dtm,
       ord.min_issue_dtm + 10,
       l.smb_key,
       sum(case when ord.min_issue_dtm + 10 >= call_date then 1 else 0 end)
from (select order_no, min(issue_dtm) as min_issue_dtm from VC_SBM_RDM_ORDER_ITEM_HIST group by order_no) as ord
left outer join 
VC_SBM_RDM_LINK_CDM_HIST as l
on ord.order_no = l.order_no
left outer join VC_CALLS_IVR c
on l.smb_key = c.smb_key
group by 1,2,3,4
order by 1,2,3,4
;

which gives

1	2017-09-01	2017-09-11	zzz   	9
2	2017-09-02	2017-09-12	yyy   	8

If this is not what you expect change the code and share your sql.

Have fun Ulrich

1 ACCEPTED SOLUTION
9 REPLIES
Enthusiast

Re: Calculate number of occurances between two time stamps

 
Teradata Employee

Re: Calculate number of occurances between two time stamps

There is a manual on SQL Functions which includes Windowing features.  Specifically, ROWS BETWEEN cannot be defined in this way.  See: http://info.teradata.com/HTMLPubs/DB_TTU_16_00/index.html#page/SQL_Reference%2FB035-1145-160K%2Fkdl1...

Junior Contributor

Re: Calculate number of occurances between two time stamps

You're probably looking for RANGE instead of ROWS, which is not implemented in Teradata.

But MIN_IVRDATE is already the result of a Window Aggregate, this is quite confusing.

 

Can you specify exactly what you want to do, including some example data and expected result?

 

 

Enthusiast

Re: Calculate number of occurances between two time stamps

MIN_IVRDATE represent the date a customer called a service line , so i want to use that date as the lower limit and count the number of calls received within 10 days after that date .

In the data example bellow , for order_no = 1 , the MIN_IVRDATE = 02-sep-17 which has to be the min(date2) and >= date1.

and the number of receivred calls 10 days after the MIN_IVRDATE  is = 7 for order_no 1.

 

so i want to find a way to calculate the number of calls received which is 7 in our exemple. thank you .

 

order_nodate1
101-Sep-17
101-Sep-17
101-Sep-17
101-Sep-17
202-Sep-17
202-Sep-17
202-Sep-17
202-Sep-17

 

order_nosbm_key
1zzz
2yyy
  

 

sbm_keydate2
zzz28-Aug-17
zzz29-Aug-17
zzz29-Aug-17
zzz2-Sep-17
zzz2-Sep-17
zzz3-Sep-17
zzz4-Sep-17
zzz04-Sep-17
zzz04-Sep-17
zzz05-Sep-17
yyy05-Aug-17
yyy06-Aug-17
yyy06-Aug-17
yyy06-Aug-17
yyy05-Sep-17
yyy05-Sep-17
yyy05-Sep-17
yyy10-Sep-17
yyy10-Sep-17
yyy11-Sep-17
Junior Contributor

Re: Calculate number of occurances between two time stamps

Rewriting the RANGE as a Scalar Subquery should result in a query like this:

SELECT
  ORD.ORDER_NO
  ,CDM.SBM_MASTER_CUST_ID
  ,Min (ORD.ISSUE_DTM) AS MIN_ORDERDATE
  ,Min (IVR.CALL_START_DTM) AS MIN_IVRDATE
  ,( MIN_IVRDATE - MIN_ORDERDATE) DAY(4) /*TO HOUR*/ AS DAYS_INTERVAL
  ,( SELECT Count (*)  
     FROM BI_SEMANTIC_BASE.VC_CALLS_IVR IVR
     WHERE CDM.SBM_MASTER_CUST_ID = IVR.SBM_MASTER_CUST_ID
      AND IVR.CALL_START_DTM BETWEEN MIN_ORDERDATE AND MIN_ORDERDATE + INTERVAL '10' DAY ) AS DAYS_INTERVALs
  
FROM  BI_SEMANTIC_BASE.VC_SBM_RDM_ORDER_ITEM_HIST ORD
LEFT JOIN BI_SEMANTIC_BASE.VC_SBM_RDM_LINK_CDM_HIST CDM
ON ORD.ORDER_NO = CDM.ORDER_NO 

GROUP BY ORD.ORDER_NO
  ,CDM.SBM_MASTER_CUST_ID

Performance might be quite bad.

 

Rewriting the logic using UNION and nested OLAP-functions should be possible, too, but hard to tell with the existing information.

 

Enthusiast

Re: Calculate number of occurances between two time stamps

the problem is that i'm geeting an error msg because of the MIN_ORDERDATE  present in the where statement :  "ordered analytical functions not allowed in WHERE Clause".

 

 

 

Highlighted
Junior Contributor

Re: Calculate number of occurances between two time stamps

In my query there's no OLAP function used, neither in the MIN_IVRDATE calculation nor any other place.

Senior Supporter

Re: Calculate number of occurances between two time stamps

It is always so much easier to support questions like this if you would provide some CODE to set up the test data which would allow to easily run some queries as @dnoeth also stated.

If I translate your lists into some DDLs and DML - which I also don't like as it is waisting my time -  would come up with

create multiset table VC_SBM_RDM_ORDER_ITEM_HIST
( ORDER_NO int,
  ISSUE_DTM date
  ) primary index (order_no)
  ;
  
insert into VC_SBM_RDM_ORDER_ITEM_HIST values ( 1, '2017-09-01');
insert into VC_SBM_RDM_ORDER_ITEM_HIST values ( 1, '2017-09-01');
insert into VC_SBM_RDM_ORDER_ITEM_HIST values ( 2, '2017-09-02');
insert into VC_SBM_RDM_ORDER_ITEM_HIST values ( 2, '2017-09-02');

create table VC_SBM_RDM_LINK_CDM_HIST
(
 ORDER_NO int,
 smb_key char(3)
) primary index (order_no);

insert into VC_SBM_RDM_LINK_CDM_HIST values (1, 'zzz');
insert into VC_SBM_RDM_LINK_CDM_HIST values (2, 'yyy');


create multiset table VC_CALLS_IVR
(
smb_key char(3),
call_date date
) primary index (smb_key);


insert into VC_CALLS_IVR
select smb_key,
       c1.calendar_date 
from  VC_SBM_RDM_LINK_CDM_HIST 
cross join 
sys_calendar.calendar c1
where c1.calendar_date between '2017-09-01' and '2017-09-20'
and random(0,1) = 1
union all
select smb_key,
       c1.calendar_date 
from  VC_SBM_RDM_LINK_CDM_HIST 
cross join 
sys_calendar.calendar c1
where c1.calendar_date between '2017-09-01' and '2017-09-20'
and random(0,1) = 1
union all
select smb_key,
       c1.calendar_date 
from  VC_SBM_RDM_LINK_CDM_HIST 
cross join 
sys_calendar.calendar c1
where c1.calendar_date between '2017-09-01' and '2017-09-20'
and random(0,1) = 1
union all
select smb_key,
       c1.calendar_date 
from  VC_SBM_RDM_LINK_CDM_HIST 
cross join 
sys_calendar.calendar c1
where c1.calendar_date between '2017-09-01' and '2017-09-20'
and random(0,1) = 1
;

you might run the last bit some time as the result set differes each run.

I got

yyy   	2017-09-01
yyy   	2017-09-02
yyy   	2017-09-03
yyy   	2017-09-06
yyy   	2017-09-08
yyy   	2017-09-09
yyy   	2017-09-10
yyy   	2017-09-11
yyy   	2017-09-15
yyy   	2017-09-17
yyy   	2017-09-18
yyy   	2017-09-19
zzz   	2017-09-02
zzz   	2017-09-03
zzz   	2017-09-06
zzz   	2017-09-07
zzz   	2017-09-09
zzz   	2017-09-09
zzz   	2017-09-10
zzz   	2017-09-10
zzz   	2017-09-11
zzz   	2017-09-14
zzz   	2017-09-15
zzz   	2017-09-16
zzz   	2017-09-17
zzz   	2017-09-17
zzz   	2017-09-19
zzz   	2017-09-20

with this data you could calculate the desired result without olap functions as

select ord.order_no, 
       ord.min_issue_dtm,
       ord.min_issue_dtm + 10,
       l.smb_key,
       sum(case when ord.min_issue_dtm + 10 >= call_date then 1 else 0 end)
from (select order_no, min(issue_dtm) as min_issue_dtm from VC_SBM_RDM_ORDER_ITEM_HIST group by order_no) as ord
left outer join 
VC_SBM_RDM_LINK_CDM_HIST as l
on ord.order_no = l.order_no
left outer join VC_CALLS_IVR c
on l.smb_key = c.smb_key
group by 1,2,3,4
order by 1,2,3,4
;

which gives

1	2017-09-01	2017-09-11	zzz   	9
2	2017-09-02	2017-09-12	yyy   	8

If this is not what you expect change the code and share your sql.

Have fun Ulrich

Enthusiast

Re: Calculate number of occurances between two time stamps

thanks a lot to everyone , Ulrich thank you very much , it worked with your proposed solution.