Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 48

# Format Calculation as whole number not a decimal SQL 2012

I have the following calculation

``````SUM(CASE WHEN ( ord_date BETWEEN @WkStartDate AND @WkEndDate
AND DATA.line_status = 'I'
) --  CONDITIONAL
THEN ( DATA.total_qty )
ELSE ( 0 )
END) AS [Wk TY Unit Sales]
``````

My current results are 50.00, 42.00, 0.00, ETC. I need 50, 42, 0

Thank you
0
mburk1968
• 2
• 2
1 Solution

Microsoft SQL Server Developer, Architect, and AuthorCommented:
CAST the first THEN value in your CASE block as an int..

``````SUM(
CASE WHEN ord_date BETWEEN @WkStartDate AND @WkEndDate AND DATA.line_status = 'I' --  CONDITIONAL
THEN CAST(DATA.total_qty as int)    -- here
ELSE  0
END) AS [Wk TY Unit Sales]
``````
0

Author Commented:
Wrong Calculation sorry

SUM(CASE WHEN ( ord_date BETWEEN @WkStartDate AND @WkEndDate )
THEN ( DATA.total_qty / DATA.total_qty + INV.Size_Qty )
ELSE ( 0 )
END) AS [Wk TY U S/O%] ,
0

Microsoft SQL Server Developer, Architect, and AuthorCommented:
Same concept, cast the first THEN.. value as an int.

``````SUM(CASE WHEN ( ord_date BETWEEN @WkStartDate AND @WkEndDate )
THEN CAST(DATA.total_qty / DATA.total_qty + INV.Size_Qty  as int)
ELSE ( 0 )
END) AS [Wk TY U S/O%] ,
``````

For more info on CASE blocks check out SQL Server CASE Solutions

>Wk TY U S/O%
btw this is a poor choice of column names as the spaces and punctuation marks force the use of square brackets [ ] surrounding it, which is one thing to potentially forget and throw an error.

>DATA.total_qty / DATA.total_qty + INV.Size_Qty
btw you'll want to eyeball this to make sure you want the divide to occur before the add.  If not you'll have to use parentheses to set the order of precedence:

``````SELECT 1.00 / 2 + 3     -- Returns 3.5
SELECT (1.00 / 2) + 3   -- Returns 3.5
SELECT 1.00 / (2 + 3)   -- Returns .2
``````
0

Author Commented:
Thank you! I was able to use what you sent me.
0

Commented:
You can also use the Floor function to format your result.
Example
``````SELECT FLOOR(55.00)
``````
0

## Featured Post

• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.