Is it possible to format a COMPUTE clause?

  • 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

  • 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


    And then again, I might be wrong ...
    David Webb

  • the answer is no. and you are better off doing

    select sum() from, as suggested above, but cast to money at the end.

     

  • 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".

  • 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