-
Date Range Problem
Hello!
I'm quite new to Analysis Services.
I have a problem i can't solve.
Scenario:
A car rental company registers the history of each car. for each record there are two date fields, StartDate and ReturnDate.
A record in the historical table is:
Car Mercedes C200
Customer Company1
StartDate 12/12/2003
ReturnDate 01/12/2004
Quantity 3
This means that the customer rented 3 mercedes c200 from 12/12/2003 to 01/12/2004
Then, If I want to know at the end of Q4-2003 how many cars where rented in T-SQL I should write
SELECT….. FROM ……
WHERE startdate<=12/31/2003 and (returndate >=31/12/2003 or returndate is null)
Now I built a cube based on that table with the following dimensions and measures:
Dimension:
CarModel
Customer
StartDate
ReturnDate
Measures
Quantity
The question is: if I use as point of my analysis Q4-2003 and I want to see at that time how many car I rented, how can I filter or select the subset of the cars rented before Q4-2003 and returned after Q4-2003 (or not returned yet)?
Carmodel quantity start return
Mer c200 2 may-2003 feb-2004
Mer c200 6 dec-2003 mar-2004
If in the cube browser I try to filter the startime selecting q4-2003 my result will be just 6 but I had other to cars out rented before (then it should be 8).
I should be able to have the possibility to dynamically select the analysis point because the cube is browsed by crystal reports, so I have to insert something in the cube structure, like….calculated member?
Thanks in advance.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|