SQL rounding function help

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
LVL 1
abarefootAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
abarefootAuthor Commented:
Thanks Scott I think this did the trick.  Not sure I understand how it works but will study up on CROSS APPLY.
0
abarefootAuthor Commented:
I'm having someone verify the data but it looks good to me.  Thanks for your help!
0
Scott PletcherSenior DBACommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.