reporting on contracts expiring, not expired.
I would like to modify the canned report for expiring in 90 days to not include items that have been retired or disposed. Can't find the table information for those choices. Anyone can help?
Answers (2)
This is the 90 day report:
SELECT ASSET.NAME AS 'Name',
T.NAME AS 'Type',
COALESCE(ADS.FIELD_9985, ADH.FIELD_9955, ADO.FIELD_9940) AS 'Contract Number',
COALESCE(ADS.FIELD_9980, ADH.FIELD_9958, ADO.FIELD_9943) AS 'Expiration Date',
DATEDIFF(COALESCE(ADS.FIELD_9980, ADH.FIELD_9958, ADO.FIELD_9943), NOW()) AS 'Days Remaining'
FROM ASSET
JOIN ASSET_TYPE T ON T.ID = ASSET.ASSET_TYPE_ID
LEFT JOIN ASSET_DATA_10000 ADS ON ADS.ID = ASSET.ASSET_DATA_ID and ASSET.ASSET_TYPE_ID = 10000
LEFT JOIN ASSET_DATA_10001 ADH ON ADH.ID = ASSET.ASSET_DATA_ID and ASSET.ASSET_TYPE_ID = 10001
LEFT JOIN ASSET_DATA_10200 ADO ON ADO.ID = ASSET.ASSET_DATA_ID and ASSET.ASSET_TYPE_ID = 10200
WHERE ASSET.ASSET_TYPE_ID in (10000,10001,10200)
LEFT JOIN ASSET_STATUS ON ASSET_STATUS.ID = ASSET.ASSET_STATUS_ID WHERE ((ASSET_STATUS.NAME != 'Disposed'))
AND DATEDIFF(COALESCE(ADS.FIELD_9980, ADH.FIELD_9958, ADO.FIELD_9943),NOW()) < 90
And this is a report I can create on Asset's which are not disposed.
SELECT ASSET.NAME AS ASSET_NAME, ASSET_STATUS.NAME AS ASSET_STATUS, ASSET_DATA_10000.FIELD_9979 AS FIELD_9979, ASSET_DATA_10000.FIELD_9980 AS FIELD_9980, ASSET_DATA_10000.FIELD_9985 AS FIELD_9985 FROM ASSET_DATA_10000 LEFT JOIN ASSET ON ASSET_DATA_10000.ID = ASSET.ASSET_DATA_ID AND ASSET.ASSET_TYPE_ID=10000 LEFT JOIN ASSET ASSET_LOCATION ON ASSET_LOCATION.ID = ASSET.LOCATION_ID LEFT JOIN ASSET_ASSOCIATION PARENT_AA10000 ON PARENT_AA10000.ASSET_ID = ASSET.ID LEFT JOIN ASSET PARENT_A10000 ON PARENT_A10000.ID = PARENT_AA10000.ASSOCIATED_ASSET_ID AND PARENT_A10000.ASSET_TYPE_ID = 10000 LEFT JOIN ASSET_STATUS ON ASSET_STATUS.ID = ASSET.ASSET_STATUS_ID WHERE ((ASSET_STATUS.NAME != 'Disposed')) ORDER BY ASSET_NAME
Most of the same fields are present and when I try to go to the first report or the second and join what is missing and get what I need, i error! I think I really need the table information.
So...I stepped away for a minute and came back and figured it out. This report will give you all the items expiring in 90 days or less and not include anything marked "disposed".
SELECT ASSET.NAME AS 'Name',
T.NAME AS 'Type',
COALESCE(ADS.FIELD_9985, ADH.FIELD_9955, ADO.FIELD_9940) AS 'Contract Number',
COALESCE(ADS.FIELD_9980, ADH.FIELD_9958, ADO.FIELD_9943) AS 'Expiration Date',
DATEDIFF(COALESCE(ADS.FIELD_9980, ADH.FIELD_9958, ADO.FIELD_9943), NOW()) AS 'Days Remaining'
FROM ASSET
JOIN ASSET_TYPE T ON T.ID = ASSET.ASSET_TYPE_ID
LEFT JOIN ASSET_DATA_10000 ADS ON ADS.ID = ASSET.ASSET_DATA_ID and ASSET.ASSET_TYPE_ID = 10000
LEFT JOIN ASSET_DATA_10001 ADH ON ADH.ID = ASSET.ASSET_DATA_ID and ASSET.ASSET_TYPE_ID = 10001
LEFT JOIN ASSET_DATA_10200 ADO ON ADO.ID = ASSET.ASSET_DATA_ID and ASSET.ASSET_TYPE_ID = 10200
LEFT JOIN ASSET_STATUS ON ASSET_STATUS.ID = ASSET.ASSET_STATUS_ID
WHERE ASSET.ASSET_TYPE_ID in (10000,10001,10200)
AND DATEDIFF(COALESCE(ADS.FIELD_9980, ADH.FIELD_9958, ADO.FIELD_9943),NOW()) < 90 AND ASSET_STATUS.NAME != 'Disposed'