Latest date of one to many assets

1

Hello there,

I have a two tables, one is an overview of assets and the other table is how many tests that asset has had. So one asset may have had many tests.

I have created the following expression that does some date adds to work out when I should next test an asset based on when it was last tested and the score it got (class)

=iif(Fields!CLASS.Value=1,DateAdd("yyyy",6,Fields!NDT_Test_Date.Value),iif(Fields!CLASS.Value=2,DateAdd("yyyy",3,Fields!NDT_Test_Date.Value),iif(Fields!CLASS.Value=3,DateAdd("yyyy",1,Fields!NDT_Test_Date.Value),iif(Fields!CLASS.Value=4,Fields!NDT_Test_Date.Value,iif(Fields!CLASS.Value=5,Fields!NDT_Test_Date.Value,Fields!NDT_Intervention_Date_1.Value)))))

the expression above works fine when there is only one or no previous tests however if there is more than one test then it throws up #error and I am assuming that is down to the one to many relationship.

My join query is below...

FROM
AS_Asset
INNER JOIN street
ON AS_Asset.USRN = street.USRN
INNER JOIN AS_GWSL_INVE_DATA
ON AS_Asset.CentralAssetID = AS_GWSL_INVE_DATA.CentralAssetID
FULL OUTER JOIN AS_GWSL_STRUCTEST_DATA
ON AS_Asset.CentralAssetID = AS_GWSL_STRUCTEST_DATA.CentralAssetID

Can anyone offer advice on how I can get this query to only bring back the very latest date record in the AS_GWSL_STRUCTEST_DATA table, and therefore make the expression above work every time...

thanks in advance

David.

2

Dunno if relevant in this context, but [MyRowNo] = ROW_NUMER() OVER(), sorted by whatever columns you need to "present" with the most appropriate row first, and then filter on [MyRowNo] = 1

Here's my generic template for ROW_NUMBER() OVER() in case helpful

-- Template for TOP 1 child data
SELECT	
	M.MainCol1
	, M.MainCol2
	, S.SubCol1
	, S.SubCol2
--	, S.OtherCol1
--	, S.OtherCol2
--	, S_RowNumber	-- Not normally required in the results
FROM	dbo.MyMainTable AS M
	JOIN
	(
		SELECT	[S_RowNumber] = ROW_NUMBER()
				OVER
				(
					PARTITION BY MatchColumn1
						, MatchColumn2
					ORDER BY 
					-- ** NOTE: This Sort Order must present the row to RETAIN FIRST
						SortColumn1
						, SortColumn2
--						, P.PartitionSortColumn1
--						, P.PartitionSortColumn2
				)
			, MatchColumn1
			, MatchColumn2

			, SubCol1
			, SubCol2
--			, O.OtherCol1
--			, O.OtherCol2
		FROM	dbo.MySubTable AS 
--			JOIN OtherTablesRequiredForTheSELECT AS O
--			JOIN OtherTablesOnlyUsedForPartition_OrderBy AS P
		WHERE	FilterColumn1 LIKE 'SomeValue%'
	) AS S
		-- Usually the MatchColumns are the same as the PKey columns
		 ON S.MatchColumn1 = M.MatchColumn1
		AND S.MatchColumn2 = M.MatchColumn2
WHERE	S_RowNumber = 1
ORDER BY M.MatchColumn1
	, M.MatchColumn2