| Record with date closest to sysdate [message #159299] |
Fri, 17 February 2006 04:09  |
Shauni
Messages: 6
Registered: November 2005
|
Junior Member |
|
|
Hello,
We have a list of customers and the products they ordered during the last 3 months. They all have a maximum amount they can order each 3 months per product
Now we would like to have a report in Discoverer that shows only the record that is closest to the sysdate (date <= sysdate).
What we have now looks like this...
Customer1
>>Product1
>>>>Day 01 | Quantity 15 | Cumul Qty 15 | Trend 15%
>>>>Day 05 | Quantity 25 | Cumul Qty 40 | Trend 40%
>>>>Day 16 | Quantity 10 | Cumul Qty 50 | Trend 50%
>>>>Day 18 | Quantity 05 | Cumul Qty 55 | Trend 55%
>>Product2
>>>>Day 03 | Quantity 10 | Cumul Qty 10 | Trend 5%
>>>>Day 17 | Quantity 10 | Cumul Qty 20 | Trend 10%
and so on
Let's say the max amount this customer can order of product1 is 100 and 200 for product2. The Trend gives you the percentage a customer has already ordered of that max amount.
Now all I want to see is...
Customer1
>>Product1
>>>>Day 16 | Quantity 10 | Cumul Qty 50 | Trend 50%
>>Product2
>>>>Day 17 | Quantity 10 | Cumul Qty 20 | Trend 10%
Is this possible in Discoverer? I'm completely stuck with this problem 
Thanks...
[Updated on: Fri, 17 February 2006 04:10] Report message to a moderator |
|
|
|
| Re: Record with date closest to sysdate [message #159801 is a reply to message #159299] |
Tue, 21 February 2006 12:50  |
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
|
Senior Member |
|
|
You should be able to do this using analytical functions with a windowing clause.
I'm not entirely sure what your requirements are, but try something like:
create filter with order_date <= sysdate (like you have already)
create a calculation: rank() over (partition by product order by order_date)
then create a filter in that new calculation: rank() = 1.
This will only return only one row per product, last one before or on sysdate.
Regards,
Sabine
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
