Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Best Approach For Iterating Through Staging Table - deleting/ignoring unwanted rows Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 9:58 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 8:28 PM
Points: 23, Visits: 154
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.


  Post Attachments 
IterationExample.png (16 views, 23.57 KB)
Post #1398770
Posted Thursday, December 20, 2012 8:20 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:50 AM
Points: 1,234, Visits: 1,273
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
Post #1399000
Posted Thursday, December 20, 2012 9:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 8:28 PM
Points: 23, Visits: 154
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.
Post #1399031
Posted Friday, December 21, 2012 3:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:51 PM
Points: 7,140, Visits: 12,763
Which version of SQL Server are you using?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1399311
Posted Friday, December 21, 2012 7:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 8:28 PM
Points: 23, Visits: 154
2008
Post #1399390
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse