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)
This entry passed through the Full-Text RSS service — if this is your content and you're reading it on someone else's site, please read the FAQ at http://ift.tt/jcXqJW.
Комментариев нет:
Отправить комментарий