Date Difference as a criteria

[t16turbo]

Hi folks,

I have a very basic report at the moment that uses the following SQL:

SELECT * FROM (SELECT ENTRYDATE, TRANSDATE, QUANTITY, COMPANY_CODE, PROJECT_CODE, RESOURCE_CODE, TASK_ID, CHARGE_CODE FROM
niku.PPA_WIP

UNION ALL
SELECT ENTRYDATE, TRANSDATE, QUANTITY, COMPANY_CODE, PROJECT_CODE, RESOURCE_CODE, TASK_ID, CHARGE_CODE FROM
niku.PPA_TRANSCONTROL) a
WHERE COMPANY_CODE = 'DST01UKT'

what I need to do is two things:
1. Show the number of days between the TRANSDATE and ENTRYDATE
2. Filter results to ONLY show those records where the TRANSDATE is more than a week before the ENTRYDATE

How can I do this?

 

[gdwood]

You can just subtract one date from another if they are dates to get the difference and use the greater than comparrison in the where clause

select transdate-entrydate as datediff
from table
where transdate - 7 > entrydate