2 Replies Latest reply: Apr 24, 2017 5:37 PM by Kim Stroupe RSS

    Count new asset and transfers

    Kim Stroupe

      Hello,

      I have a SQL view of Asset history which shows asset_id, emp_id and start_dt.  I am making a chart to count # of new assets each month and # of transferred assets each month.  I am able to find the # of new assets each month, but I'm having difficulty with knowing if the asset has a transfer.

       

      Asset_id   emp_id  start_dt

      1               1          03/01/2017   = New asset in March 2017

      2               2          04/01/2016   = New asset in April 2016

      2               3          03/01/2017   = Transferred in March 2017

       

       

      Right now my Chart is counting (1) New in April 2016 and (2) New in March 2017

       

      I would like to see (1) New in April 2016  and (1) New in March 2017 and (1) Transferred in March 2017

       

      Any help is appreciated.

       

      Thanks in advance

      Kim

        • Re: Count new asset and transfers
          Massimo Grossi

          maybe in the script;

          you can read the table ordered by asset and date and add a flag when the asset changes

           

          SET DateFormat='MM/DD/YYYY';

           

          load * inline [

          Asset_id,  emp_id,  start_dt, comment

          1    ,          1,          03/01/2017,  = New asset in March 2017

          2    ,          2 ,        04/01/2016 ,  = New asset in April 2016

          2      ,        3  ,        03/01/2017  , = Transferred in March 2017

          2      ,        4  ,        03/01/2018  , = Transferred in March 2018

          3      ,        5  ,        03/01/2017  , = Transferred in March 2017

          ];

           

          Z2:

          load

            *,

            if(Peek('Asset_id')=Asset_id, 0, 1) as FlagNew

          Resident Z

          order by Asset_id, start_dt;

           

          DROP Table Z;