IN-DB Filter all except previous day

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...

Preparation

In Database

In-Database

Accepted answers

jdunkerley79

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

All comments

SGolnik

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

jdunkerley79

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

Brawlstrogg

Legend. Working as needed.

Thanks!

Quick Links

This months top contributors

FláviaB 262

JORGE4900 235

caltang 177

DanM 167

IraWatt 157