0

In my SQL Server 2014, I have this SQL query :

SELECT
    MIN (dbo.GetActualValue(Price_Value, Discount)) AS ActualPrice, 
    IDHOTEL
FROM
    HotelRoom
GROUP BY
    IDHOTEL

dbo.GetActualValue  => return @Price_Value - ((@Discount * @Price_Value) / 100)

How I can select Price_Value, Discount at the MIN value ?

I need to calculate the price after discount (actual price), find out the minimum price after calculation (min actual price) I need to select the original price, discount at the place with the smallest actual price

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pham van phuong
  • 1
  • 1
  • 1
    You should inline `dbo.GetActualValue` otherwise your query won't be parallelizable (using `INLINE = ON` if you're on SQL Server 2019). – Dai Nov 22 '22 at 03:01
  • thank for reply me, but I am using sql 2014 – Pham van phuong Nov 22 '22 at 03:08
  • 1
    Actually, regardless, scalar functions are pretty sucky in SQL Server and should generally be avoided. Like Dai mentioned, your query (or any consumers of it) won't be able to be parallelized. You should just write the logic directly in your query. Use Views or Procedures to refactor and re-use code. – J.D. Nov 22 '22 at 03:22
  • Also it's not super clear what you're stuck on? Your query should do what you said you want it to do. – J.D. Nov 22 '22 at 03:36
  • i need to calculate the price after discount(actual price), find out the minimum price after calculation(min actual price) I need to select the original price, discount at the place with the smallest actual price – Pham van phuong Nov 22 '22 at 03:49
  • @Phamvanphuong https://stackoverflow.com/questions/15291506/sql-query-to-select-distinct-row-with-minimum-value – Dai Nov 22 '22 at 04:26
  • @Phamvanphuong `How I can select Price_Value, Discount at the MIN value?` min value of what? ActualPrice? or Price_Value? – RF1991 Nov 22 '22 at 05:37

1 Answers1

0

what you need is Subquery and Max or Min function

SELECT H.IDHOTEL,H.Price_Value,H.Discount,B.ActualPrice
FROM 
(SELECT dbo.GetActualValue(Price_Value, Discount) AS ActualPrice,Discount,Price_Value    
IDHOTEL FROM HotelRoom) H
JOIN 
(SELECT Min(dbo.GetActualValue(Price_Value, Discount)) AS ActualPrice,    
IDHOTEL FROM HotelRoom
GROUP  BY IDHOTEL) B
H.IDHOTEL=B.IDHOTEL AND B.ActualPrice=H.ActualPrice
RF1991
  • 2,037
  • 4
  • 8
  • 17