Solved

SQL rounding function help

Posted on 2014-11-14
4
167 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
  • 2
  • 2
4 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 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:ScottPletcher
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 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

13 Experts available now in Live!

Get 1:1 Help Now