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.
精彩评论