"variable" limit

[mstepPCI]

I'm VERY new to brioquery and here is what i want to do.
I need to limit my query based on a date in a database. Furthermore, i need to limit this date minus 8 days to capture all the dates on the previous Sunday. (all the dates in this field are on Mondays).

I can't hardcode the dates because they are always changing, is there anyway to automate this?

The data is set up YYYYMMDD.

 

[kublait]

Have you try using the Custom SQL as your variable limits? You can set the SQL statement to something like:
to_char(sysdate)-8

So when you schedule it to run on any given day it will only extract the data from 8 days ago.

Hope that helps.

 

[mstepPCI]

I already tried doing this (a different way allbeit). What you said gives a syntax error. Do i really want to convert to characters when doing a calculation?

 

[kublait]

What type of database are you using? the SQL syntax between Oracle and MS SQL are slightly different.

 

[mstepPCI]

the database is redbrick

 

[kublait]

mstepPCI,
I'm not familiar with Redbrick. However, this SQL scripts: (datepart(wk, dateadd(dd,-1, TABLE_NAME.COLUMN_NAME)) = datepart(wk,dateadd(dd,-1, getdate())) - 1)

Hope this helps.

 

[mstepPCI]

error(449) Function dateadd has incorrect type for argument 3. Expected type: DATETIME, actual type: CHARACTER