Solved

Format Calculation as whole number not a decimal SQL 2012

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now