Using MOD function on MONEY field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Using MOD function on MONEY field

Author

Topic

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-04 : 06:48:13

Hi There,

I currently have a table in an SQL Server 2000 database with a number of 'Money' fields.

The first, 'Cost', contains the total yearly value of the contract. There are then 4 QTR fields (QTR1, QTR2....) which each contain the amount owed for the quarterly payments.

So basically, I need to divide the 'Cost' field by 4 to find the quarterly payment amounts.

However, if the 'Cost' field does not divide exactly by 4 (or 0.04) then the remainder will be added to the 4th quarter payment. So the fourth quarter may be 0, 1, 2 or 3 pence more than the first 3 quarters.

In order to do this I assume the best way is to use the 'mod' function to find the remainder.....however I'm having trouble doing this, I assume, due to the data type being 'money' rather than 'numeric'.

My code currently looks like this:

Cost=(Appliances.Fields.Item("Cost").Value)

Remainder = (Cost mod 0.04)
Cost = (Cost-Remainder)
QTRS = (Cost/4)
FourthQTR = (QTRS+Remainder)

I have tried to convert 'Cost' to numeric with no success so far......any ideas??

Thanks!!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-04 : 06:55:16

there is no MOD function in SQL ...you can use % operator to solve your purpose !

Harsh Athalye
India.
"Nothing is Impossible"

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-04 : 06:56:04

something like
Remainder = (convert(int,Cost*100) % 4)/100.0

Decide on how accurate you want it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-04 : 07:01:38

quote:
Originally posted by harsh_athalye

there is no MOD function in SQL ...you can use % operator to solve your purpose !

Harsh Athalye
India.
"Nothing is Impossible"

ummm.....sorry.....I was trying to do this in ASP after importing the 'Cost' value from the database.....or would it be better to do it in the SQL statement?

Remainder, Cost, QTRS and FourthQTR are all ASP variables

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-04 : 07:12:38

UPDATE MyTable
set Qt1 = Cost / 4,
Qt2 = Cost / 4,
Qt3 = Cost / 4
Qt4 = Cost - 3.0 * Cost / 4
WHERE .....

Peter Larsson
Helsingborg, Sweden

Gyto
Posting Yak Master

144 Posts

Posted - 2006-10-04 : 07:25:03

quote:
Originally posted by Peso

UPDATE MyTable
set Qt1 = Cost / 4,
Qt2 = Cost / 4,
Qt3 = Cost / 4
Qt4 = Cost - 3.0 * Cost / 4
WHERE .....

Peter Larsson
Helsingborg, Sweden

The only problem with this is that I'd need to use the ROUND function on each of the quarters in order to make them accurate to 2 decimal places. This caused problems with the accuracy of the values when I tried it this way, as the quarters did not add up to the EXACT total cost value....so I thought I'd use the MOD function in ASP....

Is it not just possible to import the Cost value from the database, convert it to numeric, use the mod function on it and then convert the values back to 'money' when inserting into the database?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-04 : 07:38:06

[code]DECLARE @Test TABLE (QT1 SMALLMONEY, QT2 SMALLMONEY, QT3 SMALLMONEY, QT4 SMALLMONEY)

INSERT @Test
SELECT 1,
NULL,
NULL,
NULL
UNION ALL
SELECT 2,
NULL,
NULL,
NULL

DECLARE @Cost FLOAT

SELECT @Cost = 124.73

UPDATE @Test
SET QT1 = ROUND(@Cost / 4, 2),
QT2 = ROUND(@Cost / 4, 2),
QT3 = ROUND(@Cost / 4, 2),
QT4 = @Cost - 3 * ROUND(@Cost / 4, 2)
WHERE QT1 = 1

SELECT @Cost = 124.71

UPDATE @Test
SET QT1 = ROUND(@Cost / 4, 2),
QT2 = ROUND(@Cost / 4, 2),
QT3 = ROUND(@Cost / 4, 2),
QT4 = @Cost - 3 * ROUND(@Cost / 4, 2)
WHERE QT1 = 2

SELECT * FROM @Test[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources