April 29, 2003 at 11:53 am
Hi everyone!
1. - I'm making a dynamic list of the last 24 indexes of the Brazilian corresponding to "Consumer Price Index" (in DESCendent order of months). And in the SQL Server database column "Indexes", each row will store each monthly index, beside the corresponding month/year.
So, as time goes by, each month a new index is INSERTed in the DB, and the former "1st." index (corresponding to the "last" month) becomes the "2nd." index, and the program will eliminate the former "24th." index, and so on.
Well - in order to give it more "appeal", the page, in my site -- all built in ASP --, will display another column where the program must show the variation, the % increment of cost from one month to another.
The question is -- HOW can I dynamically make the program calculate the % variation from the data stored in the row "17" of the column "Indexes" (let's say, for instance, that it corresponds to "FEB2003") to the data stored in row "18" (corresponding to "MAR2003" index)?
In other words: HOW can I "identify" a data stored in a said column-and-row, in order to divide it, or multiply it with the data stored in another column-and-row? How can be built this statement?
2. - ...and, without boring You Gurus, would You be kind in giving me an other piece of help: I want to "normalize" the displaying of the indexes, showing all of them with 04 (four) decimal digits. And that one certain index is (for instance) "123.4" - or - the result of the division os 02 indexes makes "123.456". HOW can I force the program to display ALLWAYS 04 (four) decimal digits?
Thanks, many thanks in advance, for your kind attention!
Dalton H I Gilson, Rio
April 29, 2003 at 12:42 pm
As for number 2:
I would do the formatting of the decimal in your ASP code. But if you really NEED to do it in SQL, you must do a CONVERT or CAST around the result of your division statement, as in:
SELECT CONVERT(decimal(4,1), (@d1 / @d2))
As for number 1, you can have the INSERT statement which inserts the next index in the indexes table do the calculation during execution, for instance:
assume the following DDL for indexes table:
CREATE TABLE BrazilIndex
(
IndexID INT NOT NULL ,
IndexValue DECIMAL(4,1) NOT NULL,
VarianceFromLast DECIMAL(4,1) NOT NULL
)
DECLARE @LastIndexValue DECIMAL(4,1)
DECLARE @NextIndexID INT
DECLARE @NextIndexValue DECIMAL(4,1)
SET @NextIndexID = 18
SET @NextIndexValue = 125.5
SELECT TOP 1 IndexValue = @LastIndexValue FROM BrazilIndex ORDER BY IndexID DESC
INSERT INTO BrazilIndex (IndexID , IndexValue)
VALUES (@NextIndexID , @NextIndexValue , (@LastIndexValue - @NextIndexValue))
Hope this gets you started, and hope I got your situation correct ?!
Jay
April 30, 2003 at 4:58 am
Jay
Thanks a lot for your attention.
- As for nr. 2, I'd like to try the ASP option You said: probably it's simpler! Would You be kind in telling me the way? TIA.
- As for nr. 1, I'll (try to) implement it.
Thanks again
Dalton
May 5, 2003 at 6:17 am
Here's the link you'll need. Sorry for the wait...was on holiday...
http://www.devguru.com/Technologies/vbscript/quickref/formatnumber.html
May 5, 2003 at 8:55 am
JPipes
1. - thanks for your (more one!) help, with formatting numbers.
2. - it's really simpler!, but...
3. - considering that the web page will have a loop, displaying the last 24-month indexes -- all, with 04-digit decimals (no matter how many real decimals each number has) --, I must assume my nerd-side, and ask You:
- HOW can I "generalize" this VBScript ("<% =FormatNumber... %>"), for the page to display, correctly, whichever number the loop brings? (I confess You that a "blank" came to my mind, and I did not reach how to do it!!!)
Thanks, JPipes, once more!
Dalton
May 5, 2003 at 9:01 am
Go ahead and post your ASP script and I will take a look...
May 5, 2003 at 9:15 am
JPipes
Please do not consider my last demand (!), because the "blank" is already gone!!!:
- in the loop, where I'd put "objRS("indexnr")", now I put "FormatNumber(objRS("indexnr"), 4, -1)".
And - wow! - it works!
Thanks really a lot for your kind attention!
Sincerely yours
Dalton, Rio
May 5, 2003 at 9:24 am
No problem.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply