Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Format Calculation as whole number not a decimal SQL 2012

Posted on 2016-08-30
5
Medium Priority
?
44 Views
Last Modified: 2016-08-30
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]

Open in new window


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

Thank you
0
Comment
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
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 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]

Open in new window

0
 

Author Comment

by:mburk1968
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 66

Expert Comment

by:Jim Horn
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%] ,

Open in new window


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

Open in new window

0
 

Author Closing Comment

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

Expert Comment

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

Open in new window

0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question