SQL Server 2008 query

Question

I am trying to implement versioning of data

I have two tables Client and Address. I have to display in the UI, the various updates in the order in which they were made but with the correct client version

so,

Client Table                                  Address Table  
----------                                    ----------

Client Version  Modified Date                 Address Version   ModifiedDate  
CV1              T1                                 AV1               T2  
CV2              T4                                 AV2               T3  
CV3              T5                   

My result should be

CV1       AV1   (first version)  
CV1       AV2    (as AV1 was updated at T3)  
CV2       AV2    (as Client got updated to CV2 at T4)  
CV3       AV2     (As client has got updated at T5)  

Answer 1

This isn't perfect, but you could try a UNION subquery, something like:

SELECT ClientVersion, AddressVersion, ModifyDate FROM
(SELECT ClientVersion, NULL as AddressVersion, ModifyDate FROM Client
 UNION ALL
SELECT NULL AS ClientVersion, AddressVersion, ModifyDate FROM Address)
ORDER BY ModifyDate ASC

should return a result like (from your sample data):

ClientVersion   AddressVersion    ModifyDate
CV1                               T1
                AV1               T2
                AV2               T3
CV2                               T4
CV3                               T5

Then you can fill in the ClientVersion from the prior row on the application side.

Answer 2

I believe that this will give you what you want, although it includes one row at the start where there is a CV1 but no AV1 yet. You can easily change that by making the JOINs INNER JOIN instead:

;WITH Distinct_Dates AS (
    SELECT
        modified_date
    FROM
        Clients
    UNION
    SELECT
        modified_date
    FROM
        Addresses
)
SELECT
    C1.client_version,
    A1.address_version
FROM
    Distinct_Dates DD
LEFT OUTER JOIN Clients C1 ON
    C1.modified_date <= DD.modified_date AND
    NOT EXISTS (SELECT * FROM Clients C2 WHERE C2.modified_date > C1.modified_date AND C2.modified_date <= DD.modified_date)
LEFT OUTER JOIN Addresses A1 ON
    A1.modified_date <= DD.modified_date AND
    NOT EXISTS (SELECT * FROM Addresses A2 WHERE A2.modified_date > A1.modified_date AND A2.modified_date <= DD.modified_date)

Answer 3

With 
    ClientTable As
    (
    Select 'CV1' As Version, 'T1' As ModifiedDate
    Union All Select 'CV2', 'T4'
    Union All Select 'CV3', 'T5'
    )
    , AddressTable As
    (
    Select 'AV1' As Version, 'T2' As ModifiedDate
    Union All Select 'AV2', 'T3'
    )
    , Ranges As
    (
    Select C.Version, C.ModifiedDate As StartDate, Min(C2.ModifiedDate) As EndDate
    From ClientTable As C
        Left Join ClientTable As C2
            On C2.ModifiedDate > C.ModifiedDate
    Group By C.Version, C.ModifiedDate
    )
Select R.Version, Coalesce(A.Version, LastAddressVersion.Version)
From Ranges As R
    Left Join AddressTable As A
        On A.ModifiedDate >= R.StartDate
            And ( A.ModifiedDate < R.EndDate Or R.EndDate Is Null )
    Cross Join  (
                Select A2.Version
                From AddressTable As A2
                Where A2.ModifiedDate = (
                                        Select Max(A3.ModifiedDate)
                                        From AddressTable As A3
                                        )
                ) As LastAddressVersion
Order By R.Version