Count number of active items based on two dates

[tomgag]

Hi All, Just starting with teradata and I need to transfer the calculation I make in excel to Teradata.

This is the formula and use in excel

=COUNTIFS($A$2:$A$30000,"<="&E2,$B$2:$B$30000,">="&E2)

and this is the table and results.

Lets assume I have table dt for Date Column

select calendar_date as dt
  from sys_calendar.calendar
 where calendar_date between (current_date - 7) and  (current_date)
 ORDER BY dt

And the rest in T1

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')  

[Waldar]

Well, this one is different.

The easy way is to join the calendar with your table, using a between :

  select cl.calendar_date as dt
       , count(*)         as count_open
    from sys_calendar.calendar as cl
    join t1                          on t1.Open_Date  <= cl.calendar_date
                                    and t1.Close_Date >= cl.calendar_date
   where cl.calendar_date between date '2018-12-17' and date '2018-12-23'
group by cl.calendar_date
order by cl.calendar_date asc;

But Teradata has some handy extensions :

with cte_expand (dt) as
(
  select begin(expd) as dt
    from t1
  expand on (period(Open_Date, Close_Date + 1) p_intersect period(date '2018-12-17', date '2018-12-23' + 1)) as expd by interval '1' day
)
  select dt, count(*)
    from cte_expand
group by dt
order by dt;

Check in explain plan the difference.

[tomgag]

Thank you again. It works. One more question in refernce to code 1

I need to change this line:

join t1 on t1.Open_Date  <= cl.calendar_date and t1.Close_Date >= cl.calendar_date

 to include the condition that if t1.Close_Date is equal to 01/01/1900 then it changes to today.

I have this code but I can not get it working:

join t1 on t1.Open_Date  <= cl.calendar_date and ( CASE WHEN t1.Close_Date = '01/01/1900 00:00:00' THEN CURRENT_DATE ELSE t1.Close_Date  END)  >= cl.calendar_date 

[Waldar]

Try like this :

CASE WHEN t1.Close_Date = date '1900-01-01' THEN CURRENT_DATE ELSE t1.Close_Date END