Here is my problem. I hope somebody could help me with the solution.
I want to show Current Day and Previous Day’s data in two separate columns in report. The data is stored in a single column in database.
The current date is entered by user via a prompt.
If the user selects 01/04/2004, the report should look like this.
Date Current Data Previous Data
01/04/2004 10 6
01/03/2004 6 8
01/02/2004 8 7
If the user selects 01\03\2004, the report should look like this.
Date Current Data Previous Data
01/03/2004 6 8
01/02/2004 8 7
01/01/2004 7 10
The database looks like this.
Date Data Value
01/04/2004 10
01/03/2004 6
01/02/2004 8
01/01/2004 7
31/12/2003 10
Any ideas how this can be achieved with a single data provider. Using two DPs will definitely reduce the performance.
You’ll have to create two flags, one called (for example) Current Day Flag and another for Previous Day Flag. Those will be based on a comparison between the data from your data provider and the prompt… if the day = prompt date then 1 for current, if relative_date(day, +1) = prompt then previous day flag = 1. Then you can use the Where clause with the two flags.
Using Plain SQL we can do something like this to get the desired result… See if you can make it for your use…
SELECT
DATEID,
DATEVALUE,
(SELECT DATEVALUE FROM TABLE1 A WHERE A.DATEID + 1 = TABLE1.DATEID)
FROM
TABLE1
WHERE
DATEID <= TO_DATE('03-JAN-2004')
AND DATEID >= TO_DATE('03-JAN-2004') - 2