none
Most Recent Timesheet Line Item Status RRS feed

  • Question

  • I am attempting to create a Time sheet Line Item Status report, which works relatively well but it is returning ALL statuses for a line, not just the current status.  For example, if actual work was added to a line item the initial status is 'Pending', once it gets submitted it goes to 'Pending Approval' and once it is approved it becomes 'Approved':      





    Resource Project Task Status Project Manager Hours Day
    John Smith Project 1 Task 1 Approved Sue Jones 4 1/3/2017
    John Smith Project 1 Task 1 Pending Sue Jones 4 1/3/2017
    John Smith Project 1 Task 1 Pending Approval Sue Jones 4 1/3/2017















    I don't want to see the earlier statuses, I only want the most recent one, 'Approved'.  Is there another column that has the time stamp for these status, or something else that I can use to filter these rows?  I tried the MSP_TimesheetActual.CreatedDate column, but all lines have the same value.

    Thanks,

    Todd

    Friday, February 10, 2017 5:02 PM

Answers

  • Hi Todd,

    Try this:

    SELECT DISTINCT
            TR.ResourceName
            ,TP.ProjectName
            ,TT.TaskName
            ,case
                when TL.Comment not like '%a%' AND TSL.TimesheetLineStatus not like '[pending approval]' then 'Unsubmitted'
                else TSL.TimesheetLineStatus end as status
            ,RTM.[VW Manager] AS ResourceManagerName
            ,PM.ProjectOwnerName
            ,TPR.PeriodName
            ,TPR.StartDate
            ,CASE WHEN TPR.PeriodStatusID = 0 THEN 'OPEN'
                WHEN TPR.PeriodStatusID = 1 THEN 'CLOSED'
                ELSE NULL
                END PeriodStatusId
            ,((TS.ActualWorkBillable) + (TS.ActualWorkNonBillable))  ActualWork
            ,(TS.PlannedWork) Planned
            ,R.ResourceUID
            ,TST.Description TimesheetStatus
            ,TS.TimeByDay
        FROM MSP_TimesheetResource TR 
        JOIN MSP_Timesheet T
            ON TR.ResourceNameUID = T.OwnerResourceNameUID
        JOIN MSP_EpmResource_Userview R
            ON r.ResourceUID = TR.ResourceUID
        JOIN MSP_TimesheetLine TL
            ON T.TimesheetUID = TL.TimesheetUID
        JOIN MSP_TimesheetStatus TST
            ON T.TimesheetStatusID = TST.TimesheetStatusID
        JOIN MSP_TimesheetTask TT
            ON TL.TaskNameUID = TT.TaskNameUID
        JOIN MSP_TimesheetProject TP
            ON TL.ProjectNameUID = TP.ProjectNameUID
        JOIN MSP_TimeSheetPeriod TPR
            ON T.PeriodUID = TPR.periodUID
        Join MSP_TimesheetLine_UserView TSL
            ON TL.TimesheetLineUID = TSL.TimesheetLineUID
        JOIN
            (SELECT
                TBD.TimeYear
                ,TBD.TimeWeekOfTheYear
                ,TA.TimesheetLineUID
                ,TA.TimeByDay
                ,TA.ActualWorkBillable
                ,TA.ActualWorkNonBillable
                ,TA.PlannedWork
                ,TA.CreatedDate
            FROM MSP_TimeByDay TBD JOIN MSP_TimesheetActual TA
                ON TBD.TimeByDay = TA.TimeByDay)TS
            ON TL.TimesheetLineUID = TS.TimesheetLineUID
        LEFT OUTER JOIN MSP_EpmResource_UserView RTM
            ON RTM.resourceuid = R.ResourceTimesheetManagerUID
        LEFT OUTER JOIN MSP_EpmProject_UserView PM
            ON PM.ProjectUID = Tp.ProjectUID
        WHERE TS.TimeByDay >= @Date1
        AND TS.TimeByDay <= @Date2
        AND RTM.[VW Manager] in (@Manager)
    
        GROUP BY
            TR.ResourceName
            ,TP.ProjectName
            ,RTM.ResourceName
            ,PM.ProjectOwnerName
            ,TT.TaskName
            ,TSL.TimesheetLineStatus
            ,TPR.PeriodName
            ,TPR.PeriodStatusID
            ,RTM.[VW Manager]
            ,TS.ActualWorkBillable
            ,TS.ActualWorkNonBillable
            ,TS.PlannedWork
            ,R.ResourceUID
            ,TL.Comment
            ,TST.Description
            ,TPR.StartDate
            ,TS.TimeByDay

    I've not look at this in detail but that might fix it, I changed the MSP_TimesheetLine_UserView TSL join. I think the code could be simplified.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Sunday, February 26, 2017 9:55 PM
    Moderator

All replies

  • Hello,

    What does your whole query look like? The TimesheetActual will have one record per timesheet line per day.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Sunday, February 12, 2017 4:03 PM
    Moderator
  • Hi Paul,

    Here is the Query:

    SELECT DISTINCT
            TR.ResourceName
            ,TP.ProjectName
            ,TT.TaskName
            ,case
                when TL.Comment not like '%a%' AND TSL.TimesheetLineStatus not like '[pending approval]' then 'Unsubmitted'
                else TSL.TimesheetLineStatus end as status
            ,RTM.[VW Manager] AS ResourceManagerName
            ,PM.ProjectOwnerName
            ,TPR.PeriodName
            ,TPR.StartDate
            ,CASE WHEN TPR.PeriodStatusID = 0 THEN 'OPEN'
                WHEN TPR.PeriodStatusID = 1 THEN 'CLOSED'
                ELSE NULL
                END PeriodStatusId
            ,((TS.ActualWorkBillable) + (TS.ActualWorkNonBillable))  ActualWork
            ,(TS.PlannedWork) Planned
            ,R.ResourceUID
            ,TST.Description TimesheetStatus
            ,TS.TimeByDay
        FROM MSP_TimesheetResource TR
        JOIN MSP_Timesheet T
            ON TR.ResourceNameUID = T.OwnerResourceNameUID
        JOIN MSP_EpmResource_Userview R
            ON r.ResourceUID = TR.ResourceUID
        JOIN MSP_TimesheetLine TL
            ON T.TimesheetUID = TL.TimesheetUID
        JOIN MSP_TimesheetStatus TST
            ON T.TimesheetStatusID = TST.TimesheetStatusID
        JOIN MSP_TimesheetTask TT
            ON TL.TaskNameUID = TT.TaskNameUID
        JOIN MSP_TimesheetProject TP
            ON TL.ProjectNameUID = TP.ProjectNameUID
        JOIN MSP_TimeSheetPeriod TPR
            ON T.PeriodUID = TPR.periodUID
        Join MSP_TimesheetLine_UserView TSL
            ON TPR.PeriodUID = TSL.PeriodUID
        JOIN
            (SELECT
                TBD.TimeYear
                ,TBD.TimeWeekOfTheYear
                ,TA.TimesheetLineUID
                ,TA.TimeByDay
                ,TA.ActualWorkBillable
                ,TA.ActualWorkNonBillable
                ,TA.PlannedWork
                ,TA.CreatedDate
            FROM MSP_TimeByDay TBD JOIN MSP_TimesheetActual TA
                ON TBD.TimeByDay = TA.TimeByDay)TS
            ON TL.TimesheetLineUID = TS.TimesheetLineUID
        LEFT OUTER JOIN MSP_EpmResource_UserView RTM
            ON RTM.resourceuid = R.ResourceTimesheetManagerUID
        LEFT OUTER JOIN MSP_EpmProject_UserView PM
            ON PM.ProjectUID = Tp.ProjectUID
        WHERE TS.TimeByDay >= @Date1
        AND TS.TimeByDay <= @Date2
        AND RTM.[VW Manager] in (@Manager)

        GROUP BY
            TR.ResourceName
            ,TP.ProjectName
            ,RTM.ResourceName
            ,PM.ProjectOwnerName
            ,TT.TaskName
            ,TSL.TimesheetLineStatus
            ,TPR.PeriodName
            ,TPR.PeriodStatusID
            ,RTM.[VW Manager]
            ,TS.ActualWorkBillable
            ,TS.ActualWorkNonBillable
            ,TS.PlannedWork
            ,R.ResourceUID
            ,TL.Comment
            ,TST.Description
            ,TPR.StartDate
            ,TS.TimeByDay

    Even if I set the Date1 and Date 2 parameters to the same date I will get multiple results for the same line item.

    Todd

    Friday, February 24, 2017 4:46 PM
  • Hi Todd,

    Try this:

    SELECT DISTINCT
            TR.ResourceName
            ,TP.ProjectName
            ,TT.TaskName
            ,case
                when TL.Comment not like '%a%' AND TSL.TimesheetLineStatus not like '[pending approval]' then 'Unsubmitted'
                else TSL.TimesheetLineStatus end as status
            ,RTM.[VW Manager] AS ResourceManagerName
            ,PM.ProjectOwnerName
            ,TPR.PeriodName
            ,TPR.StartDate
            ,CASE WHEN TPR.PeriodStatusID = 0 THEN 'OPEN'
                WHEN TPR.PeriodStatusID = 1 THEN 'CLOSED'
                ELSE NULL
                END PeriodStatusId
            ,((TS.ActualWorkBillable) + (TS.ActualWorkNonBillable))  ActualWork
            ,(TS.PlannedWork) Planned
            ,R.ResourceUID
            ,TST.Description TimesheetStatus
            ,TS.TimeByDay
        FROM MSP_TimesheetResource TR 
        JOIN MSP_Timesheet T
            ON TR.ResourceNameUID = T.OwnerResourceNameUID
        JOIN MSP_EpmResource_Userview R
            ON r.ResourceUID = TR.ResourceUID
        JOIN MSP_TimesheetLine TL
            ON T.TimesheetUID = TL.TimesheetUID
        JOIN MSP_TimesheetStatus TST
            ON T.TimesheetStatusID = TST.TimesheetStatusID
        JOIN MSP_TimesheetTask TT
            ON TL.TaskNameUID = TT.TaskNameUID
        JOIN MSP_TimesheetProject TP
            ON TL.ProjectNameUID = TP.ProjectNameUID
        JOIN MSP_TimeSheetPeriod TPR
            ON T.PeriodUID = TPR.periodUID
        Join MSP_TimesheetLine_UserView TSL
            ON TL.TimesheetLineUID = TSL.TimesheetLineUID
        JOIN
            (SELECT
                TBD.TimeYear
                ,TBD.TimeWeekOfTheYear
                ,TA.TimesheetLineUID
                ,TA.TimeByDay
                ,TA.ActualWorkBillable
                ,TA.ActualWorkNonBillable
                ,TA.PlannedWork
                ,TA.CreatedDate
            FROM MSP_TimeByDay TBD JOIN MSP_TimesheetActual TA
                ON TBD.TimeByDay = TA.TimeByDay)TS
            ON TL.TimesheetLineUID = TS.TimesheetLineUID
        LEFT OUTER JOIN MSP_EpmResource_UserView RTM
            ON RTM.resourceuid = R.ResourceTimesheetManagerUID
        LEFT OUTER JOIN MSP_EpmProject_UserView PM
            ON PM.ProjectUID = Tp.ProjectUID
        WHERE TS.TimeByDay >= @Date1
        AND TS.TimeByDay <= @Date2
        AND RTM.[VW Manager] in (@Manager)
    
        GROUP BY
            TR.ResourceName
            ,TP.ProjectName
            ,RTM.ResourceName
            ,PM.ProjectOwnerName
            ,TT.TaskName
            ,TSL.TimesheetLineStatus
            ,TPR.PeriodName
            ,TPR.PeriodStatusID
            ,RTM.[VW Manager]
            ,TS.ActualWorkBillable
            ,TS.ActualWorkNonBillable
            ,TS.PlannedWork
            ,R.ResourceUID
            ,TL.Comment
            ,TST.Description
            ,TPR.StartDate
            ,TS.TimeByDay

    I've not look at this in detail but that might fix it, I changed the MSP_TimesheetLine_UserView TSL join. I think the code could be simplified.

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Sunday, February 26, 2017 9:55 PM
    Moderator
  • Perfect!  Thanks Paul.

    Todd

    Friday, March 3, 2017 7:50 PM
  • Hi Todd,

    Did this resolve the issue for you?

    Paul


    Paul Mather | Twitter | http://pwmather.wordpress.com | CPS | MVP | Downloads

    Tuesday, March 14, 2017 9:38 AM
    Moderator