custom SQL in ODBC satge
Posted: Mon Mar 12, 2007 11:25 am
hi all
i have a table attr_fact where i am storing 52 weeks of history.I want to pull data from that table and put it in a sequential table.I need only 13 weeks(latest 13 weeks) of data.I am having a field week_code in that table which is being populated by look up on time_dim table.time_dim table is a table where for each fiscal date there is a week_code means 365 days are there and for each day corresponding week_code is there.
Fiscal_date week_code
20070101 200701
20070102 200701
....
20070110 200702
.....
like tis..........
week code ranges from 1 to 52/53 for a year..
200701,200702............200752
how can i write a SQL that would ask me data_date as a run time parameter and using that value i ll find the 13 distinct weeks from the time_dim and fetch data for those 13 weeks.
i am able to find the week_code from sysdate by using select (sysdate,'YYYYIW') from dual.but that value is different from the value in the table.So in my SQL i want to refer the table value.
select * from attr_fact where week_code between.........and............
1st blank should give me 13 distinct weeks back to the recent week and second blank will refer to recent week.is it possible to write a subquery there so that it will give the desired result.
[/list]
i have a table attr_fact where i am storing 52 weeks of history.I want to pull data from that table and put it in a sequential table.I need only 13 weeks(latest 13 weeks) of data.I am having a field week_code in that table which is being populated by look up on time_dim table.time_dim table is a table where for each fiscal date there is a week_code means 365 days are there and for each day corresponding week_code is there.
Fiscal_date week_code
20070101 200701
20070102 200701
....
20070110 200702
.....
like tis..........
week code ranges from 1 to 52/53 for a year..
200701,200702............200752
how can i write a SQL that would ask me data_date as a run time parameter and using that value i ll find the 13 distinct weeks from the time_dim and fetch data for those 13 weeks.
i am able to find the week_code from sysdate by using select (sysdate,'YYYYIW') from dual.but that value is different from the value in the table.So in my SQL i want to refer the table value.
select * from attr_fact where week_code between.........and............
1st blank should give me 13 distinct weeks back to the recent week and second blank will refer to recent week.is it possible to write a subquery there so that it will give the desired result.
[/list]