Solved

SQL rounding function help

Posted on 2014-11-14
4
172 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:
Scott Pletcher 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: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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
insert into table 8 22
Simple SQL query from two tables 13 53
SSIS with VPN COnnection 2 70
Microsoft Access Write errors seem to be caused by bit fields 4 36
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

776 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