We have an interesting problem around duplicate data. Our source files can contain multiple rows of data for a single employee benefit. As an example, a health care benefit could be split up and reported over 3 rows (3 is just an example - it could be many more).

The data is loaded into staging with no problems and will contain a mix of single and multiple benefit rows. What I have done thus far is to perform aggregation to find identify with multiple benefits (aggregate on employeeid and benefitid) and if it is > 1, move it to a separate staging table.

What I now need to do is analyse the staging table with multiple benefits and aggregate the data based on employeeid and benefitid. Let's assume a benefit has 3 rows.

The employeeid and benefitid will be the key to identify those rows. Other rows may include the benefit provider, yearly cost, eligible & benefit name (and many more but these cover the 4 types of aggregation required to output a single row).

  1. if there are multiple providers in the list of 3, we should set the value to "Multiple" otherwise it should be the distinct value
  2. yearly cost is simple...SUM of all 3 rows
  3. if any of the active flags are true, set the value to true (1 could be true and 2 could be false)
  4. if there are multiple benefit names, we just select the 1st non null value.

After the data is aggregated (3 rows into 1, applying the rules above), it is then merged with the single row benefits table.

  • "1st non-null value" How do I know what's first? From an SSIS perspective, you will want to craft an appropriate query to perform this aggregation. Attempting to do so exclusively in an SSIS data flow task will be a futile exercise in patience – billinkc Jan 13 '16 at 16:08
  • That's the route I had started to take - But before I was too deep into writing a query, I was wondering if there was any way this could be done within a data flow. Even with SQL, it's a pain. Thanks. – Clive Strong Jan 13 '16 at 16:11
  • What version of SQL Server/compatibility level are you working with? – billinkc Jan 13 '16 at 16:27
  • SQL Server 2014 & SQL Server 2014(120) compatibility – Clive Strong Jan 13 '16 at 16:30

Since you're using 2012+, you have windowing functions available to you which I think make this easier to solve.

Here's my set up.

    SELECT * 
        sys.tables AS T 
        INNER JOIN 
            sys.schemas AS S 
            ON s.schema_id = t.schema_id
        S.name ='dbo' AND T.name = 'StagingBenefit'
        StagingBenefitSK int identity(1,1) NOT NULL

    ,   EmployeeID int NOT NULL
    ,   BenefitID int NOT NULL

    ,   BenefitProvider char(1) NOT NULL
    ,   Cost int NOT NULL
    ,   FlagA bit NOT NULL
    ,   FlagB bit NOT NULL
    ,   FlagC bit NOT NULL
    ,   BenefitName varchar(30) NULL
    ,   Scenario varchar(30) NOT NULL

TRUNCATE TABLE dbo.StagingBenefit;

        (1,1, 'A', 100, 0, 0, 0, 'Teeth', 'SingleRow')
    ,   (2,2, 'A', 200, 0, 0, 0, 'Teeth', 'DoubleRow, Samesies')
    ,   (2,2, 'A', 222, 0, 0, 0, 'Teeth', 'DoubleRow, Samesies')
    ,   (3,3, 'A', 300, 0, 0, 0, 'Teeth', 'ThreeRow,DoubleFlagged')
    ,   (3,3, 'A', 330, 1, 0, 0, 'Elbow', 'ThreeRow,DoubleFlagged')
    ,   (3,3, 'A', 333, 0, 1, 0, 'Elbow', 'ThreeRow,DoubleFlagged')
    ,   (4,4, 'A', 400, 0, 0, 0, 'Teeth', 'MultipleProviders')
    ,   (4,4, 'A', 440, 0, 0, 0, 'First', 'MultipleProviders')
    ,   (4,4, 'B', 444, 0, 0, 1,    NULL, 'MultipleProviders')
,   BenefitID

,   BenefitProvider
,   Cost
,   FlagA
,   FlagB
,   FlagC
,   BenefitName
,   Scenario

I would then look at a query something like this

,   SB.BenefitID
            MIN(SB.BenefitProvider) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) 
            <> MAX(SB.BenefitProvider) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID)
            THEN 'Multiple'
            MIN(SB.BenefitProvider) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) 
        END AS BenefitProvider
    -- SUM this
,   SUM(SB.Cost) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) As TotalCost
,   CAST(MAX(CAST(SB.FlagA AS int)) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) AS bit) AS FlagA
,   CAST(MAX(CAST(SB.FlagB AS int)) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) AS bit) AS FlagB
,   CAST(MAX(CAST(SB.FlagC AS int)) OVER (PARTITION BY SB.EmployeeID, SB.BenefitID) AS bit) AS FlagC
,   MIN(SB.BenefitName) OVER 
        PARTITION BY SB.EmployeeID, SB.BenefitID 
    ) AS FirstBenefit
,   SB.Scenario
    dbo.StagingBenefit AS SB

Picking through some of the tricks of the query

  • DISTINCT I feel this is the dirtiest part of it but it solves the problem
  • MIN/MAX BenefitProvider - I find the first and last for my window (employee and benefit ids) and if they are different, which I determine via the CASE expression, I use the text Multiple. Otherwise, I just grabbed the minimum but max would work just as well. Heck, I probably could have just skipped the second aggregation call and just made it column name.
  • MAX Flag - I made this as hard as I could by using a bit data type which you cannot aggregate on so I explicitly convert to an integer and then cast the results of that back to a bit. Same windowing though. Net result is if anywhere my flag is set to true, it'll get preserved across rows
  • MIN BenefitName - the rule here is to pick the first non-null benefit name. The aggregate function eliminates the NULL and returns the text "First"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.