Date Difference and Filtering on date difference

[t16turbo]

Hi guys,

I have the following SQL that returns time transactions - including fields for Entry Date Transaction Date and Quantity of time booked:

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'

I need to display another column showing the number of days between the ENTRYDATE and TRANSDATE.
I also need a criteria that will only display the results where the TRANSDATE is a week or more BEFORE the ENTRYDATE.
(7 days +)
can anyone help?

 

[SantaMufasa]

Turbo,

Oracle's date-handling capabilities are probably the strongest in the industry. Here is a version of your code that, I believe, does what you want (additions in bold):

SELECT * FROM (SELECT ENTRYDATE, TRANSDATE, [b]ENTRYDATE-TRANSDATE delta_days[/b], QUANTITY, COMPANY_CODE, PROJECT_CODE, RESOURCE_CODE, TASK_ID, CHARGE_CODE FROM
niku.PPA_WIP
[b]WHERE ENTRYDATE-TRANSDATE > 7[/b] 

UNION ALL
SELECT ENTRYDATE, TRANSDATE, [b]ENTRYDATE-TRANSDATE[/b], QUANTITY, COMPANY_CODE, PROJECT_CODE, RESOURCE_CODE, TASK_ID, CHARGE_CODE FROM
niku.PPA_TRANSCONTROL
[b]WHERE ENTRYDATE-TRANSDATE > 7[/b]) a
WHERE COMPANY_CODE = 'DST01UKT'

 [santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[[URL unfurl="true"]www.dasages.com:[/URL] Providing low-cost remote Database Admin services]
Click here to join [url=http://tek-tips.com/threadminder.cfm?pid=1426]Utah Oracle Users Group on Tek-Tips[/url] if you use [b]Oracle[/b] in [b]Utah[/b] USA.