Hello,
I have a question about how to make a report. My database follows star schema. Almost all the information necessary is in one table. There are a couple of other tables but they hold very little information.
Now I need to make a report to bring up the history. For eg, Previous transactions for a customer who rents out DVD's.
My table has 4-5 records for each customer. One record for each transaction.
The report needs to be written as follows. You encounter a prompt for the in_range date first. And after you select, It shows you the transaction for that period, which is one record for each customer. This transaction shows the name of the DVD, the ID, date when the DVD was checked out and when it was returned. Now, I want to add a few more columns that show the date when the previous DVD was checked out, Name and ID of the DVD and the expected return date.
All this information is actually in one table. Each customer has multiple records.
so the format goes something like--
DVD ID, DVD NAME, DATE_CHECKED OUT, DATE_EXPECTD RET, and a few more columns like
PREV DVDID, PREV DVD NAME, PREV DATE CHECKEDOUT, PREV DATE EXPECTED TO RETURN.
We are using ReportNet.
Any help is greatly appreciated. Thank you very much.
If all the data is in one table, then it is not a Star Schema. That sounds like a denormalizzed reporting table, or Materialized View.
To get the effect you want, I would consider 2 queries... one that is the current rental and anther that is the prior rental. Then UNION those together.
Similar columns, ie DVD_ID and PREV_DVDID need to be aligned in the Union.
hth...
Thank you for the response. But if I have to retrieve the previous transaction for before the lowest date in the date range, how can I accomplish this? Sorry if this is a basic question. But I am new to this.
Look at using the Maximum() function on the rental date to get the last rental,
and use the From Date part of the date range to limit the rows in the previous rental query, by using it as the To Date.