3 Replies Latest reply: Jan 30, 2018 12:48 PM by gz gz RSS

    Calculation of staying days.

    gz gz

      Hello,

      need help

       

      I have database of visitors that have fields

      id cross_date directions

      00001 1/15/2013 cross_in

      00002 2/10/2013 cross_out

      00003 4/14/2013 cross_in

      00001 3/24/2013 cross_out

      00002 5/11/2013 cross_in

      00003 4/15/2013 cross_out

      ... ....

      i need to calculate the staying 30(days) period from above mention id.

       

      for example

      id 00003 cross_in 14 Apr and cross_out 15 Apr so staying period is 1 day.

       

       

      Thanks

        • Re: Calculation of staying days.
          Nicole Smith

          You can create a calculated field in your load script like this:

           

          Temp:
          LOAD * INLINE [
           id, cross_date, directions
           00001, 1/15/2013, cross_in
           00002, 2/10/2013, cross_out
           00003, 4/14/2013, cross_in
           00001, 3/24/2013, cross_out
           00002, 5/11/2013, cross_in
           00003, 4/15/2013, cross_out
          ];
          
          
          
          
          Final:
          LOAD id,
           cross_date AS cross_in_date
          RESIDENT Temp
          WHERE directions = 'cross_in';
          
          
          LEFT JOIN (Final)
          LOAD id,
           cross_date AS cross_out_date
          RESIDENT Temp
          WHERE directions = 'cross_out';
          
          
          LEFT JOIN (Final)
          LOAD id,
           cross_out_date - cross_in_date AS staying_period
          RESIDENT Final;
          
          
          DROP TABLE Temp;
          


          This will yield a table that looks like this:

           

          id cross_in_date cross_out_date staying_period
          000011/15/20133/24/201368
          000025/11/20132/10/2013-90
          000034/14/20134/15/20131