Solved

SQL rounding function help

Posted on 2014-11-14
4
194 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 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

724 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