SQL to insert latest version of a group of items

Question

I’m trying to determine a good way to handle the scenario below. I have the following two database tables, along with sample data. Table1 contains distributions that are grouped per project. A project can have one or more distributions. A distribution can have one of more accounts. An account has a percentage allocated to it. The distributions can be modified by adding or removing account, as well as changing percentages.

Table2 tracks distributions, assigning a version number to each distribution. I need to be able to copy new distributions from Table1 to Table2, but only under two conditions:

1. the entire distribution does not already exist 
2. the distribution has been modified (accounts added/removed or percentages changed).

Note: When copying a distribution from Table1 to Table2 I need to compare all accounts and percentages within the distribution to determine if it already exists. When inserting the new distribution then I need to increment the VersionID (max(VersionID) + 1).

So, in the example provided the distribution (12345, 1) has been modified, adding account number 7, as well as changing percentages allocated. The entire distribution should be copied to the second table, incrementing the VersionID to 3 in the process.

The database in question is SQL Server 2005.

Table1
------
ProjectID   AccountDistributionID   AccountID   Percent
12345       1                       1           25.0
12345       1                       2           25.0
12345       1                       7           50.0
56789       2                       3           25.0
56789       2                       4           25.0
56789       2                       5           25.0
56789       2                       6           25.0

Table2
------
ID  VersionID   Project ID  AccountDistributionID   AccountID   Percent
1    1           12345       1                       1           50.0
2    1           12345       1                       2           50.0
3    2           56789       2                       3           25.0
4    2           56789       2                       4           25.0
5    2           56789       2                       5           25.0
6    2           56789       2                       6           25.0

Answer 1

Assuming you know which distribution to pull from Table1, you can dynamically determine the version that should be used like so:

Insert Table2( VersionId, ProjectId, AccountDistributionId, AccountId, Percent )
Select Coalesce(
                (
                Select Max(VersionId)
                From Table1 As T1
                Where T1.Id = T.Id
                ), 0) + 1 As VersionId
    , ProjectId, AccountDistributionId, AccountId, Percent
From Table1 As T
Where ProjectId = 12345
    And AccountDistributionId = 1

With respect to determine "any new distribution" that is tougher and it depends on how "new" is defined.

ADDITION

You asked about how to determine that the set changed. One solution is to store a hash of the values with the Account distribution when it is saved. So something like:

Update AccountDistributions
Set AccountHash = HashBytes('SHA1'
                            , (
                              Select '|' + Cast(AccountId As varchar(10)) + '|' + Cast(Percent As varchar(30))
                              From Table1 As T1
                              Where T1.AccountDistributionID = AccountDistributions.Id
                              Order By T1.AccountId, T1.Percent
                              For Xml Path('')
                              ))

You could then use that to determine if any member of the set had changed. The other solution would be to update a DateTime value into the parent table whenever a change to the set occurred and write the same value into Table2. You could then compare the last DateTime to the DateTime in the parent table to determine if anything had changed.

Answer 2

After much experimenting, I decided to take a different approach. The following query is a synopsis of what was done.

declare @accountDistributionVersionID int

select @accountDistributionVersionID = isnull(max(VersionID), 0)
from Table2 

insert into Table2
select @accountDistributionVersionID + rank,
       ProjectID, AccountDistributionID, AccountID, Percent 
from 
(
    select  D.*,
            dense_rank() over (order by ProjectID, AccountDistributionID) as rank
    from   
    (
        select distinct t2.* from
        (
            select t.ProjectID, t.AccountDistributionID, t.AccountID, t.Percent 
            from Table1 as t
            where not exists(select * from Table2 as t2
                           where t2.ProjectID = t.ProjectID
                           and t2.AccountDistributionID = t.AccountDistributionID
                           and t2.AccountID = t.AccountID
                           and t2.Percent = t.Percent
                           and VersionId = (select max(VersionID) 
                                            from compass.Table2 t3
                                            where t2.ProjectID = t3.AccountDistributionID
                                            and t2.AccountDistributionID = t3.AccountDistributionID) )
        ) as t
        cross apply
        (
            select t3.ProjectID, t3.AccountDistributionID, t3.AccountID, t3.Percent
            from Table1 As t3
            where t.ProjectID = t3.ProjectID
            and t.AccountDistributionID = t3.AccountDistributionID
        ) as t2 
    ) as D
) as T

• I get the max VersionID from the destination table.
• I retrieve only account distributions that have changed from the source table, generating a psuedo version number using the DENSE_RANK function.
• Finally I insert into the destination table, with a VersionID of (previously retrieved max VersionID) + rank

It seems to be working out well. Feedback would be greatly appreciated.