Genesys CTI User Forum

Genesys CTI User Forum => Genesys CTI Technical Discussion => Topic started by: jsty40 on May 17, 2007, 02:23:24 PM

Title: Brio sql Query help
Post by: jsty40 on May 17, 2007, 02:23:24 PM

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

[color=blue]T_Queue_Hour.AGG_BY_DAY IN (SELECT MAX(TO_CHAR(SYSDATE-1,'YYYYMMDD')) FROM From.T_Queue_Hour)[/color]

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

Title: Re: Brio sql Query help
Post by: jsty40 on May 17, 2007, 03:30:06 PM

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.

Title: Re: Brio sql Query help
Post by: tony on May 18, 2007, 02:03:10 PM

Hi John,

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

[color=blue]CASE WHEN T_xxx_Time_Dim.Date_Yymmdd = CONVERT (char,GETDATE()-1,12) THEN 1 ELSE 0 END[/color]

Similarly, for Oracle:

[color=blue]DECODE(T_xxx_Time_Dim.Date_Yyyymmdd, TO_CHAR(SYSDATE-1,'YYYYMMDD'),1,0)[/color]

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

You can play about with the [color=blue]GETDATE()-1[/color] or [color=blue]SYSDATE-1[/color] 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 [b]Limit [/b] criteria as an OR ([u]not AND[/u]) Statement...

Oracle version ([color=green]Checked[/color]):

[color=blue]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')[/color]

SQL ([b]Not[/b] Checked):

[color=blue]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' [/color]

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

Tony

Title: Re: Brio sql Query help
Post by: jsty40 on May 18, 2007, 02:54:06 PM

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