Contracts valid in each date (with a START_DATE and an END_DATE)

[Anonymous]

Hello people!

I have a table with the following fields:

CD_CONTRACT | DATE_START | DATE_END

and a filter by DATE.

I need to create a table by year(DATE) with the count of contracts that are valid in each DATE.

I've tried this:

///////

=count(

     {<

          DATE_START = {"<=$(=max(date(DATE)))"},

          DATE_END = {">=$(=min(date(DATE)))"}

     >}

distinct CD_CONTRACT)

///////

and this:

///////

=aggr(

     count(

          {<

               DATE_START = {"<=$(=max(date(DATE)))"},

               DATE_END = {">=$(=min(date(DATE)))"}

          >}

     distinct CD_CONTRACT)

, DATE)

///////

But the count is always the total count, it doesn´t aggregate by DATE.

I know that one option is to create a table with CD_CONTRACT and a line for each date where the contract was valid. But I have a lot of contracts and very big valid periods, so it´s not an option because of very low performance

Can anybody help me with some idea please?

Thank you very much in advance