IN-DB Filter all except previous day

[Brawlstrogg]

Hi all,

I'm trying to pull out some data from our Oracle databases where the records are authorised on the previous day. Usually I'd just say '01-Jan-2017' but that date needs to always be the previous day EXCEPT for where it's a Monday and then it needs to be Friday-Sundays dates.

Any ideas?

There doesn't appear to be a date filter on the IN-DB tools is all...

[SGolnik]

Have you tried writing a SQL (case) statement using the Filter in-DB tool?

[jdunkerley79 · Accepted solution]

Assuming a field of 'UpdateDate' something like:

UPDATEDATE >= TRUNC(CASE WHEN to_char(sysdate,'D') in (1,2,7) then next_day(sysdate-7, 'Friday') else sysdate-1 end)
AND UPDATEDATE < TRUNC(sysdate)

Should get you the records only on the prev business day

[Brawlstrogg]

<DELETED>

[Brawlstrogg]

Legend. Working as needed.

Thanks!