Luis Cazares (3/31/2014)
It seems almost fine, but it's weird. You're not summing up the values. I added the SUM() and converted it to a iTVF. You need to test it as well to be sure it works as it should.
--DECLARE @bid_Id UNIQUEIDENTIFIER;
--SET @bid_Id = '475C1532-20CC-437A-9642-B2A87D79036A'
ALTER FUNCTION [dbo].[fn_GetSumOfPointsForBidTEST1]
(
-- Add the parameters for the function here
@bid_Id UNIQUEIDENTIFIER
)
RETURNS TABLE
AS
RETURN
SELECT TOP 1 CASE IsDirectAuction
WHEN 0 THEN (SELECT SUM(Calc_Points) FROM [dbo].[vw_Bid_Criterias]
WHERE BID_ID = @bid_Id AND
CRITERIA_ID IN ( SELECT CRITERIA_ID
FROM [dbo].[vw_Bid_Criterias]
WHERE BID_ID = @bid_Id
--New section for OtherCriteria
UNION
SELECT drtc.CRITERIA_ID
FROM BID_CALCULATIONS bc
INNER JOIN CRITERIAS c ON bc.criteria_ID = c.ID
INNER JOIN BIDS b ON b.ID = bc.bid_ID
INNER JOIN CALL_FOR_TENDERS cft ON cft.id = b.call_for_tender_ID
INNER JOIN DECISIONS_ON_REQUESTS_FOR_TENDERS_CRITERIAS drtc ON drtc.decision_for_tender_id = cft.decision_on_request_for_tender_ID
AND drtc.criteria_id = c.ID
WHERE bid_ID = @bid_Id
AND drtc.points > 0
AND number > 13
))
WHEN 1 THEN (SELECT SUM(points) FROM [dbo].[BID_CALCULATIONS]
WHERE bid_ID = @bid_Id AND
CRITERIA_ID IN ( SELECT crt.ID
FROM bids b
INNER JOIN tbl_EntityInstanceCallForTender e ON b.call_for_tender_ID = e.CallForTenderID
INNER JOIN tbl_ProcessCriterias pc ON e.EntityID = pc.EntityID
INNER JOIN tbl_Criterias c ON pc.ID = c.ProcessID
INNER JOIN tbl_CriteriaTypes ct ON c.OrderNumber = ct.ID
INNER JOIN CRITERIAS crt ON ct.OrderNumber = crt.number
WHERE b.id = @bid_Id
AND c.IsDeleted = 0
UNION
SELECT crt.ID
FROM bids b
INNER JOIN tbl_EntityInstanceLotsCallForTender e ON b.call_for_tender_ID = e.CallForTenderID
INNER JOIN tbl_ProcessCriterias pc ON e.EntityID = pc.EntityID
INNER JOIN tbl_Criterias c ON pc.ID = c.ProcessID
INNER JOIN tbl_CriteriaTypes ct ON c.OrderNumber = ct.ID
INNER JOIN CRITERIAS crt ON ct.OrderNumber = crt.number
WHERE b.id = @bid_Id
AND c.IsDeleted = 0
--New section for OtherCriteria
UNION
SELECT drtc.CRITERIA_ID
FROM BID_CALCULATIONS bc
INNER JOIN CRITERIAS c ON bc.criteria_ID = c.ID
INNER JOIN BIDS b ON b.ID = bc.bid_ID
INNER JOIN CALL_FOR_TENDERS cft ON cft.id = b.call_for_tender_ID
INNER JOIN DECISIONS_ON_REQUESTS_FOR_TENDERS_CRITERIAS drtc ON drtc.decision_for_tender_id = cft.decision_on_request_for_tender_ID
AND drtc.criteria_id = c.ID
WHERE bid_ID = @bid_Id
AND drtc.points > 0
AND number > 13
))
ELSE 0 END AS points
FROM vw_CallForTender
WHERE ID IN ( SELECT call_for_tender_ID
FROM BIDS
WHERE ID = @bid_Id);
With iTFV have greater performance benefit. (will see the results on production environment) Also i learned now how to use CASE statement instead those IF.
still cant figure out how this code is not needed (removed)
DECLARE @isDirect BIT;
DECLARE @cftID UNIQUEIDENTIFIER;
SELECT @cftID = call_for_tender_ID
FROM BIDS
WHERE ID = @bid_Id;
SELECT @isDirect = IsDirectAuction
FROM vw_CallForTender
WHERE ID = @cftID;
but i'll figure out.
Thanks again for helping, you are awesome tutor bro, i'm starting to think set-based now 🙂