Solved

Format Calculation as whole number not a decimal SQL 2012

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

685 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