MattieMich7 (12/19/2012)
I've attached a screenshot of a selection of records from a staging table I'm using in my SSIS package. I need to iterate through these records and capture every "target_acquired"/"target_lost" combination by Auid. The trick is I need to ignore any additional "target_acquired" values before the "target_lost" record is found. I've color-coded the attached screenshot to show how the records should be grouped (all rows in white are the "ignored" rows).
Just going from your data and assuming the id column is an identity column and that all aquired records have a lost record, couldn't you do something like:
SELECT MIN(Id) AS FirstAquiredId,
MAX(Id) AS LastLostId,
AuId
FROM dbo.YourTable
GROUP BY AuId
ORDER BY AuId;
That would give you the Ids you're interested in. You could then unpivot this to get rows for each id. See if that gives you a start.
HTH,
Rob