SELECT * FROM(
SELECT ROW_NUMBER() OVER (ORDER BY r.UpdateTime desc) tempRowNum,h.BizID,h.OrgID
FROM dbo.ToPlanTargetValueHis h
INNER JOIN dbo.ToPlanAdjustRecord r
ON h.BizID=r.ID
WHERE h.BizID<>'import' AND r.AdjustAmount<>-1 AND DATEDIFF(YEAR,'',r.BizDate)=0 AND h.OrgID='0972d31c170d41b2b778d6b2ab203b7e' AND DATEDIFF(DAY, '', r.BizDate) >= 0
AND DATEDIFF(DAY, '', r.BizDate) <= 0
GROUP BY h.BizID,h.OrgID,r.UpdateTime) a
LEFT JOIN
(SELECT r.ID,(
SELECT s.ShortName+',' FROM ToPlanTargetValueHis h
LEFT JOIN dbo.SysOrganization s ON h.OrgID=s.ID WHERE h.BizID=r.ID AND h.AdjustType='in' ORDER BY h.UpdateTime DESC FOR XML PATH('')) InOrgName,
(SELECT f.Name+',' FROM ToPlanTargetValueHis h
LEFT JOIN dbo.ToFinanceAccount f ON h.TargetID=f.ID WHERE h.AdjustType='in' AND h.BizID=r.ID ORDER BY h.UpdateTime DESC FOR XML PATH('')) InTargetName,
(SELECT s.ShortName+',' FROM ToPlanTargetValueHis h
LEFT JOIN dbo.SysOrganization s ON h.OrgID=s.ID WHERE h.BizID=r.ID AND h.AdjustType='out' ORDER BY h.UpdateTime DESC FOR XML PATH('')) OutOrgName,
(SELECT f.Name FROM ToPlanTargetValueHis h
LEFT JOIN dbo.ToFinanceAccount f ON h.TargetID=f.ID WHERE h.AdjustType='out' AND h.BizID=r.ID ORDER BY h.UpdateTime desc FOR XML PATH('')) OutTargetName,
r.BizType,(SELECT TOP 1 h.IsAdjustYear FROM ToPlanTargetValueHis h WHERE h.BizID =r.ID ORDER BY h.UpdateTime DESC) IsAdjustYear,
r.UpdateTime AS BizDate,r.AdjustStatus,u.EmpName UserName
,(SELECT CONVERT(NVARCHAR(1000),h.TargetValue)+',' FROM dbo.ToPlanTargetValueHis h
WHERE h.AdjustType IN('in','out') AND h.BizID=r.ID ORDER BY h.UpdateTime DESC FOR XML PATH('')) AdjustAmount,
(SELECT CONVERT(NVARCHAR(1000),h.AfterAdjust)+',' FROM toplantargetvaluehis h
WHERE h.AdjustType IN('in','out') AND h.BizID=r.ID ORDER BY h.UpdateTime desc FOR XML PATH('')) AfterAdjust,
(SELECT COUNT(1) FROM dbo.ToPlanTargetValue WHERE BizID=r.ID) IsEnable,r.ProcInstID,w.Status as WFStatus,Remark
FROM dbo.ToPlanAdjustRecord r INNER JOIN dbo.SysUser u ON r.UpdateUser =u.ID LEFT JOIN dbo.WF_ProcInst AS w ON w.ProcInstID=r.ProcInstID) b
ON a.BizID=b.ID
WHERE a.tempRowNum>0 AND a.tempRowNum<=10 ORDER BY a.tempRowNum
