Calculated field -> lookup latest value from an other table (passing multiple criteria)-CodePudding

Hi I am unsure about how to approach my problem and I am going to explain it the best I can.

I have: Table A -> contains [ITEM ID] Other info about my item Table B -> contains [ITEM ID] [OPERATION] [SUBOPERATION] [VALUE] [FLAG IS LAST]

I want to create a View that will return:

A.[ITEM ID], [Name of OPERATION 1](contains corresponding B.[VALUE]), [Name of OPERATION 2](contains corresponding B.[VALUE])

My issue here is that i want to return only the B.[VALUE] which contains the flag "LAST" in B.[FLAG IS LAST]

Table B can have multiple rows for the same [ITEM ID] [OPERATION] [SUBOPERATION] as we can measure the same item multiple times. I only want to return the LAST [VALUE] so my view should have unique combinaison of [ITEM ID] [OPERATION] [SUBOPERATION].

Hopefully I am clear with my explanation.

I tried this with a function and it seems to work but is that the best option ?

Here is the function i created :

CREATE FUNCTION [dbo].[GetLastResult](@OPE nvarchar(100), @SUBOPE nvarchar(100), @ITEMID nvarchar(100))
RETURNS nvarchar(100)
AS 
BEGIN
RETURN(
SELECT [VALUE]
FROM [dbo].[TABLE_B]
WHERE [OPERATION] = @OPE AND [SUBOPERATION] = @SUBOPE AND [ITEM ID] = @ITEMID AND [FLAG IS LAST] = 'LAST')
END

And then in my View I am calling the function as follow :

SELECT
A.[ITEM ID],
dbo.GetLastResult('0040',  '0040', [ITEM ID]) AS Name_of_OPERATION_1,  
dbo.GetLastResult('0090',  '0090', [ITEM ID]) AS Name_of_OPERATION_2
FROM TABLE_A
LEFT JOIN TABLE_B
ON A.[ITEM ID] = B.[ITEM ID]

Here '0040', '0040',[ITEM ID] and '0090', '0090',[ITEM ID] are the Operation and suboperation passed as parameter.

I am assuming that this method is not really efficient since for each row it will have t do that research on table B to get the last result, altough the amount of record should rarely go over 10k.

How would you have approched this issue ? I dont have a choice the data must be represented the way i described : meaning one field for each [OPERATION] [SUBOPERATION].

Any help, comment is appreciated !

CodePudding user response：

I'm not sure i understand the question,

but with the information given this should have the same result:

SELECT a.[ITEM ID], operation1.VALUE AS Name_of_OPERATION_1, operation2.VALUE AS Name_of_OPERATION_2
FROM TABLE_A a
LEFT JOIN TABLE_B operation1 on operation1.[OPERATION] = '0040' AND operation1.[SUBOPERATION] = '0040' AND operation1.[ITEM ID] = a.[ITEM ID] AND operation1.[FLAG IS LAST] = 'LAST'
LEFT JOIN TABLE_B operation2 on operation2.[OPERATION] = '0090' AND operation2.[SUBOPERATION] = '0090' AND operation2.[ITEM ID] = a.[ITEM ID] AND operation2.[FLAG IS LAST] = 'LAST'

so: lookup latest value from an other table (passing multiple criteria) --> join with multiple conditions in the ON