如何获取所有“Not Yet Created”报表数据库中用户的时间表 [英] How to fetch all "Not Yet Created" timesheet for a user from Reporting DB

问题描述

 大家好，

我在SQL Server 2005 SP4上运行PS 2007 SP2 Env。

I have PS 2007 SP2 Env running on SQL Server 2005 SP4.

我的要求是获取"尚未创建"的特定资源的所有过去时间表周期 从当前日期开始。

My requirement is to fetch all the past timesheet period for a particular resource that are in "Not Yet Created"  from current date.

为此，我使用以下查询：

For that I am using following query:

SELECT     TP.EndDate，TP.PeriodStatusID，T.TimesheetStatusID，TP.PeriodName，TR.ResourceName

FROM         MSP_Timesheet_OlapView AS T INNER JOIN

MSP_TimesheetResource AS T ON T.OwnerResourceNameUID = TR.ResourceNameUID RIGHT OUTER JOIN

MSP_TimesheetPeriod_OlapView AS TP ON T.PeriodUID = TP.PeriodUID

WHERE     （TP.EndDate< GETDATE（））AND（TR.ResourceName ='--------'）

ORDER BY TP.EndDate

但是从上面的查询中我可以获取所有时间表期间，这些时间段都在  "正在进行，已提交，已批准，已拒绝的状态"但是找不到仍然没有创建的时间表期限。

But from above query I am able to fetch all the timesheet period which are either in  "In Progress, Submitted, Approved, Rejected state" but not able to find timesheet period which are still not created.

所以请帮我修改查询以获取"Not Created Yet"的时间表期间。

So plese help me to modify the query in order to fetch timesheet period which "Not Created Yet".

谢谢!!!

推荐答案

绝对不是很有效，所以还有很大的提升空间。但它应该给你所要求的结果：

definitely not very performant, so a lot of room for improvement. But it should give you the requested result:

SELECT AllTS_Res.PeriodName, AllTS_Res.EndDate, AllTS_Res.ResourceNameFROM dbo.MSP_TimesheetLine_UserView AS TL RIGHT OUTER JOIN (SELECT TP.PeriodUID, Res.ResourceUID, TP.PeriodName, TP.EndDate, Res.ResourceName FROM dbo.MSP_TimesheetPeriod_OlapView AS TP CROSS JOIN dbo.MSP_EpmResource_UserView AS Res WHERE (TP.EndDate <= GETDATE()) AND (Res.ResourceIsGeneric = 0) AND (Res.ResourceIsTeam = 0) AND (Res.ResourceType = 2) AND (Res.ResourceEarliestAvailableFrom <= TP.EndDate) AND (Res.ResourceLatestAvailableTo >= TP.StartDate) OR (TP.EndDate <= GETDATE()) AND (Res.ResourceIsGeneric = 0) AND (Res.ResourceIsTeam = 0) AND (Res.ResourceType = 2) AND (Res.ResourceEarliestAvailableFrom IS NULL) AND (Res.ResourceLatestAvailableTo IS NULL) OR (TP.EndDate <= GETDATE()) AND (Res.ResourceIsGeneric = 0) AND (Res.ResourceIsTeam = 0) AND (Res.ResourceType = 2) AND (Res.ResourceEarliestAvailableFrom <= TP.EndDate) AND (Res.ResourceLatestAvailableTo IS NULL) OR (TP.EndDate <= GETDATE()) AND (Res.ResourceIsGeneric = 0) AND (Res.ResourceIsTeam = 0) AND (Res.ResourceType = 2) AND (Res.ResourceEarliestAvailableFrom IS NULL) AND (Res.ResourceLatestAvailableTo >= TP.StartDate)) AS AllTS_Res ON TL.PeriodUID = AllTS_Res.PeriodUID AND TL.ResourceUID = AllTS_Res.ResourceUIDWHERE (TL.TimesheetLineUID IS NULL)ORDER BY AllTS_Res.EndDate DESC, AllTS_Res.ResourceName

祝你好运！

Barbara

Good luck!
Barbara

这篇关于如何获取所有“Not Yet Created”报表数据库中用户的时间表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！