Post reply

Incrementing Counter - reset on "BY" Group

  • June 21, 2007 at 8:19 am

    #196428

    Hi All

    I've got the following:

    ID            Date

    1             10/10/06

    1             02/01/07

    2             10/11/04

    2             11/10/06

    2             12/12/06

    and I need to add in the "P_ID" column as below:

    ID   P_ID         Date

    1    1             10/10/06

    1    2             02/01/07

    2    1             10/11/04

    2    2             11/10/06

    2    3             12/12/06

    Any ideas? Needs to be done in SSIS - either a component (or combination of) or a script...

    Cheers

    Ben

  • June 21, 2007 at 9:10 am

    #714394

    Ben

    I think the ROW_NUMBER function is exactly what you're looking for here.

    John

  • June 21, 2007 at 9:33 am

    #714405

    Hi John

    I'd hoped that the Row Number component would do what I wanted, but I can't see how to "restart" the numbering each time you get into the next ID - it only appears to do a continual incrementation.

    I'm using Row Number to great effect in assigning the initial ID earlier on, but am having traumas getting something that restarts each time the "by group"  - the ID in this case - changes.

    Cheers

    Ben

  • June 21, 2007 at 9:38 am

    #714408

    Ben

    Can you adapt this example from Books Online to work in your environment?

    John

    USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER
    (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCodeFROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

  • June 21, 2007 at 10:38 am

    #714425

    Hi John

    Thanks for the response. It's an option I hadn't thought of - I was aiming to keep as much of the processing in the pipeline as possible, but this could be a good work around.

    cheers

    Ben

  • June 21, 2007 at 11:02 am

    #714429

    Answering my own posts now...

    I've ended up writing a script as follows:

    Public Class ScriptMain
    Inherits UserComponent
    Private NextKey As Int32 = 0
    Private LastID As Int32 = 0
    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    '
    If LastID <> Row.nameid Then
    LastID = Row.nameid
    NextKey = 0
    End If
    Dim ChildCounter As Int32 = NextKey + 1
    NextKey = ChildCounter
    LastID = Row.nameid
    Row.ChildNumber = ChildCounter
    '
    End Sub
    End Class

    It does what I need it to - found the inspiration at http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

    Cheers

    Ben

     

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

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