Query Report adding Asset State changes
Hi Everyone,
I currently have a query that gives me
Asset Name, Product Name, Current User, Asset Tag, Serial Number, Asset Tag, State, Previous State
and the dates that these changes occured
This is really useful at the moment for finding out how many assets a specific user has.
The report works however, If someone has had the asset assigned to them before. When the sate changes it makes another row with the asset state changing but says that the old user is the current user.
Wanted to find out if there was a way to fix this, because its because of the way my Query is written.
Or there might be a way to delete the kind of duplicate entries
I really appreciate any help
Thanks everyone!
Here is the Query that I have been using:
SELECT
resources.resourcename AS "Asset Name",
componentdefinition.componentname AS "Product Name",
aaausercurrentuser.first_name AS "Current User",
resources.ASSETTAG AS "Asset Tag",
resources.serialno AS "Serial Number",
state.displaystate AS "Asset State" ,
prevstate.displaystate AS "Previous State",
to_char(to_timestamp(resourcestatehistory.starttime/1000), 'DD/MM/YYYY HH24:MI:SS') AS "Asset State Changed Date",
to_char(to_timestamp(resourceownerhistory.starttime/1000), 'DD/MM/YYYY HH24:MI:SS') AS "Assigned to User at",
CASE
WHEN (to_char(to_timestamp(resourceownerhistory.endtime / 1000), 'DD/MM/YYYY HH24:MI:SS') is not null)
then to_char(to_timestamp(resourceownerhistory.endtime / 1000), 'DD/MM/YYYY HH24:MI:SS')
ELSE 'using now' end AS "Assigned upto"
FROM resourceownerhistory
LEFT JOIN resources ON resources.resourceid = resourceownerhistory.resourceid
LEFT JOIN sduser ON sduser.userid = resourceownerhistory.userid
LEFT JOIN aaauser AS aaausercurrentuser ON aaausercurrentuser.user_id = resourceownerhistory.userid
LEFT JOIN componentdefinition ON resources.componentid = componentdefinition.componentid
LEFT JOIN resourcestatehistory ON resourcestatehistory.RESOURCEID = resources.RESOURCEID
LEFT JOIN resourceState state ON resourcestatehistory.RESOURCESTATEID = state.RESOURCESTATEID
LEFT JOIN resourceState prevstate ON resourcestatehistory.PREVRESOURCESTATEID = prevstate.RESOURCESTATEID
where resources.resourceid in (
select
resourceownerhistory.resourceid
from resourceownerhistory
left join aaauser on aaauser.user_id = resourceownerhistory.userid
where aaauser.first_name like '%')
ORDER BY Resourcestatehistory.starttime
Servvice Desk Plus Version:
14.1 Build 14104
Database PGSQL