Hi

I need to calculate a value based on dates being enterd by user
For Example the table data structure i slike this

AcctNo EntryDate Market Value
1 11/27/06 1000
1 11/28/06 2000
1 11/29/06 5000
1 11/30/06 1500
2 11/29/06 1000
2 11/30/06 2500
2 12/01/06 3000
3 11/28/06 4000
3 11/29/06 1000
3 11/30/06 2000

Where entrydate = date entered by the user

When user enters 11/30/06
the output should be like this
AcctNo EntryDdate Market Value
1 11/30/06 -3500
2 11/30/06 1500
3 11/30/06 1000

Market value is calculated by subtracting the previous row market value for same AcctNo from the entrydate row market value(which is entered by the user)
If no previous row is avialable before 11/30/06 it will display the same value as of 11/30/06 in output.

Umar

I remember you posted the same issue last month
What was wrong with the answers you have got at that post

quote:

Posted July 11, 2007 03:42 PM
i have a column called entry date which captures daily date details like account.
i need to find the difference between todays date and yesterdays date amount details.
final amount=
amount with entry date of today - amount with entry date of yesterday

please help me out

https://forums.informationbuilders.com/eve/forums/a/tpc/...971028632#8971028632


We would like to help, but al the answers are there already

Hi Frank
Solution that we got works fine only for 2 rows of data.Sinc ein my code i have sorted and selected by Highest 2 dates.
If there are more than 2 rows then it will come different.So only side by side i am also trying out but also looking for some help. I am posting the code which we used last for our calculations

:::CODE:::
SELECT
ASSET_ID,
FULL_MARKET_VALUE,
ENTRY_DATE
FROM MurexODS.ACCOUNTING_BALANCES
;
-*
-*STORING OUTPUT IN A TABLE
TABLE FILE SQLOUT
SUM FULL_MARKET_VALUE
BY HIGHEST 2 ENTRY_DATE
BY ASSET_ID
ON TABLE HOLD AS HOLDNEW
END
-RUN
-*
-*STORING OUTPUT IN A NEW TABLE FOR COMPUTATION
TABLE FILE HOLDNEW
PRINT
FULL_MARKET_VALUE
COMPUTE CURR_MARKET_VALUE/D20.2 = IF ASSET_ID EQ LAST ASSET_ID
THEN FULL_MARKET_VALUE - LAST FULL_MARKET_VALUE ELSE FULL_MARKET_VALUE;
BY ASSET_ID
BY ENTRY_DATE
ON TABLE HOLD AS NEWFILENAME
END
-RUN
-*
-*STORING OUTPUT IN A NEW TABLE FOR RESTRICTION
TABLE FILE NEWFILENAME
PRINT
CURR_MARKET_VALUE
BY HIGHEST 1 ENTRY_DATE NOPRINT
BY ASSET_ID
ON TABLE HOLD AS NEWFILENAME1
END
-RUN
-*

quote:

BY HIGHEST 2 ENTRY_DATE
BY ASSET_ID

I think this should be

BY ASSET_ID
BY HIGHEST 2 ENTRY_DATE

I wonder how you can get more than two rows since you ask for the highest 2.

Is your date format a real date? That might be important for the sorting.

Hi Frank

We donot want highest 2 rows now. we just need to subtract the current row value from the previous row value depending upon the date being entered by the user.

That's nice Umar

But to come to the end result you have to make small stepps and this is one of them.
You want to find the actual value and the previous value, so by running the program sorted on the highest two you get the highest and the previous record.

The next step is to calculate the difference of these two values.

If you want this done based on a day entered by the user you restrict the program with

WHERE ENTRY_DATE LE '&Entered_date';