Need to fetch Minimum value for Each Id and make the BidSuccess column to 1

Question

Need to fetch ID with min value and tendertype.

Below is the query:

drop table if exists #temp
create table #temp (ID int,TenderType nvarchar(100),BidAmount int,isAwardCarrier bit) 
Insert into #temp (ID , TenderType, BidAmount, isAwardCarrier)
VALUES
(1, 'Spot Bid', NULL, 0), 
(1, 'Spot Bid', NULL, 0), 
(1, 'Spot Bid', NULL, 0), 
(1, 'Spot Bid', NULL, 0), 
(1, 'Spot Bid', 390, 0), 
(1, 'Spot Bid', 345, 1), 
(1, 'Spot Bid', NULL, 0), 
(1, 'Spot Bid', 653.35, 0), 
(1, 'Ordinary', NULL, 1), 
(2, 'Spot Bid', NULL, 0), 
(2, 'Spot Bid', NULL, 0), 
(2, 'Spot Bid', NULL, 0), 
(2, 'Spot Bid', NULL, 0), 
(2, 'Spot Bid', 4150, 1), 
(2, 'Spot Bid', 3365, 0), 
(2, 'Spot Bid', NULL, 0), 
(2, 'Spot Bid', 3607.42, 0), 
(2, 'Ordinary', NULL, 1); 

select * from #temp

Expected Output

ID  TenderType  BidAmount       IsAwardCarrier  BidSuccess 
1   Spot Bid    NULL            0                   0 
1   Spot Bid    NULL            0                   0 
1   Spot Bid    NULL            0                   0 
1   Spot Bid    NULL            0                   0 
1   Spot Bid    390             0                   0 
1   Spot Bid    345             1                   1 
1   Spot Bid    NULL            0                   0 
1   Spot Bid    653             0                   0 
1   Ordinary    NULL            1                   1 
2   Spot Bid    NULL            0                   0 
2   Spot Bid    NULL            0                   0 
2   Spot Bid    NULL            0                   0 
2   Spot Bid    NULL            0                   0 
2   Spot Bid    4150            1                   0 
2   Spot Bid    3365            0                   1 
2   Spot Bid    NULL            0                   0 
2   Spot Bid    3607            0                   0 
2   Ordinary    NULL            1                   0 

Logic to calculate BidSuccess:

If the BidAmount is Minimum for the ID and IsAwarCarrier is 1 and for the same ID tenderType = Ordinary and IsAwardCarrier is 1 Then BidSuccess=1 for both the rows (Eg: ID=1)

If the BidAmount is Minimum and IsAwardCarrir is 0 then BidSuccess = 1 for that row. (Eg: ID =2)

Can someone please help with the code.

Answer 1

You can use window functions for this

SELECT
  t.ID,
  t.TenderType,
  t.BidAmount,
  t.IsAwardCarrier,
  CASE WHEN
          t.BidAmount = t.MinBidAmount
          AND (t.IsAwardCarrier = 0 OR t.HasOrdinary > 0)
        OR (t.TenderType = 'Ordinary' AND t.IsAwardCarrier = 1 AND t.MinAwardCarrier = MinBidAmount)
       THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END
    AS BidSuccess
FROM (
    SELECT *,
      MIN(t.BidAmount)
          OVER (PARTITION BY t.ID) AS MinBidAmount,
      MIN(CASE WHEN t.TenderType <> 'Ordinary' AND t.IsAwardCarrier = 1 THEN t.BidAmount END)
          OVER (PARTITION BY t.ID) AS MinAwardCarrier,
      MIN(CASE WHEN t.TenderType = 'Ordinary' AND t.IsAwardCarrier = 1 THEN 1 END)
          OVER (PARTITION BY t.ID) AS HasOrdinary
    FROM temp t
) t;

db<>fiddle

SQL Server 2008 doesn't have window functions, but if you are still on this version I strongly suggest you upgrade, because it's now completely out of date, insecure and unsupported.

Answer 2

select ID,TenderType,BidAmount, isAwardCarrier,'1'as bidsuccess from (
select ID,TenderType,BidAmount, isAwardCarrier from temp  
where isAwardCarrier=1group by ID,TenderType,BidAmount, isAwardCarrier 
having BidAmount=MIN(BidAmount  )    
union all
select ID,TenderType,BidAmount, isAwardCarrier from temp  
where isAwardCarrier=1 and TenderType='Ordinary' and ID in (select ID from temp  
where isAwardCarrier=1 group by ID,TenderType,BidAmount, isAwardCarrier 
having BidAmount=MIN(BidAmount  )   ) 

 group by ID,TenderType,BidAmount, isAwardCarrier
 
 union all 
 select ID, tendertype,MIN(BidAmount  )as bidamount,''as isAwardCarrier  from temp  
where isAwardCarrier=0 and Id not in 

(
select ID from temp  
where isAwardCarrier=1group by ID,TenderType,BidAmount, isAwardCarrier 
having BidAmount=MIN(BidAmount  )    
union all
select ID from temp  
where isAwardCarrier=1 and TenderType='Ordinary' and ID in (select ID from temp  
where isAwardCarrier=1 group by ID,TenderType,BidAmount, isAwardCarrier 
having BidAmount=MIN(BidAmount  )   ) 

 group by ID,TenderType,BidAmount, isAwardCarrier

  ) 


 group by ID,TenderType ) a