k1000 Asset History and Asset mysql join help
10/20/2016 851 views
I've been slowly teaching myself mysql for the past couple years and have usually been successful in building reports I need. I'm currently trying to build a new report for accounting that shows changes to location and status. I'm able to make reports that show all the asset information I want, and reports that show all the asset history information I want, but having difficulty joining the two.
I'm looking for have a report that has the standard asset information (name, type, serial, etc) with asset history so I can track changes to status or location (ex, recycled or spare)
This is the standard asset history report that currently works:
DATE_FORMAT(TIME,'%W %m-%d-%Y') as TIME,
VALUE1 as "Previous Status",
VALUE2 as "Current Status"
WHERE FRIENDLY_FIELD_NAME = 'STATUS' AND TIME > DATE_SUB(NOW(), INTERVAL 168 HOUR)
ORDER BY NAME
And this is my modified report:
SELECT ASSET.NAME AS "Asset Name",
ASSET_HISTORY.TYPE_NAME AS "Asset Type",
ASSET_DATA_5.FIELD_818 AS "Model Name",
ASSET_DATA_5.FIELD_816 AS "Serial Number",
ASSET_DATA_5.FIELD_817 AS "Product Number",
DATE_FORMAT(TIME,'%W %m-%d-%Y') as "Change Date",
USER_TEXT AS "Changed by",
ASSET_HISTORY.CHANGE_TYPE AS "Change Type",
ASSET_HISTORY.FRIENDLY_FIELD_NAME AS "Changed Field",
ASSET_HISTORY.VALUE1 as "Previous Status",
ASSET_HISTORY.VALUE2 as "Current Status"
LEFT JOIN ASSET ON ASSET_DATA_5.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=5
LEFT JOIN ASSET_ASSOCIATION J25 ON J25.ASSET_ID = ASSET.ID AND J25.ASSET_FIELD_ID=25
LEFT JOIN ASSET A25 ON A25.ID = J25.ASSOCIATED_ASSET_ID
LEFT JOIN ASSET_DATA_1 AD25 ON AD25.ID = A25.ASSET_DATA_ID
LEFT JOIN ASSET_HISTORY ON ASSET_HISTORY.ID = ASSET_DATA_5.ID AND ASSET.ASSET_TYPE_ID=5
WHERE ((ASSET_HISTORY.CHANGE_TYPE = 'Modification') AND (ASSET_HISTORY.FRIENDLY_FIELD_NAME = 'status'))
For some reason, the report will show the change type info for any items that show up with 'modification', but when I add AND (ASSET_HISTORY.FRIENDLY_FIELD_NAME = 'status' I get nothing in my report. Any ideas?
Thanks in advance for the help.