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

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 开发者_C百科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

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

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.