Count new asset and transfers

[kstroupe]

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

[maxgro · Accepted solution]

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;

[kstroupe]

Worked Perfectly... Your amazing... Thank you so much for your quick reply.