6 Replies Latest reply: Jun 11, 2018 8:15 AM by Youssef Belloum RSS

    Count where date1 < date from 2 tables

    nicolas sinquin

      Hye,

       

      I've a table with some connexion, and  an other with the account created date.

      CONNEXION:

      LOAD * INLINE [

      ID_PER, DATE_CONNEXION

      1 ,07/03/2018

      1,09/03/2018

      1,10/03/201

      2,08/03/2018

      2,09/03/2018

      3,04/03/2018

      3,05/03/2018

      3,06/03/2018

      ];

       

      CREATION:

      LOAD * INLINE [

      ID_PER, DATE_CREATION

      1,06/03/2018

      2,08/03/2018

      3,04/03/2018

      ];


      I would like to have, for each connexion, the number of account created on the date

      result wanted :


      ID_PER, DATE_CONNEXION  Nb_ACCOUNT

      1               07/03/2018                    2

      1               09/03/2018                    3

      1               10/03/201                      3 

      2               08/03/2018                    3  

      2               09/03/2018                    3 

      3                04/03/2018                   1  

      3                05/03/2018                   1

      3                06/03/2018                   2

       

       

      I tried with a cartesian cross, but it give me 800 000 000 lines ... a little bit to much.

       

      Thanks for your help.