Retrieving the most recent revision information for specified dates (SQL)

Question

In the above FldID 52 = Description and FldID 54 = HistoryDetail

For any given date the output should be the last entry for that date. Also the columns need to be become rows. User will provide 2 dates. Say March 2, 2010 and March 3, 2010.

So output in above case should be

Since Rev 6 does not have an entry for FldID 52 in Table A, [Placeholder 1] should have Words from Rev 3 (since that is the latest before Rev 6 for FldID 52 in Table A).

Similarly,
Since Rev 6 does not have an entry for FldID 54 in Table A, [Placeholder 2] should have Words from Rev 5 (since that is the latest before Rev 6 for FldID 54 in Table A).

Since Rev 8 does not have an entry for FldId 54 in Table A, [Placeholder 3] should have Words from Rev 7 (since that is the latest before Rev 8 for FldID 54 in Table A).

I am unable to come up with a query that can give me the above results. Please help.

update

Datatypes for fields in TABLE B

Answer 1

SELECT 
LAST(System.ChangedDate) as ChangedDate,
LAST(System.Rev) as Rev,
LAST(System.ChangedBy) as ChangedBy,
LAST(System.AssignedTO) AS AssignedTO,
LAST(System.IterationPath) AS IterationPath,
LAST(A1.Description) AS Description,
LAST(A2.Description) AS HistroyDetail
FROM TableB
JOIN TableA on TableB.Id=TableA.ID AND TableB.(datefunction)=TableA=(datefunction) 
AND FldID=52 AS A1
JOIN TableA on TableB.Id=TableA.ID AND TableB.(datefunction)=TableA=(datefunction) 
AND fldID=54 AS A2
WHERE (datefunction) >= (minimum date) and (datefunction) <= (minimum date);
ORDER BY ChangedDate
GROUP BY (datefunction)

Replace datefunction with the function that extracts the date and ignores the Time from the datetime field. This will give a table like what you want but 90 degree's off.

Answer 2

This is basically a combination of a groupwise maximum query and a reverse pivot. The simple approach is to use ROW_NUMBER and UNPIVOT.

I'll show you how to do this for one date. To do two dates it's basically just copy and paste with a second date parameter and a join at the end on the FieldName column. You also haven't posted any description schema so I'm going to assume you have a table called FieldDescription that maps the FldID to its corresponding name (such as HistoryDetail).

;WITH A_CTE AS
(
    SELECT
        fd.FieldName, a.Words,
        ROW_NUMBER() OVER (PARTITION BY a.FldID ORDER BY a.Rev DESC) AS RowNum
    FROM TableA a
    INNER JOIN FieldDescription fd
        ON fd.FldID = a.FldID
    WHERE AddedDate <= @Date
),

B_CTE AS
(
    SELECT
        IterationPath,
        CAST(ChangedDate AS nvarchar(4000)) AS ChangedDate,
        CAST(Rev AS nvarchar(4000)) AS Rev,
        CAST(ChangedBy AS nvarchar(4000)) AS ChangedBy,
        CAST(AssignedTo AS nvarchar(4000)) AS AssignedTo,
        ROW_NUMBER() OVER
        (
            PARTITION BY Rev
            ORDER BY ChangedDate DESC
        ) AS RowNum
    FROM TableB
    WHERE ChangedDate <= @Date
),

Props AS
(
    SELECT PropertyName, PropertyValue
    FROM B_CTE
    UNPIVOT
    (
        PropertyValue
        FOR PropertyName IN
        (
            IterationPath, ChangedDate, Rev, ChangedBy, AssignedTo
        )
    ) AS u
    WHERE RowNum = 1
)

SELECT FieldName, Words
FROM A_CTE
WHERE RowNum = 1

UNION ALL

SELECT PropertyName, PropertyValue
FROM Props