Solved

# Format Calculation as whole number not a decimal SQL 2012

Posted on 2016-08-30
39 Views
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
Question by:mburk1968
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 65

Accepted Solution

Jim Horn earned 500 total points
ID: 41776382
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 Comment

ID: 41776406
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

LVL 65

Expert Comment

ID: 41776418
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 Closing Comment

ID: 41776424
Thank you! I was able to use what you sent me.
0

LVL 2

Expert Comment

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differeâ€¦
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
###### Suggested Courses
Course of the Month8 days, 3 hours left to enroll