Here is the SQL


SELECT NAME, DESCRIPTION, ASSET_HISTORY.TIME FROM ASSET_HISTORY
Join ASSET on (ASSET_HISTORY.ASSET_ID = ASSET.ID)
WHERE DATE_SUB(NOW(), INTERVAL 3 DAY) < DATE(ASSET_HISTORY.TIME)
and DESCRIPTION like 'Found so%'
order by TIME DESC
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
Thank you, this optimized version really useful
Answered 02/09/2010 by: afzal
Fourth Degree Green Belt

Please log in to comment
0
Sorry to be a stickler...
I advise that any reports posted include the english translation so that we can verify their intent against their content.

This is what this report means in english
Give asset change summaries for all software changes made later than 48 hours prior to the previous midnight

My guess is that this is trying to say:
Give asset change summaries for all software changes made within the last 72 hours

If so then the query should drop the date function since:
SELECT NAME, DESCRIPTION, ASSET_HISTORY.TIME FROM ASSET_HISTORY
Join ASSET on (ASSET_HISTORY.ASSET_ID = ASSET.ID)
WHERE DATE_SUB(NOW(), INTERVAL 3 DAY) < ASSET_HISTORY.TIME
and DESCRIPTION like 'Found so%'
order by TIME DESC



Explanation:
NOW() returns a timestamp and DATE_SUB can operate on timestamps and not just the date portion. However, DATE will strip the time portion. E.g. If now were Feb 20th at 17:30 and you ran this query you would get 0 results:
select * from (select 1) T where
DATE_SUB('2010-02-20 17:31', INTERVAL 3 DAY)< DATE('2010-02-17 18:00')
Answered 02/20/2010 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity