I'm trying to automate some reports in the Analytics Cloud by using SQL syntax within SQL filter. I was able to make a report refresh on a daily basis; but I'm stuck on getting it to work for the week. Is there a syntax that will recognize Monday to Friday of the current week? Without actually having to manually, change the dates every Monday for example.
This is what I currently have ("Header Details"."Creation Date" BETWEEN timestamp '2019-03-25 00:00:00' AND timestamp '2019-03-30 00:00:00') but I have to go and edit the date range every Monday. Is it possible to automate it by using “SQL_TSI_WEEK”? Maybe! Am I trying to do something that can’t be done within SQL filter and should be done elsewhere?
I'm not clear on where exactly you're entering this filter. But if it's part of a SQL query, you can find rows in the current week by:
- Getting the start of the ISO week (Monday) with trunc ( <dt>, 'iw')
- Returning rows between this date and trunc ( <dt>, 'iw') + 5:
"Header Details"."Creation Date" >= trunc ( sysdate, 'iw' )
AND "Header Details"."Creation Date" < trunc ( sysdate, 'iw' ) + 5
For example:
with rws as (
select level rn, trunc ( sysdate, 'iw' ) + level dt
from dual
connect by level <= 10
)
select rn, dt, to_char ( dt, 'Day' )
from rws
where dt >= trunc ( sysdate, 'iw' )
and dt < trunc ( sysdate, 'iw' ) + 5;
RN DT TO_CHAR(DT,'DAY')
1 02-APR-2019 00:00:00 Tuesday
2 03-APR-2019 00:00:00 Wednesday
3 04-APR-2019 00:00:00 Thursday
4 05-APR-2019 00:00:00 Friday