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

Umar,

Sort by your date field then you can create the following:

COMPUTE FINAL_AMT/D12.2=AMOUNT - LAST AMOUNT;

Fernando

ASSET_ID FULL_MARKET_VALUE ENTRY_DATE
44 -13,482,320.00 2006/11/30
44 -13,521,531.25 2006/12/1
45 -205,000.00 2006/11/30
45 -205,390.63 2006/12/1

see here FULL_MARKET_VALUE has to be subtracted
based on the entrydate for each assetid.
we should have only one value for FULL_MARKET_VALUE after calculation

Umar,

Fernando gave you the idea towards your solution but you have to at least try to apply it.

Also, and this applies to all, please update your signature with your release, platform and usage so we have a good idea on what to advise. Kathleen has raised a post on this and it has been mentioned many times in the last few days, so help us to help you by complying.

Try this -

TABLE FILE filename
PRINT ENTRY_DATE
      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
END

T

Well that works fine Tony but
if there are many rows with same asset_id since entry dates are daily dates??
user selects a entry date and final market value should be calculated by subtracting the market value of the selected entry date with previous entry date that exists in the table for a particular asset_id.

44 -13,482,320.00 2006/11/30
44 -13,521,531.25 2006/12/1
44 -13,521,438.75 2006/12/3
44 -13,521,135.25 2006/12/4
45 -205,000.00 2006/11/30
45 -205,390.63 2006/12/1
45 -205,390.56 2006/12/3
45 -205,390.78 2006/12/4

if user selects entry date as 2006/12/3 final market value=[(-13,521,438.75)-(13,521,531.25)]
final market value=
[(market value as on selected date(i.e.2006/12/3))-(market value as on previous date 2006/12/1)]

if no previous date display the same market value

Umar

-DEFAULT &ENTRYDATE='2006/12/03';
TABLE FILE filename
SUM FULL_MARKET_VALUE
BY HIGHEST 2 ENTRY_DATE
BY ASSET_ID
WHERE ENTRY_DATE LE '&ENTRYDATE'
ON TABLE HOLD

TABLE FILE HOLD
PRINT ENTRY_DATE
      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
END 

This will give you the only the data from the selected date and the previous date.

I hope however that the date field in the database is indexed, since otherwise this report can take a long time.

PLEASE HAVE ALOOK AT MY CODE BELOW..BUT I HAVE GIVEN THE ERROR AT LAST

-SET &ECHO = ALL;
-SET ASNAMES=ON
-DEFAULT &OracleConnect='ora022';
-DEFAULT &STARTDATE='11/30/2006';

SQL SQLORA SET SERVER &OracleConnect
SQL SQLORA
SELECT
ASSET_ID,
FULL_MARKET_VALUE,
ENTRY_DATE
FROM MurexODS.ACCOUNTING_BALANCES
-*WHERE ENTRY_DATE = TO_DATE('&STARTDATE','MM/DD/YYYY')
;

TABLE FILE SQLOUT
SUM FULL_MARKET_VALUE
BY HIGHEST 2 ENTRY_DATE
BY ASSET_ID
WHERE ENTRY_DATE LE '&STARTDATE'
ON TABLE HOLD
END
-RUN

TABLE FILE HOLD
PRINT ENTRY_DATE
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
ON TABLE HOLD AS NEWFILENAME
END
-RUN
-*
TABLE FILE NEWFILENAME
PRINT *
END
-EXIT

::::ERROR GOT::::

0 ERROR AT OR NEAR LINE 6 IN PROCEDURE SQLOUT FOCEXEC *
(FOC177) INVALID DATE CONSTANT:
(FOC009) INCOMPLETE REQUEST STATEMENT
TABLE FILE HOLD
PRINT ENTRY_DATE
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
ON TABLE HOLD AS NEWFILENAME
END
-RUN
0 ERROR AT OR NEAR LINE 59 IN PROCEDURE MEMFEX FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: ENTRY_DATE
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
-*
TABLE FILE NEWFILENAME
PRINT *
END
-EXIT
0 ERROR AT OR NEAR LINE 77 IN PROCEDURE MEMFEX FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: NEWFILENAME
BYPASSING TO END OF COMMAND

Umar

in the coding part

SQL SQLORA
SELECT
ASSET_ID,
FULL_MARKET_VALUE,
ENTRY_DATE
FROM MurexODS.ACCOUNTING_BALANCES
-*WHERE ENTRY_DATE = TO_DATE('&STARTDATE','MM/DD/YYYY')
;

The line starting with -* gives you the problem.
you are mixing webfocus scripting with sql
the comment in webfocus starts with -* but in SQL it should be "rem" I think.
Just place the ";" one line higher and it might work.

You can also create a master that describes the Oracle database. Than you do not have to write SQL scripting and let Focus do that job for you. Much easier IMO.

Umar,

We use -* in our SQL sections of code all the time. This is one of the way we dynamically build all of our SQL. WF parses it out. I would look for a different issue.

Kevin

comment is not a problem that works fine

when i comment the below two statements i get the output with 2 rows

WHERE ENTRY_DATE = TO_DATE('&STARTDATE','MM/DD/YYYY')
WHERE ENTRY_DATE LE '&STARTDATE'

::::utput::::::::
ASSET_ID ENTRY_DATE FULL_MARKET_VALUE ASSET_ID CURR_MARKET_VALUE
44 12/1/2006 -13,521,531.25 44 -13,521,531.25
44 11/30/2006 -13,482,320.00 44 -39,211.25
45 12/1/2006 -205,390.63 45 -205,390.63
45 11/30/2006 -205,000.00 45 -390.63
46 12/1/2006 -9,653,359.42 46 -9,653,359.42
46 11/30/2006 -9,635,000.00 46 -18,359.42
47 12/1/2006 -106,468.75 47 -106,468.75
47 11/30/2006 -106,160.00 47 -308.75

but instead of two rows i need only one row which contains the calculate(subtracted)Market_Value

Also,

You may need to use &STARTDATE.EVAL, or do a;

 
(DATECVT(&STARTDATE , I8YYMD, YYMD)
 

to perform the date comparison.

Kevin

Just put the result again into a hold file. (HOLD2)

TABLE FILE HOLD2
PRINT *
BY HIGHEST 1 ENTRY_DATE NOPRINT
BY ASSET_ID
END

quote:

TABLE FILE HOLD2
PRINT *
BY HIGHEST 1 ENTRY_DATE NOPRINT
BY ASSET_ID
END

i get correctly when i change highest to lowest.
but i get an additional column in my output please refer below
:::CODE:::
TABLE FILE NEWFILENAME
PRINT CURR_MARKET_VALUE
BY LOWEST 1 ENTRY_DATE NOPRINT
BY ASSET_ID
ON TABLE HOLD AS NEWFILENAME1
END
-RUN

:::OUTPUT:::
ASSET_ID CURR_MARKET_VALUE RNK.ENTRY_DATE
44 -39,211.25 1
45 -390.63 1
46 -18,359.42 1
47 -308.75 1
48 -110,223.75 1

I DO NOT WANT THIS COLUMN TO APPEAR IN MY OUTPUT

Umar

you seem to have two problems

1) is the number 1 in your report.

2) is the sorting highest and lowest.

The first problem has something to do with your date format.
A date format D/M/YY sorts in the wrong sequence.
As you will understand the first of december is a higher date than de last day of november.

Change your day format (internally) to either a real number (number of days since 1 jan 1900) or to the format YY/M/D.
Then you can use the BY HIGHEST 1 ENTRY_DATE

I have to think about your other problem.
maybe someone else know why the rnk.entry_date is printed.

If you leave out the "noprint" what do you get then, I suppose only the date.

Umar

in stead of "by highest (or lowest) 1 entry_date"

you can say

TABLE FILE HOLD2
PRINT CURR_MARKET_VALUE
BY ASSET_ID
WHERE ENTRY_DATE EQ '&ENTRY_DATE';
END

The &Entry_date is the by the users entered date.

when i executed the below code i got positve value.so interchanged the THEN statement to
THEN FULL_MARKET_VALUE - LASTFULL_MARKET_VALUE ELSE FULL_MARKET_VALUE;
i got CURR_MARKET_VALUE in negative which i wanted bcos FULL_MARKET_VALUE of today minus FULL_MARKET_VALUE of yesterday both are in negative

::CODE::

quote:

TABLE FILE HOLD
PRINT ENTRY_DATE
FULL_MARKET_VALUE
COMPUTE CURR_MARKET_VALUE/D20.2 = IF ASSET_ID EQ LAST ASSET_ID
THEN LASTFULL_MARKET_VALUE - FULL_MARKET_VALUE ELSE FULL_MARKET_VALUE;
BY ASSET_ID
ON TABLE HOLD AS NEWFILENAME
END
-RUN

so only i also changed the HIGHEST TO LOWEST in below code
TABLE FILE NEWFILENAME
PRINT CURR_MARKET_VALUE
BY LOWEST 1 ENTRY_DATE NOPRINT
BY ASSET_ID
ON TABLE HOLD AS NEWFILENAME1
END
-RUN

if you can suggest me a better way to change the date and try it plese do help me out

Umar

I think this question is not solved, but you are getting more and more confused.

Let's start over and begin with posting your master on this table request.

I also wonder if the data you are using for this request is a basic table or a compound data report.

So start with posting the master file you want to use and then I can lead you step by step to the final result.