Microsoft प्रोजेक्ट सर्वर के आधार पर प्रोजेक्ट ऑफिस टूल बनाना

 SELECT R.StartDate, R.EndDate, R.PeriodName, R.TimeByDay, R.TimeByDay_DayOfWeek, R.ActualWorkBillable, R.ProjectName, R.TS_LINE_CACHED_ASSIGN_NAME + ' (' + R.ProjectName + ')' AS TaskName, R.Status, R.ProjectAccount, R.Location, R.ResourceCompany, R.EmployeeID, R.TS_LINE_CLASS_NAME, R.Type, R.ProjectOwner, R.Firstname, R.LastName, R.ResourceName, R.ModifiedDate, R.ResourceNameUID, DATEPART(yyyy, R.EndDate) AS PeriodYear, DATEPART(mm, R.EndDate) AS PeriodMonth, DATEPART(ww, R.EndDate) AS PeriodWeek, DATEPART(yyyy, R.TimeByDay) AS RealPeriodYear, DATEPART(mm, R.TimeByDay) AS RealPeriodMonth, DATEPART(ww, R.TimeByDay) AS RealPeriodWeek, R.ProjectStatus, CASE WHEN ISNULL(R.RES_TERMINATION_DATE, GETDATE()) >= GETDATE() THEN 0 ELSE 1 END AS IsFire, R.WRES_EMAIL AS Email FROM (SELECT R_1.RES_HIRE_DATE, R_1.RES_TERMINATION_DATE, R_1.WRES_EMAIL, R_1.TODAY, R_1.IsActive, R_1.StartDate, R_1.EndDate, R_1.PeriodName, R_1.TimeByDay, R_1.TimeByDay_DayOfWeek, R_1.ActualWorkBillable, R_1.ProjectName, R_1.[Task Name], R_1.Status, R_1.ProjectAccount, R_1.Location, R_1.ResourceCompany, R_1.EmployeeID, R_1.TS_LINE_CACHED_ASSIGN_NAME, R_1.TS_LINE_CLASS_NAME, R_1.Type, R_1.TimesheetClass, R_1.ProjectOwner, R_1.Firstname, R_1.LastName, R_1.ResourceName, R_1.ModifiedDate, R_1.ResourceNameUID, R_1.ResourceCC, R_1.CostCenter, R_1.ProjectType, CASE WHEN R_1.ProjectStatus IS NULL THEN 'Undefined' ELSE R_1.ProjectStatus END AS ProjectStatus, R_1.TS_LINE_UID, DATEADD(day, - MIN(DATEDIFF(day, T.EFFECTIVE_DATE, R_1.TimeByDay)), R_1.TimeByDay) AS EFFECTIVE_DATE FROM (SELECT res.RES_HIRE_DATE, res.RES_TERMINATION_DATE, res.WRES_EMAIL, GETDATE() AS TODAY, CASE WHEN (res.RES_TERMINATION_DATE > GETDATE() OR res.RES_TERMINATION_DATE IS NULL) THEN 'Active' ELSE 'Inactive' END AS IsActive, tpr.WPRD_START_DATE AS StartDate, tpr.WPRD_FINISH_DATE AS EndDate, tpr.WPRD_NAME AS PeriodName, ISNULL(tla.TS_ACT_START_DATE, tpr.WPRD_START_DATE) AS TimeByDay, DATEPART(weekday, tla.TS_ACT_START_DATE) AS TimeByDay_DayOfWeek, tla.TS_ACT_VALUE / 60000 AS ActualWorkBillable, CASE tcl.TS_LINE_CLASS_NAME WHEN 'Standard' THEN tp.PROJ_NAME ELSE tcl.TS_LINE_CLASS_NAME END AS ProjectName, tsk.TASK_NAME AS [Task Name], CASE t .TS_STATUS_ENUM WHEN 0 THEN 'InProgress' WHEN 1 THEN 'Submitted' WHEN 2 THEN 'Acceptable' WHEN 3 THEN 'Approved' WHEN 4 THEN 'Rejected' WHEN 5 THEN 'Pending' ELSE 'Missing' END AS Status, CASE tcl.TS_LINE_CLASS_NAME WHEN 'Standard' THEN PP.ProjectAccount WHEN 'Administrative & General' THEN '0700-000' WHEN 'Bench time' THEN '0702-000' WHEN 'Holidays' THEN '0500-000' WHEN 'Internal Projects' THEN '0701-000' WHEN 'Pre-sales & Overhead' THEN '0600-000' WHEN 'Recruitment (interview)' THEN '0703-000' WHEN 'Sales activity' THEN '0704-000' WHEN 'Vacation' THEN '0209-000' ELSE PP.ProjectAccount END AS ProjectAccount, C.Location, RC.ResourceCompany, E.EmployeeID, tl.TS_LINE_CACHED_ASSIGN_NAME, tcl.TS_LINE_CLASS_NAME, tcl.TS_LINE_CLASS_TYPE AS Type, tcltop.TS_LINE_CLASS_NAME AS TimesheetClass, pr_owner.RES_NAME AS ProjectOwner, SUBSTRING(tr.RES_NAME, 0, CHARINDEX(' ', tr.RES_NAME)) AS Firstname, SUBSTRING(tr.RES_NAME, CHARINDEX(' ', tr.RES_NAME) + 1, LEN(tr.RES_NAME)) AS LastName, SUBSTRING(tr.RES_NAME, CHARINDEX(' ', tr.RES_NAME) + 1, LEN(tr.RES_NAME)) + ' ' + SUBSTRING(tr.RES_NAME, 0, CHARINDEX(' ', tr.RES_NAME)) AS ResourceName, t.MOD_DATE AS ModifiedDate, tr.RES_UID AS ResourceNameUID, tr.ResourceCC, PCC.CostCenter, PT.ProjectType, PPS.ProjectStatus as ProjectStatus, tla.TS_LINE_UID FROM pub.MSP_WEB_TIME_PERIODS AS tpr CROSS JOIN (SELECT RES_UID, RES_NAME, CASE WHEN tr.ResourceCC = 4 OR tr.ResourceCC = 6 THEN 'Only DC' ELSE CASE WHEN tr.ResourceCC = 1 THEN 'Only NSK' ELSE 'DC & NSK' END END AS ResourceCC FROM (SELECT RES_UID, RES_NAME, SUM(CASE WHEN tr.CostCenter IS NULL THEN 4 ELSE CASE WHEN tr.CostCenter = 'DC' THEN 2 ELSE 1 END END) AS ResourceCC FROM (SELECT DISTINCT tr.RES_UID, tr.RES_NAME, PCC.CostCenter FROM pub.MSP_RESOURCES AS tr INNER JOIN pub.MSP_PROJECT_RESOURCES AS pr ON pr.RES_UID = tr.RES_UID INNER JOIN pub.MSP_PROJECTS AS p ON p.PROJ_UID = pr.PROJ_UID LEFT OUTER JOIN (SELECT pspPrjCFV.PROJ_UID, psLV.LT_VALUE_TEXT AS CostCenter FROM pub.MSP_PROJ_CUSTOM_FIELD_VALUES AS pspPrjCFV INNER JOIN pub.MSP_CUSTOM_FIELDS AS pspCF ON pspPrjCFV.MD_PROP_UID = pspCF.MD_PROP_UID LEFT OUTER JOIN pub.MSP_LOOKUP_TABLE_VALUES AS psLV ON psLV.LT_STRUCT_UID = pspPrjCFV.CODE_VALUE WHERE (pspCF.MD_PROP_NAME = 'Cost_Center')) AS PCC ON PCC.PROJ_UID = p.PROJ_UID WHERE (tr.RES_TYPE = 2 OR tr.RES_TYPE = 102)) AS tr GROUP BY RES_UID, RES_NAME) AS tr) AS tr INNER JOIN pub.MSP_RESOURCES AS res ON res.RES_UID = tr.RES_UID LEFT OUTER JOIN pub.MSP_TIMESHEETS AS t ON t.WPRD_UID = tpr.WPRD_UID AND t.RES_UID = tr.RES_UID LEFT OUTER JOIN pub.MSP_TIMESHEET_LINES AS tl ON tl.TS_UID = t.TS_UID AND tl.TS_LINE_ACT_SUM_VALUE > 0 LEFT OUTER JOIN pub.MSP_TIMESHEET_ACTUALS AS tla ON tla.TS_LINE_UID = tl.TS_LINE_UID LEFT OUTER JOIN pub.MSP_TIMESHEET_CLASSES AS tcl ON tcl.TS_LINE_CLASS_UID = tl.TS_LINE_CLASS_UID LEFT OUTER JOIN (SELECT TS_LINE_CLASS_UID, TS_LINE_CLASS_IS_EDITABLE, TS_LINE_CLASS_NAME, TS_LINE_CLASS_TYPE, TS_LINE_CLASS_NEED_APPROVAL, TS_LINE_CLASS_ORGANIZATION, TS_LINE_CLASS_DESC, TS_LINE_CLASS_IS_DISABLED, TS_LINE_CLASS_ALWAYS_DISPLAY, CREATED_DATE, MOD_DATE, CREATED_REV_COUNTER, MOD_REV_COUNTER FROM pub.MSP_TIMESHEET_CLASSES WHERE (TS_LINE_CLASS_TYPE = 0)) AS tcltop ON tcltop.TS_LINE_CLASS_UID = tl.TS_LINE_CLASS_UID LEFT OUTER JOIN (SELECT PROJ_UID, PROJ_NAME, WRES_UID FROM pub.MSP_PROJECTS UNION SELECT 'E38038FA-F8CA-47D1-BFD4-6B45B8462972' AS Expr1, 'Administrative' AS Expr2, NULL AS Expr3) AS tp ON tp.PROJ_UID = tl.PROJ_UID LEFT OUTER JOIN pub.MSP_TASKS AS tsk ON tsk.TASK_UID = tl.TASK_UID LEFT OUTER JOIN pub.MSP_RESOURCES AS pr_owner ON pr_owner.RES_UID = tp.WRES_UID LEFT OUTER JOIN (SELECT ppResCFV.RES_UID, ppResCFV.TEXT_VALUE AS EmployeeID FROM pub.MSP_RES_CUSTOM_FIELD_VALUES AS ppResCFV INNER JOIN pub.MSP_CUSTOM_FIELDS AS ppCF ON ppResCFV.MD_PROP_UID = ppCF.MD_PROP_UID WHERE (ppCF.MD_PROP_NAME = 'employeeID')) AS E ON tr.RES_UID = E.RES_UID LEFT OUTER JOIN (SELECT ppResCFV.RES_UID, ppResCFV.TEXT_VALUE AS Location FROM pub.MSP_RES_CUSTOM_FIELD_VALUES AS ppResCFV INNER JOIN pub.MSP_CUSTOM_FIELDS AS ppCF ON ppResCFV.MD_PROP_UID = ppCF.MD_PROP_UID WHERE (ppCF.MD_PROP_NAME = 'co')) AS C ON tr.RES_UID = C.RES_UID LEFT OUTER JOIN (SELECT pspPrjCFV.PROJ_UID, pspPrjCFV.TEXT_VALUE AS ProjectAccount FROM pub.MSP_PROJ_CUSTOM_FIELD_VALUES AS pspPrjCFV INNER JOIN pub.MSP_CUSTOM_FIELDS AS pspCF ON pspPrjCFV.MD_PROP_UID = pspCF.MD_PROP_UID WHERE (pspCF.MD_PROP_NAME = 'Project Account')) AS PP ON PP.PROJ_UID = tp.PROJ_UID LEFT OUTER JOIN (SELECT pspPrjCFV.PROJ_UID, psLV.LT_VALUE_TEXT AS CostCenter FROM pub.MSP_PROJ_CUSTOM_FIELD_VALUES AS pspPrjCFV INNER JOIN pub.MSP_CUSTOM_FIELDS AS pspCF ON pspPrjCFV.MD_PROP_UID = pspCF.MD_PROP_UID LEFT OUTER JOIN pub.MSP_LOOKUP_TABLE_VALUES AS psLV ON psLV.LT_STRUCT_UID = pspPrjCFV.CODE_VALUE WHERE (pspCF.MD_PROP_NAME = 'Cost_Center')) AS PCC ON PCC.PROJ_UID = tp.PROJ_UID LEFT OUTER JOIN (SELECT pspPrjCFV.PROJ_UID, psLV.LT_VALUE_TEXT AS ProjectStatus FROM pub.MSP_PROJ_CUSTOM_FIELD_VALUES AS pspPrjCFV INNER JOIN pub.MSP_CUSTOM_FIELDS AS pspCF ON pspPrjCFV.MD_PROP_UID = pspCF.MD_PROP_UID LEFT OUTER JOIN pub.MSP_LOOKUP_TABLE_VALUES AS psLV ON psLV.LT_STRUCT_UID = pspPrjCFV.CODE_VALUE WHERE (pspCF.MD_PROP_NAME = 'Project Status')) AS PPS ON PPS.PROJ_UID = tp.PROJ_UID LEFT OUTER JOIN (SELECT pspPrjCFV.PROJ_UID, psLV.LT_VALUE_TEXT AS ProjectType FROM pub.MSP_PROJ_CUSTOM_FIELD_VALUES AS pspPrjCFV INNER JOIN pub.MSP_CUSTOM_FIELDS AS pspCF ON pspPrjCFV.MD_PROP_UID = pspCF.MD_PROP_UID LEFT OUTER JOIN pub.MSP_LOOKUP_TABLE_VALUES AS psLV ON psLV.LT_STRUCT_UID = pspPrjCFV.CODE_VALUE WHERE (pspCF.MD_PROP_NAME = 'ProjectType')) AS PT ON PT.PROJ_UID = tp.PROJ_UID LEFT OUTER JOIN (SELECT ppResCFV.RES_UID, ppResCFV.TEXT_VALUE AS ResourceCompany FROM pub.MSP_RES_CUSTOM_FIELD_VALUES AS ppResCFV INNER JOIN pub.MSP_CUSTOM_FIELDS AS ppCF ON ppResCFV.MD_PROP_UID = ppCF.MD_PROP_UID WHERE (ppCF.MD_PROP_NAME = 'Resource Company')) AS RC ON tr.RES_UID = RC.RES_UID WHERE (tpr.WPRD_START_DATE < GETDATE()) AND (tpr.WPRD_START_DATE >= '12.01.2008')) AS R_1 LEFT OUTER JOIN CUSTOM_RES_PROJ_ASSIGNMENTS AS T ON T.RES_NAME = R_1.ResourceName AND T.PROJ_NAME = R_1.ProjectName AND (T.EFFECTIVE_DATE IS NULL OR DATEDIFF(day, T.EFFECTIVE_DATE, R_1.TimeByDay) >= 0) GROUP BY R_1.RES_HIRE_DATE, R_1.RES_TERMINATION_DATE, R_1.WRES_EMAIL, R_1.TODAY, R_1.IsActive, R_1.StartDate, R_1.EndDate, R_1.PeriodName, R_1.TimeByDay, R_1.TimeByDay_DayOfWeek, R_1.ActualWorkBillable, R_1.ProjectName, R_1.[Task Name], R_1.Status, R_1.ProjectAccount, R_1.Location, R_1.ResourceCompany, R_1.EmployeeID, R_1.TS_LINE_CLASS_NAME, R_1.Type, R_1.TimesheetClass, R_1.ProjectOwner, R_1.Firstname, R_1.LastName, R_1.ResourceName, R_1.ModifiedDate, R_1.ResourceNameUID, R_1.ResourceCC, R_1.CostCenter, R_1.ProjectStatus, R_1.ProjectType, R_1.TS_LINE_UID, R_1.TS_LINE_CACHED_ASSIGN_NAME) AS R LEFT OUTER JOIN CUSTOM_RES_PROJ_ASSIGNMENTS AS T ON T.RES_NAME = R.ResourceName AND T.PROJ_NAME = R.ProjectName AND (R.EFFECTIVE_DATE IS NOT NULL AND T.EFFECTIVE_DATE = R.EFFECTIVE_DATE OR R.EFFECTIVE_DATE IS NULL AND T.EFFECTIVE_DATE IS NULL)