Date limit

[stacybost]

I need six weeks of data in my report, including the current week(finished or not). So, if today is Friday and week_id 27, I need to pull in week_id's 22 through 27.

In my time table, I have:
week_id, dt

In my fact table, I have:
dt, sales

How can I pull six weeks of data based on the current date?
i.e.
select * from sales s, time t
where s.dt = t.dt
and week_id >= select (week_id - 5) from time where dt = trunc(sysdate)

Thanks for any help! This sub-query type of stuff is giving me a hard time.

Stacy

 

[nagrajm]

Stacy

I am assuming that you want a solution for Impromptu.

If your database supports a function that can extract the week number from the system date, you could use that function in the filter condition to derive your results.

Otherwise, a sub query functionality in Impromptu is achieved by the use of DATASET.

Your filter condition in the main report would say;

week_id in Dataset

The dataset would point to an IMR report (say WEEK.IMR) with only one column called week_id.

The filter condition of WEEK.IMR would say

select distinct (week_id - 5)
from time
where dt = trunc(sysdate)

Impromptu will then generate the SQL you need;

select * from sales s, time t
where s.dt = t.dt
and week_id >= select (week_id - 5) from time where dt = trunc(sysdate)

Hope this helps.

Nagraj

 

[stacybost]

I was indeed looking for an Impromptu solution. Two good possibilities -- that helps a lot, thanks!

Stacy

 

[nagrajm]

Hi Stacy

I am afraid there is a small error in my last thread;

The filter condition in the main report should instead read

week_id >= any DATASET (or)
week_id >= all DATASET

Sorry about that :-(

 

[nagrajm]

Just to add to my last thread, if your time table should have data for more than one year, you need to filter your main report further on year_id. I am sure you know that...just in case!