return all ESNID one time, the most recent

This is more ASP SElect .

I need to return all the rows. Where the ESNnumber only returns the most recent one that is associsted with the Asset.

Basically, I need the info most current ESN number only.
They are 19,00 rows of each ESN number but it returns 40,000. Duplicates.

SELECT TOP (100) PERCENT dbo.AssetType.Description, dbo.AssetCustomAttributeDef.Name AS [Custom Asset], dbo.ESN.EsnNumber AS [ESN #],
dbo.AssetAttribute.AssetDescription AS [Description Detail], dbo.Asset.Barcode, dbo.Asset.SKU,
dbo.InventoryOrigin.WarehouseDescription AS [Inventory (W/H)], dbo.ESN.DateImplemented, dbo.ESNTracking.TraceTime,
dbo.ESNTracking.PreviousTraceTime, dbo.ESNTracking.HasMoved, dbo.ESNTracking.DistanceMiles, dbo.ESNTracking.Direction,
dbo.ESNTracking.Landmark, dbo.ESNTracking.FemaLocation AS Fema, dbo.ESNTracking.ReportTime AS [Report Time],
dbo.ESNTracking.CurrLocStreet AS Address, dbo.ESNTracking.CurrLocCity AS City, dbo.ESNTracking.CurrLocState AS State,
dbo.ESNTracking.CurrLocZip AS Zipcode, dbo.ESNTracking.CurrLocCounty AS County, dbo.ESNTracking.MapUrl AS [Map Link],
dbo.ESNTracking.ReplaceByDate AS [Replace Batt.], dbo.ESNTracking.CurrMileFromStratix AS [From Stratix Now],
dbo.ESNTracking.PrevMileFromStratix AS [From STratix Then]
FROM dbo.AssetType INNER JOIN
dbo.Asset ON dbo.AssetType.AssetTypeId = dbo.Asset.AssetTypeId INNER JOIN
dbo.InventoryOrigin ON dbo.Asset.WarehouseId = dbo.InventoryOrigin.WarehouseId INNER JOIN
dbo.AssetAttribute ON dbo.Asset.AssetAttributeId = dbo.AssetAttribute.AssetAttributeId INNER JOIN
dbo.EsnAsset ON dbo.Asset.AssetId = dbo.EsnAsset.AssetId INNER JOIN
dbo.ESN ON dbo.EsnAsset.EsnId = dbo.ESN.EsnId LEFT OUTER JOIN
dbo.ESNTracking ON dbo.EsnAsset.EsnId = dbo.ESNTracking.EsnId LEFT OUTER JOIN
dbo.AssetVehicle ON dbo.EsnAsset.AssetId = dbo.AssetVehicle.AssetId LEFT OUTER JOIN
dbo.AssetCustomAttribute ON dbo.EsnAsset.AssetId = dbo.AssetCustomAttribute.AssetId LEFT OUTER JOIN
dbo.AssetCustomAttributeDef ON dbo.AssetCustomAttribute.AssetTypeId = dbo.AssetCustomAttributeDef.AssetTypeId

ORDER BY dbo.AssetType.Description

If I have understood this correctly...

1. Drop a MULTICAST into your flow.

2. On one output, use AGGREGATE to work out the max ESN Number per asset.

3. Join that back to the other output using MERGE JOIN, joining on ESN Number and Asset

Does that work?

-Jamie

|||

Could you help me please.

I need help.

writiing the query with thos parameters