CODEWITHSUNDEEP
sql-serversql-server-2014sql-function
biggboss2019
biggboss2019

Reputation: 300

Calculating MINIMUM value based on two SELECT statement in Single SQL FUNCTION (SQL Server 2014)

I am trying to create a single SQL FUNCTION, where if I input my "a_id". SQL should return me MINIMUM Cost based on 3 different c_id's. My Tables are as follow.

TB1 : A (a_id (pk),a_name,a_description,a_qh)
TB2:  AC (ac_id (pk),a_id(fk),c_id(fk),ac_cost)
TB3: C (c_id(pk),c_name)

As an Example: if I have below table and I input a_id of 1 then SQL should be able to find all c_id associated with a_id of 1. Then based on that SQL should be able to calculate minimum value based on cost. For below example of a_id =1 SQL should return me c_id of 1 since it is with lowest cost of 150 out of 150,155, and 160 :

 a_id    a_description         c_id   Cost
    1   RS                      1   150.0000
    2   BS                      1   145.0000
    3   GS                      1   130.0000
    1   RS                      2   155.0000
    14  GH                      2   120.0000
    1   RS                      3   160.0000
    14  GH                      3   125.0000

I have created two SELECT statements achieve this:

Statement1:

SELECT 
    c_id, Cost, a_id 
FROM
    AC 
WHERE
    a_ID = 1);    -- This code returns all c_id associated with one  a_id

Statement 2:

SELECT
    MIN(AC.cost), c_id, a_ID
FROM
    AC
WHERE
    a_ID = 1
GROUP BY 
    c_id, a_ID
HAVING 
    MIN(AC.COST) <= (SELECT MIN(AC.COST) FROM AC)   -- This code calculates minimum cost based on 3 different c_id. AS 1 a_id can be sell be 3 different c_id

My dilemma is: how should I use above two SELECT statements in single function to achieve the lowest ac.Cost?

Thanks in advance!

Upvotes: 0

Views: 129

Answers (1)

uh_big_mike_boi
uh_big_mike_boi

Reputation: 3470

I dont think you need other tables based on what im seeing, just AC. Here is a rank query 

select * 
from 
    (select 
         *, 
         rank() over (partition by a_id order by cost asc) as cost_rank 
     from 
         AC) a 
where 
    cost_rank = 1 and a_id = 1

Upvotes: 1

Related Questions