March 21, 2007 at 4:07 pm
Hello everyone,
When our financial data warehouse gets out of balance, we rebuild the month that is out of balance. When we are finished rebuilding, we do a little check to see if the assets + equity + liabilities add up to 0 and to do that we run a query similar to this:
CREATE TABLE [#tmpSumTest] (
[Number] [float] NULL ,
) ON [PRIMARY]
GO
INSERT INTO #tmpSumTest
VALUES (256788735.79999971) -- Pretend Assets for this example
INSERT INTO #tmpSumTest
VALUES (-58264092.309999987) -- Pretend Equity
INSERT INTO #tmpSumTest
VALUES (-198524643.48999953) -- Pretend Liabilities
SELECT * FROM #tmpSumTest
COMPUTE sum(number)
Which gives us a result set similar to:
Number
-----------------------------------------------------
256788735.79999971
-58264092.309999987
-198524643.48999953
sum
=====================================================
1.7881393432617188E-7
Is it possible to format the compute clause (something like Round(COMPUTE sum(number),2))) and get 0 as my result instead of E-7? I've tried round and cast, but I get an error when I use them (and perhaps I'm not using the functions correctly).
Thanks for your time...
-Scott
March 21, 2007 at 5:24 pm
First off, I'd recommend not using float for real money. Float is an approximate data type and not very good for keeping dollars and cents straight. Are the data types for the columns you're summing really float?
I'd go with a large numeric like numeric(19,2) or numeric(19,6) if you're working with stock prices and convert the data before the sum, or numeric(25,8) if the example data is truely representative.
CREATE
TABLE [#tmpSumTest] (
[Number]
numeric(25,8) NULL ,
)
ON [PRIMARY]
GO
INSERT
INTO #tmpSumTest
VALUES
(256788735.79999971) -- Pretend Assets for this example
INSERT
INTO #tmpSumTest
VALUES
(-58264092.309999987) -- Pretend Equity
INSERT
INTO #tmpSumTest
VALUES
(-198524643.48999953) -- Pretend Liabilities
SELECT
convert(numeric(25,2),round(sum(number),2)) FROM #tmpSumTest
March 22, 2007 at 6:49 am
the answer is no. and you are better off doing
select sum() from, as suggested above, but cast to money at the end.
March 22, 2007 at 7:43 am
If the base table is small, you can do this:
SELECT *
FROM #tmpSumTest
UNION ALL
SELECT CAST(SUM(number) AS DECIMAL(19, 8))
FROM #tmpSumTest
If not small, you can make the main query a subquery, adding a dummy column, which you can group on to get a formatted total. More on that if really needed 🙂 .
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 22, 2007 at 10:18 am
Thanks Gentlemen!
To answer your questions, the base table contains millions of records but the query results contains only 3 records, so I inserted those three records into a temp table. Then I used David and Camilo suggestion to select from that temp table and format the result.
David, you asked if we were using float data types for our monetary values. Unfortunately, we do. They have been around since somewhere around '98 or '99 (well before my time) and I'm not sure if I'll get the opportunity to correct them when we finally migrate to SQL2005.
I appreciate all your help, guys, and I hope that you all have a terrific day!
-Scott
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply