I've got the following scenario:
I've got
- a table of assets with standard columns
- a table of hardware assets with extension columns
- a history table where all changes to an asset are stored with asset ID, field name, date, new value
- a contact table
I want to set a field in hardware assets called "first_usage" to the earliest date of change of the contact field (called "resource_contact") when the new value was a contact with a user-ID (i.e. no technical user or group).
One difficulty was that no ID is stored in the "new value" but a combined string of first, middle and last name (in case change of contact).
My first try of a query looks like this:
UPDATE dbo.asset_z_hardware
SET first_usage = (
select min(history.com_dt)
from dbo.nr_com history,
dbo.ca_contact contact
where history.attr_name = 'resource_contact' AND
history.com_par_id = own_resource_uuid AND
contact.userid IS NOT NULL AND
(history.new_value = '' +
case when contact.last_name is not null then contact.last_name end +
case when contact.first_name is not null then ', ' + contact.first_name end +
case when contact.middle_name is not null then ' ' + contact.middle_name end
)
)
This seemed to work quite OK, though only some assets were updated with a date (probably I need to check the string-comparison and contacts which changed meanwhile).
But then I realized that I only should update only active assets! The "inactive" flag is in the main asset table called "ca_owned_resource". Unfortunately, after adding the new condition, the first usage values were all set to the same date!!! How come? I did not find my mistake.
This is the second query with "inactive" flag and link between hardware and main asset:
UPDATE dbo.asset_z_hardware
SET first_usage = (
select min(history.com_dt)
from dbo.nr_com history,
dbo.ca_contact contact,
dbo.ca_owned_resource res
where history.attr_name = 'resource_contact' AND
res.inactive = 0 AND res.own_resource_uuid = own_resource_uuid AND
history.com_par_id = own_resource_uuid AND
contact.userid IS NOT NULL AND
(history.new_value = '' +
case when contact.last_name is not null then contact.last_name end +
case when contact.first_name is not null then ', ' + contact.first_name end +
case when contact.middle_name is not null then ' ' + contact.middle_name end
)
)
I'd appreciate any help and suggestions for the only-one-date-mistake, also for improving the rest, if any ideas.
