Brio sql Query help

Genesys Cloud WFM
1

Have used this in Brio to get previous days data and it works well.

T_Queue_Hour.AGG_BY_DAY IN (SELECT MAX(TO_CHAR(SYSDATE-1,‘YYYYMMDD’)) FROM From.T_Queue_Hour)

Now we have a need to use a similar statement but get from and to dates. How do we use the “BETWEEN” function in a similar statement so lets say on a Saturday the scheduled report will run giving SYSDATE-6 to SYSDATE-1 ???

Thanks

JS

2

Or I’ve tried adding a second

T_Queue_Hour.AGG_BY_DAY IN (SELECT MAX(TO_CHAR(SYSDATE-1,‘YYYYMMDD’)) FROM From.T_Queue_Hour) AND T_Queue_Hour.AGG_BY_DAY IN (SELECT MAX(TO_CHAR(SYSDATE-2,‘YYYYMMDD’)) FROM From.T_Queue_Hour)

didn’t work either.

1 Like
3

Hi John,

I had my own version of this a while back. To display all results from the previous day, the SQL for the Request Section would be;

CASE WHEN T_xxx_Time_Dim.Date_Yymmdd = CONVERT (char,GETDATE()-1,12) THEN 1 ELSE 0 END

Similarly, for Oracle:

DECODE(T_xxx_Time_Dim.Date_Yyyymmdd, TO_CHAR(SYSDATE-1,‘YYYYMMDD’),1,0)

Then, in the Limit Section of the Brio Query, drag in the Request Statement created above and enter 1 for yesterday’s date as the selection criteria.

You can play about with the GETDATE()-1 or SYSDATE-1 to get the desired results for one day, two days, three days previous, etc. but that won’t give you a date to date range - just a full day’s results based on today’s (system) date.

If you want a range, try creating a series of statements from above (one with -1, another with -2, etc.) and place them in your Limit criteria as an OR (not AND) Statement…

Oracle version (Checked):

DECODE(AL3.DATE_YYYYMMDD, TO_CHAR(SYSDATE-1,‘YYYYMMDD’),1,0)=‘1’ OR DECODE(AL3.DATE_YYYYMMDD, TO_CHAR(SYSDATE-2,‘YYYYMMDD’),1,0)=‘1’ OR DECODE(AL3.DATE_YYYYMMDD, TO_CHAR(SYSDATE-3,‘YYYYMMDD’),1,0)=‘1’ OR DECODE(AL3.DATE_YYYYMMDD, TO_CHAR(SYSDATE-4,‘YYYYMMDD’),1,0)=‘1’ OR DECODE(AL3.DATE_YYYYMMDD, TO_CHAR(SYSDATE-5,‘YYYYMMDD’),1,0)=‘1’ OR DECODE(AL3.DATE_YYYYMMDD, TO_CHAR(SYSDATE-6,‘YYYYMMDD’),1,0)=‘1’)

SQL (Not Checked):

CASE (WHEN T_xxx_Time_Dim.Date_Yymmdd = CONVERT (char,GETDATE()-1,12) THEN 1 ELSE 0 END)=‘1’ OR CASE (WHEN T_xxx_Time_Dim.Date_Yymmdd = CONVERT (char,GETDATE()-2,12) THEN 1 ELSE 0 END)=‘1’ OR CASE (WHEN T_xxx_Time_Dim.Date_Yymmdd = CONVERT (char,GETDATE()-3,12) THEN 1 ELSE 0 END)=‘1’ OR CASE (WHEN T_xxx_Time_Dim.Date_Yymmdd = CONVERT (char,GETDATE()-4,12) THEN 1 ELSE 0 END)=‘1’ OR CASE (WHEN T_xxx_Time_Dim.Date_Yymmdd = CONVERT (char,GETDATE()-5,12) THEN 1 ELSE 0 END)=‘1’ OR CASE (WHEN T_xxx_Time_Dim.Date_Yymmdd = CONVERT (char,GETDATE()-6,12) THEN 1 ELSE 0 END)=‘1’

OK, so I’m sure there’s a better way with a BETWEEN Statement but it’s Friday afternoon and I have checked that this does the job in Oracle! ;D

Tony

4

Thanks Tony,

An engineer also came up with this today and it works great. Now I can schedule this report on Fridays after business closes and it will give hourly detail for the current week from Monday - Friday…

(AL3.AGG_BY_DAY BETWEEN (SELECT MAX(TO_CHAR(SYSDATE-4,‘YYYYMMDD’)) FROM T_QUEUE_HOUR AL3) and (SELECT MAX(TO_CHAR(SYSDATE,‘YYYYMMDD’)) FROM T_QUEUE_HOUR AL3))