Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Worked Perfectly... Your amazing... Thank you so much for your quick reply.