I have been working on one item that is easy in excel and I can not do it in teradata.
In excel I use the folowing formula =COUNTIFS($A$2:$A$30000,"<="&E2,$B$2:$B$30000,">="&E2) to count number of active items for last 7 days.
It is easier as the last 7 days are in the seperate column and not sure how to get the same results in Teradata?
dt has got last 7 days:
SELECT calendar_date AS dt
FROM sys_calendar.CALENDAR
WHERE calendar_date between (CURRENT_DATE - 7) and (CURRENT_DATE)
ORDER BY dt
T1 holds all data:
CREATE TABLE T1
(
ID INT ,
Open_Date DATE format 'YYYY-MM-DD',
Close_Date DATE format 'YYYY-MM-DD')
insert into T1 values (1, '2018-12-17', '2018-12-18')
insert into T1 values (2, '2018-12-18', '2018-12-18')
insert into T1 values (3, '2018-12-18', '2018-12-18')
insert into T1 values (4, '2018-12-19', '2018-12-20')
insert into T1 values (5, '2018-12-19', '2018-12-21')
insert into T1 values (6, '2018-12-20', '2018-12-22')
insert into T1 values (7, '2018-12-20', '2018-12-22')
insert into T1 values (8, '2018-12-21', '2018-12-25')
insert into T1 values (9, '2018-12-22', '2018-12-26')
insert into T1 values (10, '2018-12-23', '2018-12-27')
Desired results and the excel table:
