Solved

Format Calculation as whole number not a decimal SQL 2012

Posted on 2016-08-30
5
35 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
  • 2
  • 2
5 Comments
 
LVL 65

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 65

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

832 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