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!
Tony
