/build/static/layout/Breadcrumb_cap_w.png

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?


0 Comments   [ + ] Show comments

Answers (2)

Posted by: jaw8899 1 month ago
Senior White Belt
0

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.
Posted by: jaw8899 1 month ago
Senior White Belt
0

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'


Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ