Database
Highlighted
Fan

## Count number of active items based on two dates

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

3 REPLIES 3

## Re: Count number of active items based on two dates

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.

Fan

## Re: Count number of active items based on two dates

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 `
`CASE WHEN t1.Close_Date = date '1900-01-01' THEN CURRENT_DATE ELSE t1.Close_Date END`