?
Solved

SQL rounding function help

Posted on 2014-11-14
4
Medium Priority
?
204 Views
Last Modified: 2014-11-14
One of our users gave me the following excel function that they want for the column "New FSP"
2014-11-14-12-15-37.jpg
I'm not sure how to do the rounding part.  Below is my script which works fine but need the rounding added for the column "New FSP" which is my last select in the script below.

SELECT DISTINCT
                      price_book.description AS book_desc, price_page.description, inv_mast.item_id, inv_mast.item_desc, inventory_supplier.supplier_id,
                      inventory_supplier_x_loc.primary_supplier, supplier.supplier_name, inventory_supplier.cost, inventory_supplier.list_price,
                      price_page.price AS fsp_price, price_family.price_family_id, inventory_supplier.supplier_sort_code as last_cost,

case when convert(decimal(20,2),inventory_supplier.supplier_sort_code) = 0 THEN 0 ELSE
(inventory_supplier.cost - CONVERT(decimal(20,2),inventory_supplier.supplier_sort_code)) / CONVERT(decimal(20,2),inventory_supplier.supplier_sort_code)

END as last_new_cost_diff,



case when inventory_supplier.list_price >0 then (price_page.price +
(
case when convert(decimal(20,2),inventory_supplier.supplier_sort_code) = 0 THEN 0 ELSE
(inventory_supplier.cost - CONVERT(decimal(20,2),inventory_supplier.supplier_sort_code)) / CONVERT(decimal(20,2),inventory_supplier.supplier_sort_code)
END
))
Else
(price_page.price + case when convert(decimal(20,2),inventory_supplier.supplier_sort_code) = 0 THEN 0 ELSE
(inventory_supplier.cost - CONVERT(decimal(20,2),inventory_supplier.supplier_sort_code)) / CONVERT(decimal(20,2),inventory_supplier.supplier_sort_code)
END) end as new_fsp_price




FROM         price_family INNER JOIN
                      inv_loc ON price_family.price_family_uid = inv_loc.price_family_uid RIGHT OUTER JOIN
                      inventory_supplier INNER JOIN
                      inv_mast ON inventory_supplier.inv_mast_uid = inv_mast.inv_mast_uid INNER JOIN
                      price_page ON inv_mast.inv_mast_uid = price_page.inv_mast_uid INNER JOIN
                      supplier ON inventory_supplier.supplier_id = supplier.supplier_id INNER JOIN
                      price_page_x_book ON price_page.price_page_uid = price_page_x_book.price_page_uid INNER JOIN
                      price_book ON price_page_x_book.price_book_uid = price_book.price_book_uid INNER JOIN
                      inventory_supplier_x_loc ON inventory_supplier.inventory_supplier_uid = inventory_supplier_x_loc.inventory_supplier_uid ON
                      inv_loc.inv_mast_uid = inv_mast.inv_mast_uid
WHERE  
(price_page.row_status_flag = '704') AND (price_book.description LIKE 'FSP%') and (LEFT(price_page.description,3)= LEFT(inventory_supplier_x_loc.location_id,3))
and (RIGHT(inventory_supplier_x_loc.location_id,2) = '00') and ( inventory_supplier_x_loc.primary_supplier = 'y') and supplier_sort_code <>'0'
ORDER BY price_page.description
0
Comment
Question by:abarefoot
[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
4 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40443346
I used a CROSS APPLY to do the existing computation to yield "new_fsp_price[_unrounded]" without regard to rounding.  Then, in the main SELECT, I get the final, rounded value of new_fsp_price:

SELECT DISTINCT
 ...
 END as last_new_cost_diff,
 --round to nearest 0.05
 FLOOR((new_fsp_price_unrounded + 0.02) / 0.05) * 0.05 AS new_fsp_price
FROM         price_family INNER JOIN
                       inv_loc ON price_family.price_family_uid = inv_loc.price_family_uid RIGHT OUTER JOIN
                       inventory_supplier INNER JOIN
                       inv_mast ON inventory_supplier.inv_mast_uid = inv_mast.inv_mast_uid INNER JOIN
                       price_page ON inv_mast.inv_mast_uid = price_page.inv_mast_uid INNER JOIN
                       supplier ON inventory_supplier.supplier_id = supplier.supplier_id INNER JOIN
                       price_page_x_book ON price_page.price_page_uid = price_page_x_book.price_page_uid INNER JOIN
                       price_book ON price_page_x_book.price_book_uid = price_book.price_book_uid INNER JOIN
                       inventory_supplier_x_loc ON inventory_supplier.inventory_supplier_uid = inventory_supplier_x_loc.inventory_supplier_uid ON
                       inv_loc.inv_mast_uid = inv_mast.inv_mast_uid CROSS APPLY (
                       select
                        case when inventory_supplier.list_price >0 then (price_page.price +
                         (
                         case when convert(decimal(20,2),inventory_supplier.supplier_sort_code) = 0 THEN 0 ELSE
                         (inventory_supplier.cost - CONVERT(decimal(20,2),inventory_supplier.supplier_sort_code)) / CONVERT(decimal(20,2),inventory_supplier.supplier_sort_code)
                         END
                         ))
                         Else
                         (price_page.price + case when convert(decimal(20,2),inventory_supplier.supplier_sort_code) = 0 THEN 0 ELSE
                         (inventory_supplier.cost - CONVERT(decimal(20,2),inventory_supplier.supplier_sort_code)) / CONVERT(decimal(20,2),inventory_supplier.supplier_sort_code)
                         END) end as new_fsp_price_unrounded
                      ) as assign_alias_names              
       
 WHERE  
 ...
0
 
LVL 1

Author Comment

by:abarefoot
ID: 40443395
Thanks Scott I think this did the trick.  Not sure I understand how it works but will study up on CROSS APPLY.
0
 
LVL 1

Author Closing Comment

by:abarefoot
ID: 40443439
I'm having someone verify the data but it looks good to me.  Thanks for your help!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40443549
You're welcome!

CROSS APPLY is normally used to invoke a table-valued function and include results from that into the result.  But, since its results are processed ahead of the main SELECT statement, it can also be used to assign alias names that can then be used throughout the rest of the query.

Later CAs can reference aliases from earlier CAs, so they can build on each other too ... how sweet is that!:

CROSS APPLY (
    SELECT <some_complex_expression> AS calc1
) AS ca1
CROSS APPLY (
    SELECT CHARINDEX('delimiter', calc1) AS calc1_delim_location --note use of "calc1" from prev. CA
) AS ca2
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Suggested Courses

777 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