Localization of data in a certain column-and-row

Question

Post reply

Localization of data in a certain column-and-row

dig

SSC-Addicted

Points: 435
More actions
April 29, 2003 at 11:53 am

#82069

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

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

dig SSC-Addicted Points: 435 More actions · Answer 2

dig

SSC-Addicted

Points: 435

April 30, 2003 at 4:58 am

#455777

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

jpipes SSChampion Points: 12230 More actions · Answer 3

jpipes

SSChampion

Points: 12230

May 5, 2003 at 6:17 am

#456128

Here's the link you'll need. Sorry for the wait...was on holiday...

http://www.devguru.com/Technologies/vbscript/quickref/formatnumber.html

dig SSC-Addicted Points: 435 More actions · Answer 4

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