Dynamic Date -- Either via JDBC (Information Link) or ODBC (Oracle MYSQL)

[Barry Rowan]

I have weekly data that is updated once per month (with the previous month's data). How could I construct a custom query (whether it be via the ODBC connection to the external data or thru the information link in the information designer) to just bring back the latest 52 weeks of data

Each week has a particular TimeID column -- which is indexed.

In the very least, there are 108 weeks. Each month 4 are added -- 112, 116, 120, 124, etc.

 

Is there a way to query the Oracle MySQL data-source to only bring back the latest 52 weeks of data I do not want to filter and a prompt has proven to be inefficient.

I would prefer it to be via the ODBC connection.

TimeID 1-108 (108 being latest week) -- data-source is pruned at 156 weeks.

Because the data is delayed 1 month, most of the time functions are not feasible --GETDATE(), etc. I'd need something that would reference the indexed value (TimeID).

#DrSpotfire

[Peter McKinnis]

If you must use the TimeID, then we need to know a bit more about what happens when you hit TimeID=156. When you hit 156 does it start over at 1 You should be able to write a query that could use the TimeID but one needs to know the max TimeID and what happens if one has recently wrapped around 156.

I would recommend looking at using a date column in the data. Then you could write a query that says give me the last 52 weeks of data based on the max date of the data. For example, this query syntax works in SQL Server:

select * from mytablewhere datecol> dateadd(week, -52, (select max(datecol) from mytable))

This should be similar or the same in MySQL.

Peter