Post reply

Best Approach For Iterating Through Staging Table - deleting/ignoring unwanted rows

  • December 19, 2012 at 9:58 pm

    #147794

    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).

    Any advice on how to approach this would be much appreciated.

  • December 20, 2012 at 8:20 am

    #1570565

    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

  • December 20, 2012 at 9:22 am

    #1570592

    I don't believe the above approach will work because there could be multiple Acquired/Lost combinations for a single Auid (sorry, should have explained that in my original post). For example, the result of the query when run against the records in the screenshot I provided is:

    FirstAquireID=2

    LastLostID=34

    Auid=71127c4145a...

    What I ended up doing (and still have more testing to do), is use a modified version of the what was explained in this article: http://www.sqlservercentral.com/articles/T-SQL/68467/

    Using this method, I was able to flag and delete the unwanted rows from the staging table.

  • December 21, 2012 at 3:55 am

    #1570826

    Which version of SQL Server are you using?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • December 21, 2012 at 7:04 am

    #1570892

    2008

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply