Solved

Format Calculation as whole number not a decimal SQL 2012

Posted on 2016-08-30
5
41 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 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]

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

623 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